SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 12 15:02:09 2007 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> alter database archivelog 2 / Database altered. SQL> alter database open 2 / Database altered. SQL> create table t (a number) 2 / Table created. Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> backup database 2> ; Starting backup at 12-DEC-07 using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=139 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.D BF input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.D BF input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01. DBF input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DB F channel ORA_DISK_1: starting piece 1 at 12-DEC-07 channel ORA_DISK_1: finished piece 1 at 12-DEC-07 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current controlfile in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 12-DEC-07 channel ORA_DISK_1: finished piece 1 at 12-DEC-07 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:20 Finished backup at 12-DEC-07 RMAN> SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS') 2 from dual 3 / TO_CHAR(SYSDATE,'DD ------------------- 12-12-2007 15:09:42 SQL> drop table t 2 / Table dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause using target database controlfile instead of recovery catalog Starting restore at 12-DEC-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=159 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:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB F restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529 channel ORA_DISK_1: restore complete Finished restore at 12-DEC-07 Starting recover at 12-DEC-07 using channel ORA_DISK_1 starting media recovery media recovery complete Finished recover at 12-DEC-07 RMAN> alter database open resetlogs 2> ; database opened RMAN> list incarnation 2> ; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 ORCL1 1025591162 PARENT 1 09-MAR-04 2 2 ORCL1 1025591162 PARENT 318842 12-DEC-07<---------set this 3 3 ORCL1 1025591162 CURRENT 331575 12-DEC-07<-----its current SQL> conn sys/sys as sysdba Connected. SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER SQL> drop table t 2 / Table dropped. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes Database mounted. SQL> Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle. All rights reserved. RMAN> connect target sys/sys connected to target database: ORCL1 (DBID=1025591162) RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause using target database controlfile instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of set command at 12/12/2007 15:31:22 RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time RMAN> shutdown immediate using target database controlfile instead of recovery catalog database dismounted Oracle instance shut down RMAN> startup nomount connected to target database (not started) Oracle instance started Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 145488364 bytes Database Buffers 25165824 bytes Redo Buffers 524288 bytes RMAN> restore controlfile from 'C:\oracle\flash_recovery_area\orcl1\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP' 2> ; Starting restore at 12-DEC-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK channel ORA_DISK_1: restoring controlfile channel ORA_DISK_1: restore complete output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL Finished restore at 12-DEC-07 RMAN> startup mount database is already started database mounted RMAN> reset database to incarnation 2 2> ; Starting implicit crosscheck backup at 12-DEC-07 released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=160 devtype=DISK Crosschecked 1 objects Finished implicit crosscheck backup at 12-DEC-07 Starting implicit crosscheck copy at 12-DEC-07 using channel ORA_DISK_1 Finished implicit crosscheck copy at 12-DEC-07 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF _1_5_3OZFHKNJ_.ARC File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_ NCSNF_TAG20071212T150529_3OZDPGX9_.BKP database reset to incarnation 2 RMAN> run 2> { 3> set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')"; 4> restore database; 5> recover database; 6> } executing command: SET until clause ORACLE error from target database: ORA-19922: there is no parent row with id 0 and level 0 ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038 ORA-06512: at line 1 RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row s Starting restore at 12-DEC-07 using channel ORA_DISK_1 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:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB F restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF channel ORA_DISK_1: restored backup piece 1 piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529 channel ORA_DISK_1: restore complete Finished restore at 12-DEC-07 Starting recover at 12-DEC-07 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1 2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5 media recovery complete Finished recover at 12-DEC-07 RMAN> alter database open resetlogs 2> ; database opened Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER SQL> select name from v$database 2 / NAME --------- ORCL1 You can recover the same table by using flashback technologies its by default enabled at 10g ,you can recover table from recycle bin unless you dont purge it, Recycle Bin catches receives database objects and its dependent objects i.e PK, Index etc when you drop table. SQL> desc dept Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 TABLE 2008-02-11:23:19:28 SQL> drop table dept 2 / Table dropped. SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- DEPT BIN$aAEWEykwReeRQG4USzgP8w==$0 TABLE 2008-02-12:15:59:12 EMP BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 TABLE 2008-02-11:23:19:28 SQL> select object_name,original_name 2 from recyclebin 3 / OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN$5c4ILi0WRlq9WhqKxCr/1A==$0 PK_EMP BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 EMP BIN$m4hxC9lWQe67YFtifqXGNg==$0 PK_DEPT BIN$aAEWEykwReeRQG4USzgP8w==$0 DEPT SQL> flashback table "BIN$aAEWEykwReeRQG4USzgP8w==$0" to before drop 2 / Flashback complete. SQL> desc dept Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)
Tuesday, February 12, 2008
How to recover user errors
There is traditional way to recover the user errors i.e drop table by doing
incomplete recovery till the time before dropped the table,in oracle 9i you can
recover the user errors by flashback technologies flashback drop by using
recycle bin,here i am not considering the space issues of recyclebin.
Subscribe to:
Post Comments (Atom)
1 comment:
I have heard about another way of repair sql2005 mdl files. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.
Post a Comment