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 Go to next message
trantuananh24hg
Messages: 668
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 #296235 is a reply to message #296229] Fri, 25 January 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many question there.
Quote:
This parameter utl_file_dir is not set, how do I set it?


As other initialisation parameter using alter system or modifying init.ora depending on your configuration.

Quote:
how to get SCN exactly priod the example data was dropped

When you start Log Miner on logs search for the last reference to the dropped object in V$LOGMNR_CONTENTS.

More details in Database Utilities, Chapter 17 Using LogMiner to Analyze Redo Log Files

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #296339 is a reply to message #296235] Fri, 25 January 2008 21:54 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #296342 is a reply to message #296229] Fri, 25 January 2008 22:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I could be mistaken as I am getting older & my memory is not what it used to be.

Looking in the alertSID.log file might get you close.

SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE '%LOG%';

One of the views will show when previous logfile switch happened.
Re: How do I get SCN from DBMS_LGMNR? [message #296343 is a reply to message #296342] Fri, 25 January 2008 22:42 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #296344 is a reply to message #296229] Fri, 25 January 2008 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
The last 2 days I have been reading "RMAN Recipes for Oracle Database 11g" by Darl Kuhn, Sam Alapati & Arup Nanda.
ISBN-13 978-1-59059-851-1 or ISBN-10 1-59059-851-2

The #1 COMMANDMENT for any DBA is:
Thou shalt NEVER lose any data.

I have Good News & I have Bad News.
The Good News is that with proper planning & careful execution the DBA can achive #1.
The Bad News is that between Murphy's Law & carelessness it is really easy to fail at #1.

Since I have been doing Oracle for the last 10+ years, either I have been lucky or good, because I have not lost any data so far!

Re: How do I get SCN from DBMS_LGMNR? [message #296382 is a reply to message #296344] Sat, 26 January 2008 08:33 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>Since I have been doing Oracle for the last 10+ years, either >I have been lucky or good, because I have not lost any data so far!
All the best for future also Smile
Re: How do I get SCN from DBMS_LGMNR? [message #296476 is a reply to message #296382] Sun, 27 January 2008 22:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #296490 is a reply to message #296476] Sun, 27 January 2008 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Are you sure this is the correct log file?
2/ Are you sure this the correct dictionary file?
3/ Are you sure this is the correct statement?
4/ I am sure the answer to the previous question is no. Oracle does not work with name, it works with id.

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #296511 is a reply to message #296490] Mon, 28 January 2008 00:55 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #296535 is a reply to message #296534] Mon, 28 January 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oah, what the long SCN! May you guide me how to format the SCN shorter?

SCN is SCN you can't make it shorter but you can displau it correctly:
SQL> select 12345467890123456789 from dual;
12345467890123456789
--------------------
          1,2345E+19

1 row selected.

SQL> set numwidth 30
SQL> /
          12345467890123456789
------------------------------
          12345467890123456789

1 row selected.

What is the relation of your RMAN and Log Miner?
Do you mix 2 questions?
(I still didn't read that part.)

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #296544 is a reply to message #296535] Mon, 28 January 2008 02:31 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #297277 is a reply to message #297258] Wed, 30 January 2008 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because at this SCN drop is already done.
You should recover BEFORE this SCN.

In addition, delete on obj$ may not be the FIRST recursive query to drop the table. There may be many other things, you have to check them (for instance, delete on tab$, col$, con$, seg$...).

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #297318 is a reply to message #297277] Thu, 31 January 2008 02:20 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #297323 is a reply to message #297318] Thu, 31 January 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To recover just BEFORE a SCN, you can just recover UNTIL this SCN-1.

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #297333 is a reply to message #297323] Thu, 31 January 2008 03:02 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I am sorry, Michel, I've just not understood your words!
Re: How do I get SCN from DBMS_LGMNR? [message #297334 is a reply to message #297333] Thu, 31 January 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't be sorry, it happens to me each day.
Which post didn't you understand, the one you last answered or the previous one? Do you understand it now?

Regards
Michel
Re: How do I get SCN from DBMS_LGMNR? [message #297471 is a reply to message #297334] Thu, 31 January 2008 21:46 Go to previous message
trantuananh24hg
Messages: 668
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 Embarassed

You're respectability and I need to say "sorry" when I'm wrong.

Previous Topic: Backup and Recovery
Next Topic: Online or Device Back Up?
Goto Forum:
  


Current Time: Sat Dec 03 09:40:57 CST 2016

Total time taken to generate the page: 0.08617 seconds