Install:
SQL> @?/rdbms/admin/spcreate.sql
Create snapshot
SQL> execute statspack.snap;
Run report (need: begin snap, and snap, report_file)
SQL> @?/rdbms/admin/spreport.sql
Run SQL report (need: begin snap, and snap, hash value for SQL, report_file)
SQL> @?/rdbms/admin/sprepsql.sql
Delete snapshots
SQL> @?/rdbms/admin/sppurge.sql
Truncate all stats
SQL> @?/rdbms/admin/sptrunc.sql
Drop Statspack
SQL> @?/rdbms/admin/spdrop.sql
3zecisi3 ... DBA experiences
Tuesday, March 27, 2012
Thursday, March 22, 2012
RMAN view jobs
SQL> select sid, spid, client_info from v$session
2 join v$process
3 on v$session.paddr=v$process.addr
4 where client_info like '%rman%';
39 7769
rman channel=ORA_DISK_1
48 7767
rman channel=ORA_DISK_1
SQL> select opname, context, sofar, totalwork, round(sofar/totalwork*100,2)
2 "%_complete" from v$session_longops
3 where opname like 'RMAN%'
4 and opname not like '%aggregate%';
RMAN: full datafile backup 1
245040 245040 100
RMAN: full datafile backup 2
148331 148331 100
RMAN: full datafile backup 1
245040 245040 100
RMAN: full datafile backup 2
148331 148331 100
RMAN: full datafile backup 1
245040 245040 100
RMAN: full datafile backup 2
148329 148329 100
RMAN: full datafile backup 1
245040 245040 100
RMAN: full datafile backup 2
148334 148334 100
8 rows selected.
Using Flashback Transaction Query
As sysdba
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> grant execute to dbms_flashback to danco;
SQL> grant select any transaction to danco;
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
2028207
SQL> select * from test2;
2
2
2
2
2
SQL> select * from flashback_transaction_query where table_name='TEST2' and undo_sql is not null;
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAE';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAD';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAC';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAB';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAE';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAD';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAC';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAB';
8 rows selected.
SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (2028015);
PL/SQL procedure successfully completed.
SQL> select * from test2;
2
5
5
5
5
SQL> select * from flashback_transaction_query where table_name='TEST2' and undo_sql is not null;
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAE';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAD';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAC';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAB';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAE';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAD';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAC';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAB';
8 rows selected.
SQL>
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> grant execute to dbms_flashback to danco;
SQL> grant select any transaction to danco;
SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;
2028207
SQL> select * from test2;
2
2
2
2
2
SQL> select * from flashback_transaction_query where table_name='TEST2' and undo_sql is not null;
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAE';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAD';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAC';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAB';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAE';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAD';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAC';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAB';
8 rows selected.
SQL> exec DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (2028015);
PL/SQL procedure successfully completed.
SQL> select * from test2;
2
5
5
5
5
SQL> select * from flashback_transaction_query where table_name='TEST2' and undo_sql is not null;
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAE';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAD';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAC';
03001C0039050000 2028066 22-MAR-12 2028067 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '5' where ROWID = 'AAAS2jAAEAAAAhnAAB';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 1 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAE
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAE';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 2 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAD
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAD';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 3 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAC
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAC';
040018008B040000 2015834 22-MAR-12 2015835 22-MAR-12
DANCO 4 UPDATE
TEST2
DANCO AAAS2jAAEAAAAhnAAB
update "DANCO"."TEST2" set "ID" = '6' where ROWID = 'AAAS2jAAEAAAAhnAAB';
8 rows selected.
SQL>
Wednesday, March 21, 2012
RMAN restore controlfile error (RMAN-20021: database not set)
RMAN> restore controlfile to '/tmp/controlfile';
Starting restore at 20120321:13:36:17
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/21/2012 13:36:17
RMAN-20021: database not set
RMAN> exit
Recovery Manager complete.
-bash-3.2$ NLS_LANG=american_america.we8iso8859p1
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 13:38:07 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORATEST (DBID=3309052188)
RMAN> restore controlfile to '/tmp/controlfile';
Starting restore at 20120321:13:38:11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
output file name=/tmp/controlfile
channel ORA_DISK_1: reading from backup piece +FRA/oratest/autobackup/2012_03_21/s_778511564.295.778511569
channel ORA_DISK_1: piece handle=+FRA/oratest/autobackup/2012_03_21/s_778511564.295.778511569 tag=TAG20120321T131243
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 20120321:13:38:17
RMAN>
DRA (Data Recovery Advisor)
Data Recovery Advisor:
- list failure
- advise failure
- repair failure
After dropping one datafile:
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
722 HIGH OPEN 21-MAR-12 One or more non-system datafiles need media recovery
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If you restored the wrong version of data file +DATA/oratest/datafile/drop_tbs.269.778508815, then replace it with the correct one
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 7
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_2369864291.hm
contents of repair script:
# restore and recover datafile
restore datafile 7;
recover datafile 7;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 21-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +DATA/oratest/datafile/drop_tbs.269.778508815
channel ORA_DISK_1: reading from backup piece +FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083
channel ORA_DISK_1: piece handle=+FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083 tag=TAG20120321T123122
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-MAR-12
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
repair failure complete
RMAN> exit
Recovery Manager complete.
-bash-3.2$
-bash-3.2$
-bash-3.2$
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 12:36:22 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn drop_user
Enter password:
Connected.
SQL> select count(*) from drop_table;
select count(*) from drop_table
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '+DATA/oratest/datafile/drop_tbs.269.778509333'
SQL> conn / as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 12:37:18 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORATEST (DBID=3309052188)
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
862 HIGH OPEN 21-MAR-12 One or more non-system datafiles are offline
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
862 HIGH OPEN 21-MAR-12 One or more non-system datafiles are offline
848 HIGH OPEN 21-MAR-12 Tablespace 9: 'DROP_TBS' is offline
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Online tablespace DROP_TBS
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1895925774.hm
contents of repair script:
# online a offline tablespace
sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;";
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/oratest/oratest/hm/reco_1466333412.hm'' ); end;
repair failure complete
RMAN> exit
Recovery Manager complete.
RMAN switch to copy
If a copy of datafile is available, the restore downtime can be minimized with SWITCH TO COPY
SQL> alter database datafile 7 offline;
Database altered.
RMAN> list copy;
specification does not match any control file copy in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 1 A 21-MAR-12 1961992 21-MAR-12
Name: +FRA/oratest/datafile/system.284.778509743
Tag: TAG20120321T124222
3 2 A 21-MAR-12 1962026 21-MAR-12
Name: +FRA/oratest/datafile/sysaux.282.778509799
Tag: TAG20120321T124222
5 3 A 21-MAR-12 1962058 21-MAR-12
Name: +FRA/oratest/datafile/undotbs1.263.778509849
Tag: TAG20120321T124222
7 4 A 21-MAR-12 1962065 21-MAR-12
Name: +FRA/oratest/datafile/users.292.778509863
Tag: TAG20120321T124222
4 5 A 21-MAR-12 1962052 21-MAR-12
Name: +FRA/oratest/datafile/example.300.778509833
Tag: TAG20120321T124222
6 6 A 21-MAR-12 1962061 21-MAR-12
Name: +FRA/oratest/datafile/tab.291.778509855
Tag: TAG20120321T124222
8 7 A 21-MAR-12 1962067 21-MAR-12
Name: +FRA/oratest/datafile/drop_tbs.293.778509865
Tag: TAG20120321T124222
List of Archived Log Copies for database with db_unique_name ORATEST
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
21 1 34 A 19-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_34.283.778415739
22 1 35 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_35.279.778419185
23 1 36 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_36.278.778419207
24 1 37 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_37.275.778419953
25 1 38 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_38.271.778501521
26 1 39 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_39.270.778502477
27 1 40 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_40.269.778506097
28 1 41 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_41.268.778508691
RMAN> switch datafile 7 to copy;
datafile 7 switched to datafile copy "+FRA/oratest/datafile/drop_tbs.293.778509865"
RMAN> recover datafile 7;
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
Move back the datafile in original path with datafile copy
RMAN> backup as copy datafile 7 format '+DATA';
Starting backup at 21-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+FRA/oratest/datafile/drop_tbs.293.778509865
output file name=+DATA/oratest/datafile/drop_tbs.269.778510537 tag=TAG20120321T125535 RECID=9 STAMP=778510536
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-MAR-12
Starting Control File and SPFILE Autobackup at 21-MAR-12
piece handle=+FRA/oratest/autobackup/2012_03_21/s_778510537.298.778510539 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-12
RMAN> switch datafile 7 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 03/21/2012 12:55:50
RMAN-06572: database is open and datafile 7 is not offline
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline
RMAN> switch datafile 7 to copy;
datafile 7 switched to datafile copy "+DATA/oratest/datafile/drop_tbs.269.778510
RMAN> recover datafile 7;
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
RMAN> sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online
RMAN> exit
SQL> alter database datafile 7 offline;
Database altered.
RMAN> list copy;
specification does not match any control file copy in the repository
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 1 A 21-MAR-12 1961992 21-MAR-12
Name: +FRA/oratest/datafile/system.284.778509743
Tag: TAG20120321T124222
3 2 A 21-MAR-12 1962026 21-MAR-12
Name: +FRA/oratest/datafile/sysaux.282.778509799
Tag: TAG20120321T124222
5 3 A 21-MAR-12 1962058 21-MAR-12
Name: +FRA/oratest/datafile/undotbs1.263.778509849
Tag: TAG20120321T124222
7 4 A 21-MAR-12 1962065 21-MAR-12
Name: +FRA/oratest/datafile/users.292.778509863
Tag: TAG20120321T124222
4 5 A 21-MAR-12 1962052 21-MAR-12
Name: +FRA/oratest/datafile/example.300.778509833
Tag: TAG20120321T124222
6 6 A 21-MAR-12 1962061 21-MAR-12
Name: +FRA/oratest/datafile/tab.291.778509855
Tag: TAG20120321T124222
8 7 A 21-MAR-12 1962067 21-MAR-12
Name: +FRA/oratest/datafile/drop_tbs.293.778509865
Tag: TAG20120321T124222
List of Archived Log Copies for database with db_unique_name ORATEST
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
21 1 34 A 19-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_34.283.778415739
22 1 35 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_35.279.778419185
23 1 36 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_36.278.778419207
24 1 37 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_20/thread_1_seq_37.275.778419953
25 1 38 A 20-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_38.271.778501521
26 1 39 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_39.270.778502477
27 1 40 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_40.269.778506097
28 1 41 A 21-MAR-12
Name: +FRA/oratest/archivelog/2012_03_21/thread_1_seq_41.268.778508691
RMAN> switch datafile 7 to copy;
datafile 7 switched to datafile copy "+FRA/oratest/datafile/drop_tbs.293.778509865"
RMAN> recover datafile 7;
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
Move back the datafile in original path with datafile copy
RMAN> backup as copy datafile 7 format '+DATA';
Starting backup at 21-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+FRA/oratest/datafile/drop_tbs.293.778509865
output file name=+DATA/oratest/datafile/drop_tbs.269.778510537 tag=TAG20120321T125535 RECID=9 STAMP=778510536
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-MAR-12
Starting Control File and SPFILE Autobackup at 21-MAR-12
piece handle=+FRA/oratest/autobackup/2012_03_21/s_778510537.298.778510539 comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAR-12
RMAN> switch datafile 7 to copy;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 03/21/2012 12:55:50
RMAN-06572: database is open and datafile 7 is not offline
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline
RMAN> switch datafile 7 to copy;
datafile 7 switched to datafile copy "+DATA/oratest/datafile/drop_tbs.269.778510
RMAN> recover datafile 7;
Starting recover at 21-MAR-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 21-MAR-12
RMAN> sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online
RMAN> exit
RMAN restore database until time
1. Backup database
RMAN> list backup;
List of Backup Sets
===================
....
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 303.08M DISK 00:01:31 21-MAR-12
BP Key: 36 Status: AVAILABLE Compressed: YES Tag: TAG20120321T123122
Piece Name: +FRA/oratest/backupset/2012_03_21/nnndf0_tag20120321t123122_0.267.778509083
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1960721 21-MAR-12 +DATA/oratest/datafile/system.256.773672945
2 Full 1960721 21-MAR-12 +DATA/oratest/datafile/sysaux.257.773672955
3 Full 1960721 21-MAR-12 +DATA/oratest/datafile/undotbs1.258.773672957
4 Full 1960721 21-MAR-12 +DATA/oratest/datafile/users.259.773672963
5 Full 1960721 21-MAR-12 +DATA/oratest/datafile/example.265.773673233
6 Full 1960721 21-MAR-12 +DATA/oratest/datafile/tab.267.774023547
7 Full 1960721 21-MAR-12 +DATA/oratest/datafile/drop_tbs.269.778509333
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 9.36M DISK 00:00:02 21-MAR-12
BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20120321T123258
Piece Name: +FRA/oratest/autobackup/2012_03_21/s_778509178.266.778509181
SPFILE Included: Modification time: 21-MAR-12
SPFILE db_unique_name: ORATEST
Control File Included: Ckp SCN: 1961455 Ckp time: 21-MAR-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 9.36M DISK 00:00:02 21-MAR-12
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: TAG20120321T124426
Piece Name: +FRA/oratest/autobackup/2012_03_21/s_778509866.294.778509869
SPFILE Included: Modification time: 21-MAR-12
SPFILE db_unique_name: ORATEST
Control File Included: Ckp SCN: 1962073 Ckp time: 21-MAR-12
2. View data, time and drop one table
SQL> select * from drop_user.drop_table;
ID
----------
2
1
1
1
1
1
1
1
1
1
1
ID
----------
1
1
1
1
15 rows selected.
SQL>
SQL>
SQL> host
bash-3.2$ date
Wed Mar 21 13:03:38 EET 2012
bash-3.2$ exit
exit
SQL> drop table drop_user.drop_table;
Table dropped.
SQL> exit
3. Recover database until time before drop
-bash-3.2$ export NLS_LANG=american_america.us7ascii
-bash-3.2$ export NLS_DATE_FORMAT="yyyymmdd:hh24:mi:ss"
-bash-3.2$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Mar 21 13:05:20 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORATEST (DBID=3309052188)
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 313159680 bytes
Fixed Size 2225576 bytes
Variable Size 272632408 bytes
Database Buffers 33554432 bytes
Redo Buffers 4747264 bytes
RMAN> run
2> {
3> set until time '20120321:13:00:00';
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 20120321:13:08:54
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=2 STAMP=778509788 file name=+FRA/oratest/datafile/syst em.284.778509743
destination for restore of datafile 00001: +DATA/oratest/datafile/system.256.773 672945
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=+DATA/oratest/datafile/system.256.773672945 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00002
input datafile copy RECID=3 STAMP=778509832 file name=+FRA/oratest/datafile/sysa ux.282.778509799
destination for restore of datafile 00002: +DATA/oratest/datafile/sysaux.257.773 672955
channel ORA_DISK_1: copied datafile copy of datafile 00002
output file name=+DATA/oratest/datafile/sysaux.257.773672955 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=5 STAMP=778509852 file name=+FRA/oratest/datafile/undo tbs1.263.778509849
destination for restore of datafile 00003: +DATA/oratest/datafile/undotbs1.258.7 73672957
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=+DATA/oratest/datafile/undotbs1.258.773672957 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=7 STAMP=778509863 file name=+FRA/oratest/datafile/users.292.778509863
destination for restore of datafile 00004: +DATA/oratest/datafile/users.259.773672963
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=+DATA/oratest/datafile/users.259.773672963 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=4 STAMP=778509847 file name=+FRA/oratest/datafile/example.300.778509833
destination for restore of datafile 00005: +DATA/oratest/datafile/example.265.773673233
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=+DATA/oratest/datafile/example.265.773673233 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=6 STAMP=778509859 file name=+FRA/oratest/datafile/tab.291.778509855
destination for restore of datafile 00006: +DATA/oratest/datafile/tab.267.774023547
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=+DATA/oratest/datafile/tab.267.774023547 RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00007
input datafile copy RECID=10 STAMP=778510722 file name=+FRA/oratest/datafile/drop_tbs.293.778509865
destination for restore of datafile 00007: +DATA/oratest/datafile/drop_tbs.269.778510537
channel ORA_DISK_1: copied datafile copy of datafile 00007
output file name=+DATA/oratest/datafile/drop_tbs.269.778510537 RECID=0 STAMP=0
Finished restore at 20120321:13:10:46
Starting recover at 20120321:13:10:46
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 20120321:13:10:48
RMAN> sql 'alter database open read only';
sql statement: alter database open read only
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/21/2012 13:11:08
RMAN-11003: failure during parse/execution of SQL statement: alter database open read only
ORA-16005: database requires recovery
RMAN>
NOTE: In DBA2 course for 11gr2 is RMAN> sql 'alter database open read only'; and this should be working.
-bash-3.2$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 21 13:11:35 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from drop_user.drop_table;
ID
----------
2
1
1
1
1
1
1
1
1
1
1
ID
----------
1
1
1
1
15 rows selected.
SQL>
Monday, March 19, 2012
View rman processes
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%';
Tuesday, March 13, 2012
View session, process, sql and explain plan
View running statements
select ses.sid, ses.username, ses.terminal, sq.sql_id, sq.sql_text, p.pid, p.spid
from v$session ses
join v$sql sq
on ses.sql_id = sq.sql_id
join v$process p
on ses.paddr=p.addr;
View open cursors for one session
select SID,SQL_ID,SQL_TEXT from v$open_cursor where sid=244;
View explain plan for one sql_id
SELECT * FROM TABLE(dbms_xplan.display_cursor('azbbfqxzp56sw'));
Time left to complete rollback
DECLARE
CURSOR tx
IS
SELECT /*+ USE_NL(S,T,X) */
NVL
(s.username,
'session no more exists or running on the other node of RAC'
),
s.sid,x.ktuxeusn, x.ktuxeslt, x.ktuxesqn, x.ktuxesiz
FROM ((sys.x$ktuxe x LEFT JOIN sys.v$transaction t ON
t.xidusn = x.ktuxeusn AND t.xidslot = x.ktuxeslt AND
t.xidsqn = x.ktuxesqn ) LEFT JOIN
sys.v$session s ON
s.saddr = t.ses_addr)
WHERE x.ktuxesta = 'ACTIVE' AND x.ktuxesiz > 1;
user_name VARCHAR2 (80);
xid_usn NUMBER;
xid_slot NUMBER;
xid_sqn NUMBER;
used_ublk1 NUMBER;
used_ublk2 NUMBER;
sid number;
BEGIN
OPEN tx;
LOOP
FETCH tx
INTO user_name, sid, xid_usn, xid_slot, xid_sqn, used_ublk1;
EXIT WHEN tx%NOTFOUND;
IF tx%ROWCOUNT = 1
THEN
sys.DBMS_LOCK.sleep (120);
SELECT SUM (ktuxesiz)
INTO used_ublk2
FROM sys.x$ktuxe
WHERE ktuxeusn = xid_usn
AND ktuxeslt = xid_slot
AND ktuxesqn = xid_sqn
AND ktuxesta = 'ACTIVE';
sys.DBMS_OUTPUT.put_line ('session (' || user_name || sid ||')');
sys.DBMS_OUTPUT.put_line
( 'transaction '
|| xid_usn
|| '.'
|| xid_slot
|| '.'
|| xid_sqn
|| ' will finish rolling back at approximately '
|| TO_CHAR (sysdate +
used_ublk2
/ (used_ublk2 - used_ublk1)
/ 30
/ 24, 'HH24:MI:SS DD-MON-YYYY'
)
);
END IF;
END LOOP;
IF user_name IS NULL
THEN
sys.DBMS_OUTPUT.put_line ('No transactions appear to be rolling back.');
END IF;
END;
/
Thursday, March 8, 2012
Transform comma text file in SQL insert file
From unix prompt:
perl -pi.bak -e "s/,/','/g;s/^/insert into tab\(x,y\) values \('/g;s/$/'\);/" filename.dat
where:
more filename.dat
11111111,1
.........
After change, there will be one backup file of the original file.
perl -pi.bak -e "s/,/','/g;s/^/insert into tab\(x,y\) values \('/g;s/$/'\);/" filename.dat
where:
more filename.dat
11111111,1
.........
After change, there will be one backup file of the original file.
View locked objects
select
object_name,
object_type,
session_id,
type,
lmode,
request,
block,
ctime
from
v$locked_object, all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name;
Example:
SQL> lock table ext_tab_db in exclusive mode;
Table(s) Locked.
Elapsed: 00:00:00.11
SQL> select
2 object_name,
3 object_type,
4 session_id,
5 type,
6 lmode,
7 request,
8 block,
9 ctime
10 from
11 v$locked_object, all_objects, v$lock
12 where
13 v$locked_object.object_id = all_objects.object_id AND
14 v$lock.id1 = all_objects.object_id AND
15 v$lock.sid = v$locked_object.session_id
16 order by
17 session_id, ctime desc, object_name;
OBJECT_NAME OBJECT_TYPE SESSION_ID TY LMODE
------------------------------ ------------------- ---------- -- ----------
REQUEST BLOCK CTIME
---------- ---------- ----------
EXT_TAB_DB TABLE 31 TM 6
0 2 8
Elapsed: 00:00:00.17
SQL>
Wednesday, March 7, 2012
Password versions
SQL> select dbms_metadata.get_ddl('USER','TEST') from dual;
CREATE USER "TEST" IDENTIFIED BY VALUES 'S:D9B62B388A0FE232B5AE09A051D2175C8F
493BF6797D415DEDB0A8B8BB2D;7A0F2B316C212D67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL> select password, spare4 from sys.user$ where name='TEST';
7A0F2B316C212D67
S:D9B62B388A0FE232B5AE09A051D2175C8F493BF6797D415DEDB0A8B8BB2D
SQL> select password_versions from dba_users where username='TEST';
10G 11G
SQL> alter user test identified by values '7A0F2B316C212D67';
User altered.
SQL> select password, spare4 from sys.user$ where name='TEST';
7A0F2B316C212D67
SQL> select password_versions from dba_users where username='TEST';
10G
SQL> alter user test identified by values 'S:D9B62B388A0FE232B5AE09A051D2175C8F493BF6797D415DEDB0A8B8BB2D';
User altered.
SQL> select password, spare4 from sys.user$ where name='TEST';
S:D9B62B388A0FE232B5AE09A051D2175C8F493BF6797D415DEDB0A8B8BB2D
SQL> select password_versions from dba_users where username='TEST';
11G
SQL> conn test/test
Connected.
SQL> conn dba_user
Enter password:
Connected.
SQL> alter user test identified by values 'S:D9B62B388A0FE232B5AE09A051D2175C8F493BF6797D415DEDB0A8B8BB2D;7A0F2B316C212D67';
User altered.
SQL> select password_versions from dba_users where username='TEST';
10G 11G
SQL>
Pseudocolumn ORA_ROWSCN
For each row, ORA_ROWSCN returns the upper system change number (SCN) of the most recent change of the row. By default, this information is kept at oracle block level, so all rows in the block will have the same ORA_ROWSCN with the updated row.
It uses SCN stored for transaction in ITL (Interesting Transaction List) from oracle block header.
To have a fine-grained view of the update SCN, this system change number can be kept at row level when row dependencies is enabled on table. This option must be enabled at table creation time with CREATE TABLE ... ROWDEPENDENCIES statement.
This pseudocolumn:
SQL> select table_name, dependencies from user_tables;
TEST DISABLED
TEST_IDX DISABLED
-- no tables with rowdependencies enabled
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
PK_COL NOT NULL NUMBER
NOT_NULL NOT NULL NUMBER
CHECK_COL NUMBER
UK_COL NUMBER
FK_COL NUMBER
SQL> select * from test;
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
SQL> create table test2 as select * from test; -- no row dependencies
Table created.
SQL> create table test3 rowdependencies as select * from test; -- with row dependencies
Table created.
SQL> select table_name, dependencies from user_tables;
TEST3 ENABLED
TEST2 DISABLED
TEST DISABLED
TEST_IDX DISABLED
SQL> select ORA_ROWSCN from test2;
9064952
9064952
9064952
SQL> select ORA_ROWSCN from test3;
9065020
9065020
9065020
SQL> update test2 set check_col=5 where pk_col=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN from test2;
9065228
9065228
9065228
-- same SCN for all rows in block
SQL> update test3 set check_col=5 where pk_col=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN from test3;
9065020
9065020
9065258
-- different SCN for updated row
SQL> update test3 set check_col=4 where pk_col=3;
1 row updated.
SQL> select ORA_ROWSCN, check_col from test3;
9065020 1
9065020 2
4
-- ORA_ROWSCN is null until commit or rollback
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN, check_col from test3;
9065020 1
9065020 2
9065432 4
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test2
2 versions between scn minvalue and maxvalue where pk_col=3;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V CHECK_COL
----------------- --------------- ---------------- - ----------
9065228 080021007E090000 U 5
9065228 3
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test3
2 versions between scn minvalue and maxvalue where pk_col=3;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V CHECK_COL
----------------- --------------- ---------------- - ----------
9065432 090012007C090000 U 4
9065258 9065432 020018008A090000 U 5
9065258 3
SQL> -- view all versions pseudocolumns
07-MAR-12 03.41.42 PM
9065228
080021007E090000 U 5
07-MAR-12 03.41.42 PM
9065228 3
SQL> select versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation, check_col from test3
2 versions between scn minvalue and maxvalue where pk_col=3;
07-MAR-12 03.42.51 PM
9065432
090012007C090000 U 4
07-MAR-12 03.42.03 PM
9065258
07-MAR-12 03.42.51 PM
9065432 020018008A090000 U 5
07-MAR-12 03.42.03 PM
9065258 3
SQL>
SQL> insert into test3 values (4,4,4,4,4);
1 row created.
SQL> delete from test3 where pk_col=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation from test3
2 versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V
----------------- --------------- ---------------- -
9068407 020012008C090000 D
9065432 090012007C090000 U
9065258 9065432 020018008A090000 U
9068407
9065258
9068407 020012008C090000 I
7 rows selected.
SQL>
where,
It uses SCN stored for transaction in ITL (Interesting Transaction List) from oracle block header.
To have a fine-grained view of the update SCN, this system change number can be kept at row level when row dependencies is enabled on table. This option must be enabled at table creation time with CREATE TABLE ... ROWDEPENDENCIES statement.
This pseudocolumn:
- cannot be used in a query to a view
- can be use to refer to the underlying table when creating a view
- can be use in WHERE clause of an UPDATE or DELETE statement.
- is not supported for Flashback Query. Instead of ORA_ROWSCN can be used version query pseudocolumns. http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns003.htm#i1009358
SQL> select table_name, dependencies from user_tables;
TEST DISABLED
TEST_IDX DISABLED
-- no tables with rowdependencies enabled
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
PK_COL NOT NULL NUMBER
NOT_NULL NOT NULL NUMBER
CHECK_COL NUMBER
UK_COL NUMBER
FK_COL NUMBER
SQL> select * from test;
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
SQL> create table test2 as select * from test; -- no row dependencies
Table created.
SQL> create table test3 rowdependencies as select * from test; -- with row dependencies
Table created.
SQL> select table_name, dependencies from user_tables;
TEST3 ENABLED
TEST2 DISABLED
TEST DISABLED
TEST_IDX DISABLED
SQL> select ORA_ROWSCN from test2;
9064952
9064952
9064952
SQL> select ORA_ROWSCN from test3;
9065020
9065020
9065020
SQL> update test2 set check_col=5 where pk_col=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN from test2;
9065228
9065228
9065228
-- same SCN for all rows in block
SQL> update test3 set check_col=5 where pk_col=3;
1 row updated.
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN from test3;
9065020
9065020
9065258
-- different SCN for updated row
SQL> update test3 set check_col=4 where pk_col=3;
1 row updated.
SQL> select ORA_ROWSCN, check_col from test3;
9065020 1
9065020 2
4
-- ORA_ROWSCN is null until commit or rollback
SQL> commit;
Commit complete.
SQL> select ORA_ROWSCN, check_col from test3;
9065020 1
9065020 2
9065432 4
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test2
2 versions between scn minvalue and maxvalue where pk_col=3;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V CHECK_COL
----------------- --------------- ---------------- - ----------
9065228 080021007E090000 U 5
9065228 3
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation, check_col from test3
2 versions between scn minvalue and maxvalue where pk_col=3;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V CHECK_COL
----------------- --------------- ---------------- - ----------
9065432 090012007C090000 U 4
9065258 9065432 020018008A090000 U 5
9065258 3
SQL> -- view all versions pseudocolumns
SQL> select versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation, check_col from test2
2 versions between scn minvalue and maxvalue where pk_col=3;07-MAR-12 03.41.42 PM
9065228
080021007E090000 U 5
07-MAR-12 03.41.42 PM
9065228 3
SQL> select versions_starttime, versions_startscn, versions_endtime, versions_endscn, versions_xid, versions_operation, check_col from test3
2 versions between scn minvalue and maxvalue where pk_col=3;
07-MAR-12 03.42.51 PM
9065432
090012007C090000 U 4
07-MAR-12 03.42.03 PM
9065258
07-MAR-12 03.42.51 PM
9065432 020018008A090000 U 5
07-MAR-12 03.42.03 PM
9065258 3
SQL>
SQL> insert into test3 values (4,4,4,4,4);
1 row created.
SQL> delete from test3 where pk_col=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select versions_startscn, versions_endscn,versions_xid, versions_operation from test3
2 versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V
----------------- --------------- ---------------- -
9068407 020012008C090000 D
9065432 090012007C090000 U
9065258 9065432 020018008A090000 U
9068407
9065258
9068407 020012008C090000 I
7 rows selected.
SQL>
where,
VERSIONS_STARTSCN - SCN of the first version of the row
VERSIONS_ENDSCN - SCN of the last version of the row
VERSIONS_XID - transaction ID
VERSIONS_OPERATION - type of operation (I-insert, U-update, D-delete)
VERSIONS_OPERATION - type of operation (I-insert, U-update, D-delete)
View transaction details
SELECT username, terminal, osuser,
t. name, t.start_time, r.name, t.used_ublk "ROLLB BLKS",
DECODE(t.SPACE, 'YES', 'SPACE TX',
DECODE(t.recursive, 'YES', 'RECURSIVE TX',
DECODE(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
FROM sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
WHERE t.xidusn = r.usn
AND t.ses_addr = s.saddr;
View database character set
SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
VALUE$
--------------------------------------------------------------------------------
WE8ISO8859P1
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.1.0.4.0
20 rows selected.
View current scn
select current_scn from v$database;
or
SELECT dbms_flashback.get_system_change_number FROM dual;
or
SELECT dbms_flashback.get_system_change_number FROM dual;
View locks and latches
SELECT s.sid, s.serial#, DECODE(s.process, NULL, DECODE(SUBSTR(p.username,1,1), '?', UPPER(s.osuser), p.username), DECODE( p.username, 'ORACUSR ', UPPER(s.osuser), s.process) ) process, NVL(s.username, 'SYS ('||substr(p.username,1,4)||')') username, DECODE(s.terminal, NULL, RTRIM(p.terminal, CHR(0)), UPPER(s.terminal)) terminal, DECODE(l.TYPE, -- Long locks 'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ', 'UL', 'PLS USR LOCK', -- Short locks 'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE', 'CI', 'CROSS INST F', 'DF', 'DATA FILE ', 'CU', 'CURSOR BIND ', 'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP', 'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN', 'FS', 'FILE SET ', 'IN', 'INSTANCE NUM', 'FI', 'SGA OPN FILE', 'IR', 'INSTCE RECVR', 'IS', 'GET STATE ', 'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ', 'LS', 'LOG SWITCH ', 'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY', 'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT', 'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ', 'RW', 'ROW WAIT ', 'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE', 'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC', 'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ', 'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ', 'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ', 'UN', 'USER NAME ', 'WL', 'WRITE REDO ', 'TYPE='||l.TYPE) TYPE, DECODE(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR(l.lmode) ) lmode, DECODE(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR(l.request) ) lrequest, DECODE(l.TYPE, 'MR', DECODE(u.name, NULL, 'DICTIONARY OBJECT', u.name||'.'||o.name), 'TD', u.name||'.'||o.name, 'TM', u.name||'.'||o.name, 'RW', 'FILE#='||substr(l.id1,1,3)|| ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2, 'TX', 'RS_SLOT#'||l.id1||' WRP#'||l.id2, 'WL', 'REDO LOG FILE#='||l.id1, 'RT', 'THREAD='||l.id1, 'TS', DECODE(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'), 'ID1='||l.id1||' ID2='||l.id2) object FROM sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.USER$ u, sys.v_$process p WHERE s.paddr = p.addr(+) AND l.sid = s.sid AND l.id1 = o.obj#(+) AND o.owner# = u.USER#(+) AND l.TYPE <> 'MR' UNION ALL /*** LATCH HOLDERS ***/ SELECT s.sid, s.serial#, s.process, s.username, s.terminal, 'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr) FROM sys.v_$process p, sys.v_$session s, sys.v_$latchholder h WHERE h.pid = p.pid AND p.addr = s.paddr UNION ALL /*** LATCH WAITERS ***/ SELECT s.sid, s.serial#, s.process, s.username, s.terminal, 'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait FROM sys.v_$session s, sys.v_$process p, sys.v_$latch l WHERE latchwait IS NOT NULL AND p.addr = s.paddr AND p.latchwait = l.addr;
Subscribe to:
Posts (Atom)