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.
Thursday, February 7, 2008
Recovery of a lost datafile without backup
Its the demo for recovery of a lost datafile without backup,but you should have database in archivelog in order to make complete recovery,otherwise in noarchivelog mode you may lose some data.Every steps is self explainatory so i feel need'nt to add some textual description during demo.
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Feb 8 10:35:24 2008 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> select name from v$database 2 / NAME --------- ORCL SQL> create tablespace my_tablespace datafile 'C:\oracle\product\10.1.0\oradata\orcl\my_datafile.dbf' size 500M 2 / Tablespace created. SQL> set linesize 1000 SQL> select * from v$tablespace 2 / TS# NAME INC BIG FLA ---------- ------------------------------ --- --- --- 0 SYSTEM YES NO YES 1 UNDOTBS1 YES NO YES 2 SYSAUX YES NO YES 4 USERS YES NO YES 3 TEMP YES NO YES 6 EXAMPLE YES NO YES 9 MY_TABLESPACE YES NO YES 7 rows selected. SQL> alter user scott default tablespace my_tablespace 2 / User altered. SQL> conn scott/tiger@orcl Connected. SQL> create table my_table as select * from all_objects 2 / Table created. SQL> desc my_table Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> conn sys/sys@orcl as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\>cd C:\oracle\product\10.1.0\oradata\orcl C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf Volume in drive C has no label. Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\orcl 02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF 02/08/2008 10:41 AM 524,296,192 MY_DATAFILE.DBF 02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF 02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF 02/07/2008 11:43 PM 104,865,792 TEMP01.DBF 02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF 02/08/2008 10:41 AM 56,369,152 USERS01.DBF 7 File(s) 1,639,768,064 bytes 0 Dir(s) 63,114,174,464 bytes free C:\oracle\product\10.1.0\oradata\orcl>del my_datafile.dbf C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf Volume in drive C has no label. Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\orcl 02/08/2008 10:41 AM 157,294,592 EXAMPLE01.DBF 02/08/2008 10:41 AM 283,123,712 SYSAUX01.DBF 02/08/2008 10:41 AM 482,353,152 SYSTEM01.DBF 02/07/2008 11:43 PM 104,865,792 TEMP01.DBF 02/08/2008 10:41 AM 31,465,472 UNDOTBS01.DBF 02/08/2008 10:41 AM 56,369,152 USERS01.DBF 6 File(s) 1,115,471,872 bytes 0 Dir(s) 63,638,204,416 bytes free C:\oracle\product\10.1.0\oradata\orcl> SQL> startup 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. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' SQL> select * from v$recover_file 2 / FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- ------------------ ---------- --------- 6 ONLINE ONLINE FILE NOT FOUND 0 SQL>alter database create datafile 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' as 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE01.DBF'; Database altered. C:\oracle\product\10.1.0\oradata\orcl>dir my_*.dbf Volume in drive C has no label. Volume Serial Number is F49D-FF2B Directory of C:\oracle\product\10.1.0\oradata\orcl 02/08/2008 10:53 AM 524,296,192 MY_DATAFILE01.DBF 1 File(s) 524,296,192 bytes 0 Dir(s) 63,110,365,184 bytes free C:\oracle\product\10.1.0\oradata\orcl> SQL> recover tablespace my_tablespace Media recovery complete. SQL> alter database open 2 / Database altered. SQL> conn scott/tiger@orcl Connected. SQL> desc my_table Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL>
Subscribe to:
Posts (Atom)
