OraDataSystems.com

Wednesday, March 10, 2010

Recovering from Loss of All Archive Log Files

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
549942

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
549942
549942
549942
549942

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
549942
549942
549942
549942

SQL> alter system checkpoint;

System altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
550020

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
550020
550020
550020
550020

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
550020
550020
550020
550020

RMAN> backup database format 'C:\Oracle\rmanbackup\%U';

Starting backup at 11-NOV-10
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=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 11-NOV-10
channel ORA_DISK_1: finished piece 1 at 11-NOV-10
piece handle=C:\ORACLE\RMANBACKUP\05LSNL67_1_1 tag=TAG20101111T090927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 11-NOV-10
channel ORA_DISK_1: finished piece 1 at 11-NOV-10
piece handle=C:\ORACLE\RMANBACKUP\06LSNL7V_1_1 tag=TAG20101111T090927 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-NOV-10

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
550020
550020
550020
550020

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
550020

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
550899
550899
550899
550899

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
551455

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
551455
551455
551455
551455

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
551455
551455
551455
551455

SQL> shutdown abort
ORACLE instance shut down.

--Forcefull corrupted USERS01.DBF file
--Delete ALL archive log files

SQL> startup nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 184551676 bytes
Database Buffers 419430400 bytes
Redo Buffers 7094272 bytes
SQL> alter database mount
2 ;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF'

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
551455

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
551455
551455
551455
0

SQL> select name, checkpoint_change# from v$datafile_header;

NAME CHECKPOINT_CHANGE#
-----------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF 551455
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF 551455
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF 551455
0


SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
551455
551455
551455
551455

-- Restore database from last FULL backup

RMAN> restore database;

Starting restore at 11-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 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\TESTDB\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\RMANBACKUP\05LSNL67_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\RMANBACKUP\05LSNL67_1_1 tag=TAG20101111T090927
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 11-NOV-10

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 TESTDB 2518047087 PARENT 1 19-NOV-06
2 2 TESTDB 2518047087 CURRENT 538268 10-NOV-10

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF'

SQL> recover database;
ORA-00279: change 550899 generated at 11/11/2010 09:09:28 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2010_11_11\O1_MF_
1_4_%U_.ARC
ORA-00280: change 550899 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2010_11_11\O1_MF
_1_4_6FR9M7M6_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2010_11_11\O1_MF
_1_4_6FR9M7M6_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> alter database backup controlfile to trace as 'C:\Oracle\product\10.2.0\oradata\testdb\backupctlfile.sql';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 188745980 bytes
Database Buffers 415236096 bytes
Redo Buffers 7094272 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO01.LOG' SIZE 50M,
9 GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO02.LOG' SIZE 50M,
10 GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO03.LOG' SIZE 50M
11 DATAFILE
12 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF',
13 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF',
14 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF',
15 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF'
16 CHARACTER SET WE8MSWIN1252
17 ;

Control file created.

--Check the timestamp on the newly created control files

-rwxrwx--- 1 Administrators SYSTEM 7389184 Nov 11 09:42 CONTROL01.CTL
-rwxrwx--- 1 Administrators SYSTEM 7389184 Nov 11 09:42 CONTROL02.CTL
-rwxrwx--- 1 Administrators SYSTEM 7389184 Nov 11 09:42 CONTROL03.CTL

-- Resetting Logs while creating control file reset the database SCN in the control file to 0

SQL>

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
0


SQL> select name, checkpoint_change# from v$datafile_header;

NAME CHECKPOINT_CHANGE#
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF 550899
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF 550899
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF 550899
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF 550899

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
550899
550899
550899
550899

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
550901

SQL> select name, checkpoint_change# from v$datafile_header;

NAME CHECKPOINT_CHANGE#
------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF 550901
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DBF 550901
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF 550901
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF 550901


SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
550901
550901
550901
550901
Posted by at No comments:

What's this blog about

This is blog is about keeping track of the information I collect, technical or otherwise, so as to give myself abn incentive to keep doing what I do best and that is collecting knowledge.
Posted by at No comments:
Subscribe to: Posts (Atom)