Home » RDBMS Server » Backup & Recovery » How do I get SCN from DBMS_LGMNR?
| How do I get SCN from DBMS_LGMNR? [message #296229] |
Fri, 25 January 2008 03:13  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
My DB is 10gR2, disable Flashback and running with archivelog.
I have a question, how to get SCN exactly priod the example data was dropped by using DBMS_LOGMNR
Example:
1. Create and drop a table
SQL> create table Test as select * from all_objects
table created.
SQL> alter system switch logfile;
system altered.
SQL> drop table Test purge;
table dropped.
SQL> alter system switch logfile;
system altered.
2. Use DBMS_LOGMNR
SQL> select object_name from dba_objects
2 where owner='SYS'
3 and object_name='DBMS_LOGMNR';
OBJECT_NAME
-----------------------------------------
DBMS_LOGMNR
DBMS_LOGMNR
SQL> begin
2 dbms_logmnr_d.build('miner_dic.dat','c:\temp',options=>dbms_logmnr_d.store_
in_flat_file);
3* end;
SQL> /
begin
*
ERROR at line 1:
ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: at "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: at line 2
SQL>
This parameter utl_file_dir is not set, how do I set it?
My purpose is getting SCN before table TEST dropped, and recovery point to time.
Would you like to clarify more?
Thank you!
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296339 is a reply to message #296235] |
Fri, 25 January 2008 21:54  |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Oh, I saw and I did. Thank you, Michel!
And may you advice me:
We have 2 DB within one server running Solaris, 4 SATA IDE, 8GB RAM, 4 CPUs, and it names V890. One DB has NOARCHIVELOG, the other has. I disabled all Flashback feature, only used RMAN to backup full at Saturday weekly, and no incrmental, no OS backup. The whole backup is located at this server. Would I like to use Flashback and execute incremental backup daily?
I think, one backup full is all I need, however, some one took mistakes with dropping tables, I can not restore and recovery DB because it's Fully Recovery. I think of RMAN Tablespace Point In Time.
Thank you for your advice!
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296343 is a reply to message #296342] |
Fri, 25 January 2008 22:42 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you, Anna!
I understand what you mean!
So, I think of the wholly strategy of backup which let me recovery or restore easiler, faster and better. You're Senior DBA, and so that, you must have yours, other than checking, taking any logfile, any view which I, you and many DBAs know about it or its contents, do you agree with me?
Let me take an example:
The PROD DB is currently running, one client retrives data from table A, she/he updates it, ok, no problem because of his/her working. Suddenly, the other client drops table A which he/she does not ask any one before, I suppose that the Update Client done, and the Drop Client did it after. Oh, as DBA, I must restore it - the table as soon as possible, are you agreement?? I don't care about view, it's my private working, and my boss, my customers, my DBs don't care, too.
All of that I want are: In my situation, what do you do with? Will you waste time to checking the logfile, alertSID.ora, etc or you restore the table by the backup strategy which you setup successfully before?
The main idea which I submitted that I have very very few experiences, you, Michel, and the other have much. I need an advice to do from you.
Thank you very much!
[Updated on: Fri, 25 January 2008 22:45] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296476 is a reply to message #296382] |
Sun, 27 January 2008 22:09 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
I've ordered this book, and I hope it will arrive as soon as possible, thank you for your advice.
And, why did I not select SCN..
SQL> drop table test purge;
drop table test purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table test as select * from scott.dept@test_meta;
Table created.
SQL> commit;
Commit complete.
SQL> drop table test purge;
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL> begin
2 sys.dbms_logmnr.add_logfile('C:\archive_TEST\ARC00013_0644955117.001'
3 ,sys.dbms_logmnr.NEW);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> begin
2 sys.dbms_logmnr.start_logmnr(
3 dictFileName=>'c:\temp\test.mnr');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select scn, sql_redo
2 from v$logmnr_contents
3 where sql_redo like 'delete from SYS.OBJ$%''DEPT''%';
no rows selected
SQL> show user
USER is "SYS"
SQL>
Would you like to guide me more?
Thank you very much!
[Updated on: Sun, 27 January 2008 22:10] Report message to a moderator |
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296511 is a reply to message #296490] |
Mon, 28 January 2008 00:55 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you for your reply, Michel!
Before I posted my questions, I guessed some answers like yours, and so that, to ensure objectively, I run again..
By anyway, I've just enable Flashback, but, please don't care about it. I am sure this is not to be test with situation which I would like to get SCN from LOGMINER after dropping a table, and I would like to use RMAN recovery incomplete - point_in_time.
Delete all of backup file and archivelog file
C:\>rman catalog=rman/rman@meta target=sys/test@test
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 28 13:32:40 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=1943000103)
connected to recovery catalog database
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2067 Full 1.17G DISK 00:04:35 28-JAN-08
BP Key: 2069 Status: AVAILABLE Compressed: NO Tag: TAG20080128T12132
5
Piece Name: C:\TEST_BACKUP\BACKUPTEST_DB_0KJ79N35_20_1
List of Datafiles in backup set 2067
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 18772901170 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTE
M01.DBF
2 Full 18772901170 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOT
BS01.DBF
3 Full 18772901170 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAU
X01.DBF
4 Full 18772901170 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS
01.DBF
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
1919 1 13 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00013_0644955117.001
1934 1 14 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00014_0644955117.001
1945 1 15 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00015_0644955117.001
2027 1 16 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00016_0644955117.001
2056 1 17 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00017_0644955117.001
2065 1 18 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00018_0644955117.001
2128 1 19 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00019_0644955117.001
2129 1 19 A 28-JAN-08 C:\FLASHBACK_TEST\TEST\ARCHIVELOG\2008_01_28\O1
_MF_1_19_3STTJX0H_.ARC
RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00013_0644955117.001 recid=27 stamp=6451
88771
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00014_0644955117.001 recid=28 stamp=6451
91373
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00015_0644955117.001 recid=29 stamp=6451
91644
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00016_0644955117.001 recid=30 stamp=6451
91799
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00017_0644955117.001 recid=31 stamp=6451
92800
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00018_0644955117.001 recid=32 stamp=6451
93081
validation succeeded for archived log
archive log filename=C:\ARCHIVE_TEST\ARC00019_0644955117.001 recid=33 stamp=6451
94253
validation succeeded for archived log
archive log filename=C:\FLASHBACK_TEST\TEST\ARCHIVELOG\2008_01_28\O1_MF_1_19_3ST
TJX0H_.ARC recid=34 stamp=645194253
Crosschecked 8 objects
RMAN> RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
1919 1 13 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00013_0644955117.001
1934 1 14 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00014_0644955117.001
1945 1 15 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00015_0644955117.001
2027 1 16 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00016_0644955117.001
2056 1 17 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00017_0644955117.001
2065 1 18 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00018_0644955117.001
2128 1 19 A 28-JAN-08 C:\ARCHIVE_TEST\ARC00019_0644955117.001
2129 1 19 A 28-JAN-08 C:\FLASHBACK_TEST\TEST\ARCHIVELOG\2008_01_28\O1
_MF_1_19_3STTJX0H_.ARC
Do you really want to delete the above objects (enter YES or NO)? y
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00013_0644955117.001 recid=27 stamp=6451
88771
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00014_0644955117.001 recid=28 stamp=6451
91373
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00015_0644955117.001 recid=29 stamp=6451
91644
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00016_0644955117.001 recid=30 stamp=6451
91799
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00017_0644955117.001 recid=31 stamp=6451
92800
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00018_0644955117.001 recid=32 stamp=6451
93081
deleted archive log
archive log filename=C:\ARCHIVE_TEST\ARC00019_0644955117.001 recid=33 stamp=6451
94253
deleted archive log
archive log filename=C:\FLASHBACK_TEST\TEST\ARCHIVELOG\2008_01_28\O1_MF_1_19_3ST
TJX0H_.ARC recid=34 stamp=645194253
Deleted 8 objects
RMAN>RMAN> delete backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
2068 2066 1 1 AVAILABLE DISK C:\TEST_BACKUP\BACKUPTEST_DB_0JJ
79N32_19_1
2069 2067 1 1 AVAILABLE DISK C:\TEST_BACKUP\BACKUPTEST_DB_0KJ
79N35_20_1
2092 2087 1 1 AVAILABLE DISK C:\TEST_BACKUP\BACKUPTEST_DB_0LJ
79NBQ_21_1
2104 2102 1 1 AVAILABLE DISK C:\ORACLE\PRODUCT\10.2.0\NEO\DAT
ABASE\C-1943000103-20080128-01
2133 2130 1 1 AVAILABLE DISK C:\FLASHBACK_TEST\TEST\AUTOBACKU
P\2008_01_28\O1_MF_S_645193831_3STT51K2_.BKP
Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=C:\TEST_BACKUP\BACKUPTEST_DB_0JJ79N32_19_1 recid=16 stamp=64
5192803
deleted backup piece
backup piece handle=C:\TEST_BACKUP\BACKUPTEST_DB_0KJ79N35_20_1 recid=17 stamp=64
5192805
deleted backup piece
backup piece handle=C:\TEST_BACKUP\BACKUPTEST_DB_0LJ79NBQ_21_1 recid=18 stamp=64
5193083
deleted backup piece
backup piece handle=C:\ORACLE\PRODUCT\10.2.0\NEO\DATABASE\C-1943000103-20080128-
01 recid=19 stamp=645193087
deleted backup piece
backup piece handle=C:\FLASHBACK_TEST\TEST\AUTOBACKUP\2008_01_28\O1_MF_S_6451938
31_3STT51K2_.BKP recid=20 stamp=645193873
Deleted 5 objects
RMAN>
Rebackup database
RMAN> backup database ;
Starting backup at 28-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DB
F
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
channel ORA_DISK_1: starting piece 1 at 28-JAN-08
channel ORA_DISK_1: finished piece 1 at 28-JAN-08
piece handle=C:\TEST_BACKUP\BACKUPTEST_DB_0OJ79RTM_24_1 tag=TAG20080128T133550 c
omment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:46
Finished backup at 28-JAN-08
Starting Control File and SPFILE Autobackup at 28-JAN-08
piece handle=C:\FLASHBACK_TEST\TEST\AUTOBACKUP\2008_01_28\O1_MF_S_645198039_3STY
793X_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 28-JAN-08
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2155 Full 1.17G DISK 00:04:42 28-JAN-08
BP Key: 2156 Status: AVAILABLE Compressed: NO Tag: TAG20080128T13355
0
Piece Name: C:\TEST_BACKUP\BACKUPTEST_DB_0OJ79RTM_24_1
List of Datafiles in backup set 2155
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 18772904055 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTE
M01.DBF
2 Full 18772904055 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOT
BS01.DBF
3 Full 18772904055 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAU
X01.DBF
4 Full 18772904055 28-JAN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS
01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2169 Full 6.86M DISK 00:00:03 28-JAN-08
BP Key: 2174 Status: AVAILABLE Compressed: NO Tag: TAG20080128T13403
9
Piece Name: C:\FLASHBACK_TEST\TEST\AUTOBACKUP\2008_01_28\O1_MF_S_6451980
39_3STY793X_.BKP
Control File Included: Ckp SCN: 18772904165 Ckp time: 28-JAN-08
SPFILE Included: Modification time: 28-JAN-08
RMAN>
Drop, recreate Database link TEST_META and Test table from TEST database
I have 2 Database, one is META, one is TEST. Because of non example schema in TEST, so that, I insert into Test by using database link which connects to META.
RMAN> exit
Recovery Manager complete.
C:\>sqlplus sys/test@test as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 28 13:43:31 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> drop database link test_meta;
Database link dropped.
SQL> create databsae link test_meta connect to SCOTT identified by TIGER using '
META';
create databsae link test_meta connect to SCOTT identified by TIGER using 'META'
*
ERROR at line 1:
ORA-00901: invalid CREATE command
SQL> create database link test_meta connect to SCOTT identified by TIGER using '
META';
Database link created.
SQL> drop table test purge;
Table dropped.
SQL> commit;
Commit complete.
SQL> create table test as select * from scott.dept@test_meta;
Table created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> drop table test purge;
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL>
Recreate DictFileName with DBMS_LOGMNR
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string c:\temp
SQL>SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string C:\archive_TEST
SQL> begin
2 sys.dbms_logmnr_d.build('test.mnr','c:\temp');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 sys.dbms_logmnr.add_logfile('c:\archive_TEST\ARC00022_0644955117.001'
3 ,sys.dbms_logmnr.NEW);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> begin
2 sys.dbms_logmnr.start_logmnr(dictFilename=>'C:\temp\test.mnr');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select scn, sql_redo
2 from v$logmnr_contents
3 where sql_redo like 'delete from SYS.OBJ$%''DEPT''%';
no rows selected
SQL>
I am very suprised..
And, I am sorry if I did not understand exactly your answers.
1/ New ArchiveLog File is generated, the log_dest_ is correct
2/ I submit with DBMS_LOGMNR_D.BUILD the new Dictionary File
3/ The statement which, I am sorry, I thought that are all above, am I right or wrong?
Thank you for clarifing me!
[Updated on: Mon, 28 January 2008 00:58] Report message to a moderator |
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296521 is a reply to message #296511] |
Mon, 28 January 2008 01:17 |
 |
Michel Cadot
Messages: 68651
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1/ The (archived) log must be the one that contains the drop statement (I mean the recursive calls that implements this one)
2/ You build the dictionary AFTER the drop, so it does not contain your table
3/ I'm pretty sure the statement is more like "delete [from] sys.obj$ where [data]obj#=..." maybe in lower, upper or mixed case, with or without "from", using dataobj# or obj#...
As you are in 10g, Oracle kows how to translate recursive calls to original statement. So you don't have to search for this statement but for "DROP TABLE" (if you have a correct dictionary).
Note: I don't read all the stuff.
Regards
Michel
[Updated on: Mon, 28 January 2008 01:17] Report message to a moderator |
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296534 is a reply to message #296521] |
Mon, 28 January 2008 01:49 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Oh, that's my mistake, thank you, Michel!
I've just searched the SCN from v$logmnr_contents, 2 results returned with SCN
SQL> spool c:\test.txt create
SQL> select scn from v$logmnr_contents
2 where sql_redo like 'delete from%';
SCN
----------
1.8773E+10
1.8773E+10
SQL> spool off
SQL>
And I tried to get SCN from v$archived_log
FIRST_CHANGE# SEQUENCE#
------------- ----------
1.8773E+10 9
1.8773E+10 10
1.8773E+10 11
1.8773E+10 12
1.8773E+10 13
1.8773E+10 14
1.8773E+10 15
1.8773E+10 16
1.8773E+10 17
1.8773E+10 18
1.8773E+10 19
FIRST_CHANGE# SEQUENCE#
------------- ----------
1.8773E+10 19
1.8773E+10 20
1.8773E+10 20
1.8773E+10 21
1.8773E+10 21
1.8773E+10 22
1.8773E+10 22
1.8773E+10 23
1.8773E+10 23
1.8773E+10 24
1.8773E+10 24
22 rows selected.
SQL>
Oah, what the long SCN! May you guide me how to format the SCN shorter?
It's seem that I have to recover Database with Sequence. Then, I try...
RMAN> run{
2> set until sequence 23;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 28-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=138 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\TEST_BACKUP\BACKUPTEST_DB_0OJ7
RTM_24_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/28/2008 14:48:19
ORA-19870: error reading backup piece C:\TEST_BACKUP\BACKUPTEST_DB_0OJ79RTM_24_
ORA-19573: cannot obtain exclusive enqueue for datafile 4
RMAN>
Oh, no! This is 2 times I have this error.
By anyway, thank you very much!
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #296544 is a reply to message #296535] |
Mon, 28 January 2008 02:31 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
oh, I love you, Michel!
I got the full number of SCN. Thank again!
Because I just want to use RMAN to recover point_in_time, then, I tested with this above example.
1/ Drop table Test
2/ Get SCN after dropped with DBMS_LOGMNR
3/ Use RMAN recover point_in_time with Full backup before.
And I know there are 3 kinds of Incomplete Recovery:
+ Time-Based
+ Cancel-Based
+ Changed-Based
The Time and Cancle - Based is not 100% accurate, this is the reason which made me use DBMS_LOGMNR. And, because of ARCHIVELOG mode, I want to use RMAN to recover point_in_time instead of shutdown Database, startup mount and write sql statement 'recover automatic database until change..' .
I am thinking about Flashback again..
[Updated on: Mon, 28 January 2008 02:37] Report message to a moderator |
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #297258 is a reply to message #296544] |
Wed, 30 January 2008 21:48 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Ola!
I just found the SCN by DBMS_LOGMNR, but...
C:\>sqlplus sys/test@test as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 31 10:28:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table test as select * from scott.emp@test_meta
2 ;
Table created.
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> begin
2 dbms_logmnr_d.build('dictionary.dat','c:\utl_file_dir');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> drop table test;
Table dropped.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> begin
2 dbms_logmnr.add_logfile('c:\archive_TEST\ARC_00005_0645382294.ARC001',
3 dbms_logmnr.NEW);
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_logmnr.start_logmnr(dictFileName=>'c:\utl_file_dir\dictionary.dat');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> set numwidth 30
SQL> sql scn, sql_redo
SP2-0734: unknown command beginning "sql scn, s..." - rest of line ignored.
SQL> select scn, sql_redo
2 from v$logmnr_contents
3 where sql_redo like 'delete from%TEST';
no rows selected
SQL> ed
Wrote file afiedt.buf
1 select scn, sql_redo
2 from v$logmnr_contents
3* where sql_redo like 'delete from%TEST%'
SQL> /
SCN
------------------------------
SQL_REDO
--------------------------------------------------------------------------------
18773011999
delete from "SYS"."OBJ$" where "OBJ#" = '49077' and "DATAOBJ#" = '49077' and "OW
NER#" = '0' and "NAME" = 'TEST' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and
"TYPE#" = '2' and "CTIME" = TO_DATE('31-JAN-08', 'DD-MON-RR') and "MTIME" = TO_D
ATE('31-JAN-08', 'DD-MON-RR') and "STIME" = TO_DATE('31-JAN-08', 'DD-MON-RR') an
d "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" =
'0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NUL
L and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AA
AAASAABAAAMHIAAl';
SCN
------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 1250788 bytes
Variable Size 192940572 bytes
Database Buffers 473956352 bytes
Redo Buffers 2940928 bytes
Database mounted.
SQL> recover database until change 18773011999;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> desc test
ERROR:
ORA-04043: object test does not exist
SQL>
Would you like to clarify me why I did not recover this table?
Thank you very much!
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #297318 is a reply to message #297277] |
Thu, 31 January 2008 02:20 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you, Michel!
I understood.
But, I am sorry if I am wrong. It's seem that there are 2 commands
SQL> recover database until change SCN
SQL> recover database until time 'TIME'
SQL> recover database until cancel
In this situation, can I use the fullbackup before created by RMAN to recover incomplete?
Thank you!
|
|
|
|
|
|
|
|
|
|
| Re: How do I get SCN from DBMS_LGMNR? [message #297471 is a reply to message #297334] |
Thu, 31 January 2008 21:46 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Yes, Michel!
I understand said you that "..before SCN". Yesterday, I tried to test again, however, I're still a guy when cleared and deleted all archive_log_file 
You're respectability and I need to say "sorry" when I'm wrong.
|
|
|
|
Goto Forum:
Current Time: Wed May 15 00:07:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
