Current online redo logfile loss Handling Method

Current online redo logfile loss Handling Method

We have previously performed a recovery drill after the rm-rf operation, and there is no backup. Today, before performing destructive operations, we made an rman full backup, and then deleted all database files online, including control files, data files, online log files, and archive files. Let's see how to restore the database. Because the current redo logfile is lost, data will be lost in this test, so it can be used as a case of Incomplete recovery.

-- Log on to the database for dml operations
[Oracle @ ora10g ~] $ Sqlplus/as sysdba
 

SQL * Plus: Release 10.2.0.1.0-Production on Tue Aug 26 13:40:37 2014
 

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
 

SQL> select * from aaron8219.test1;
 

INT
----------
1
2
 

SQL> insert into on8219.test1 values (3 );
 

1 row created.
 

SQL> select group #, status, sequence # from v $ log;
 

GROUP # status sequence #
------------------------------------
1 INACTIVE 1
2 CURRENT 2
3 INACTIVE 0
 

-- Do not submit. Open another session to perform the rm-rf operation.
[Root @ ora10g ~] # Cd/u01/app/oracle/oradata
[Root @ ora10g oradata] # ll
Total 4
Drwxr-x --- 2 oracle oinstall 4096 Aug 25 ora10g
[Root @ ora10g oradata] # rm-rf ora10g/
[Root @ ora10g oradata] # ll
Total 0
 

-- The original session is directly disabled by abort, simulating the loss of current online redo logfile
SQL> shutdown abort
 

-- Use the generated rman full backup to restore various database files
[Oracle @ ora10g ~] $ Rman target/
 

Recovery Manager: Release 10.2.0.1.0-Production on Tue Aug 26 13:44:58 2014
 

Copyright (c) 1982,200 5, Oracle. All rights reserved.
 

Connected to target database: ora10g (not mounted)
 

RMAN> restore controlfile from '/rmanbak/full_ora10g_1095411955_20140826_02pgtq5h_1_1.bak ';
 

Starting restore at 26-AUG-14
Using channel ORA_DISK_1
 

Channel ORA_DISK_1: restoring control file
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Output filename =/u01/app/oracle/oradata/ora10g/control01.ctl
Output filename =/u01/app/oracle/oradata/ora10g/control02.ctl
Output filename =/u01/app/oracle/oradata/ora10g/control03.ctl
Finished restore at 26-AUG-14
 

RMAN> alter database mount;
 

Database mounted
Released channel: ORA_DISK_1
 

RMAN> restore database;
 

Starting restore at 26-AUG-14
Starting implicit crosscheck backup at 26-AUG-14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 156 devtype = DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-AUG-14
 

Starting implicit crosscheck copy at 26-AUG-14
Using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-AUG-14
 

Searching for all files in the recovery area
Cataloging files...
Cataloging done
 

List of Cataloged Files
======================================
File Name:/u01/app/oracle/flash_recovery_area/ORA10G/autobackup/2014_08_26/o1_mf_s_856615092_9zr3snsq _. bkp
 

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/u01/app/oracle/oradata/ora10g/system01.dbf
Restoring datafile 00002 to/u01/app/oracle/oradata/ora10g/undotbs01.dbf
Restoring datafile 00003 to/u01/app/oracle/oradata/ora10g/sysaux01.dbf
Restoring datafile 00004 to/u01/app/oracle/oradata/ora10g/users01.dbf
Restoring datafile 00005 to/u01/app/oracle/oradata/ora10g/example01.dbf
Channel ORA_DISK_1: reading from backup piece/rmanbak/full_ora10g_00005411955_20140826_01pgtq36_00001.bak
Channel ORA_DISK_1: restored backup piece 1
Piece handle =/rmanbak/full_ora10g_1095411955_20140826_01pgtq36_00001.bak tag = TAG20140826T123653
Channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 26-AUG-14
 

RMAN> exit
 

Recovery Manager complete.
 

Since rman does not back up online log files, just now rman only backs up full-database backup and control files. We can see that the three files redo01.log, redo02.log, and redo03.log do not exist.
 

[Oracle @ ora10g ~] $ Sqlplus/as sysdba
 

SQL * Plus: Release 10.2.0.1.0-Production on Tue Aug 26 14:52:35 2014
 

Copyright (c) 1982,200 5, Oracle. All rights reserved.
 

 

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
 

SQL> select open_mode from v $ database;
 

OPEN_MODE
----------
MOUNTED
 

SQL> col member for a60
SQL> select group #, member from v $ logfile;
 

GROUP # MEMBER
----------------------------------------------------------------------
3/u01/app/oracle/oradata/ora10g/redo03.log
2/u01/app/oracle/oradata/ora10g/redo02.log
1/u01/app/oracle/oradata/ora10g/redo01.log

SQL> alter database open resetlogs;
Alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10g/system01.dbf'
 

The database cannot be opened because it only restores the database and does not have a recover.
 

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done
 

 

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the backup controlfile option must be done
 

 

SQL> recover database using backup controlfile;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion:
/U01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3 _ % u _. arc
ORA-00280: change 502729 for thread 1 is in sequence #3
 

 

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
Auto
ORA-00308: cannot open archived log
'/U01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3 _ % u _. arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
 

 

ORA-00308: cannot open archived log
'/U01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3 _ % u _. arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
 

 

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion:
/U01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3 _ % u _. arc
ORA-00280: change 502729 for thread 1 is in sequence #3
 

 

Specify log: {<RET> = suggested | filename | AUTO | CANCEL}
Cancel
Media recovery canceled.
SQL>
 

Because the previous archive does not use rman backup, but does not exist now, you cannot recover the database, even if you use resetlogs, you cannot open the database. In this case, only the implicit parameter _ allow_resetlogs_uption is used to open the database.
 

SQL> show parameter spfile
 

NAME TYPE VALUE
-----------------------------------------------------------------------------
Spfile string/u01/app/oracle/product/10.2.0
/Db_1/dbs/spfileora10g. ora
 

-- Create pfile
SQL> create pfile from spfile;
 

File created.
 

-- Edit the $ ORACLE_HOME/dbs/initora10g. ora initialization parameter file and add *. _ allow_resetlogs_uption = true at the end of the file.
[Oracle @ ora10g ora10g] $ vi $ ORACLE_HOME/dbs/initora10g. ora
 

Ora10g. _ db_cache_size = 180355072
Ora10g. _ java_pool_size = 4194304
Ora10g. _ large_pool_size = 4194304
Ora10g. _ shared_pool_size = 92274688
Ora10g. _ streams_pool_size = 0
*. Audit_file_dest = '/u01/app/oracle/admin/ora10g/adump'
*. Background_dump_dest = '/u01/app/oracle/admin/ora10g/bdump'
*. Compatible = '10. 2.0.1.0'
*. Control_files = '/u01/app/oracle/oradata/ora10g/control01.ctl', '/u01/app/oracle/oradata/ora10g/control02.ctl ', '/u01/app/oracle/oradata/ora10g/control03.ctl' # Restore Controlfile
*. Core_dump_dest = '/u01/app/oracle/admin/ora10g/cdump'
*. Db_block_size = 8192
*. Db_domain =''
*. Db_file_multiblock_read_count = 16
*. Db_name = 'ora10g'
*. Db_recovery_file_dest = '/u01/app/oracle/flash_recovery_area'
*. Db_recovery_file_dest_size = 2147483648
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = ora10gXDB )'
*. Job_queue_processes = 10
*. Log_archive_format = '% t _ % s _ % r. dbf'
*. Nls_language = 'simplified CHINESE'
*. Nls_territory = 'China'
*. Open_cursors = 300
*. Pga_aggregate_target = 94371840
*. Processses = 150
*. Remote_login_passwordfile = 'clusive'
*. Sga_target = 285212672
*. Undo_management = 'auto'
*. Undo_tablespace = 'undotbs1'
*. User_dump_dest = '/u01/app/oracle/admin/ora10g/udump'
*. _ Allow_resetlogs_uption = TRUE
 

-- Shut down the database instance and start it with pfile
SQL> shutdown immediate
ORA-01109: database not open
 

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile = $ ORACLE_HOME/dbs/initora10g. ora
ORACLE instance started.
 

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 

-- Use resetlogs to open the database
SQL> alter database open resetlogs;
 

Database altered.
 

SQL>
 

[Oracle @ ora10g ora10g] $ ll
Total 1051020
-Rw-r ----- 1 oracle oinstall 7061504 Aug 26 control01.ctl
-Rw-r ----- 1 oracle oinstall 7061504 Aug 26 control02.ctl
-Rw-r ----- 1 oracle oinstall 7061504 Aug 26 control03.ctl
-Rw-r ----- 1 oracle oinstall 104865792 Aug 26 example01.dbf
-Rw-r ----- 1 oracle oinstall 52429312 Aug 26 redo01.log
-Rw-r ----- 1 oracle oinstall 52429312 Aug 26 redo02.log
-Rw-r ----- 1 oracle oinstall 52429312 Aug 26 redo03.log
-Rw-r ----- 1 oracle oinstall 251666432 Aug 26 sysaux01.dbf
-Rw-r ----- 1 oracle oinstall 503324672 Aug 26 system01.dbf
-Rw-r ----- 1 oracle oinstall 20979712 Aug 26 temp01.dbf
-Rw-r ----- 1 oracle oinstall 31465472 Aug 26 undotbs01.dbf
-Rw-r ----- 1 oracle oinstall 5251072 Aug 26 users01.dbf
[Oracle @ ora10g ora10g] $
 

View the data file again and find that three online redo logfiles are generated again.
 

-- Resetlogs: returns the SEQUENCE # serial number to 1.
SQL> select group #, sequence # from v $ log;
 

GROUP # SEQUENCE #
--------------------
1 0
2 1
3 0
 

-- The test table only leaves the original database. Because there is no commit, the newly inserted 3rd records are lost. Even if the commit is used, data is lost because the redo logfile is also deleted.

SQL> select * from aaron8219.test1;

INT
----------
1
2

Note: In the end, remove the implicit parameter _ allow_resetlogs_uption = true and recreate the spfile. Otherwise, the database may cause inconsistency in the future. setting this implicit parameter is only the following policy, in order to make the database open, it is imperative to lose the database, because this is a test in extreme cases, generally, data is lost only when the online redo logfile in the current State is lost. If only the inactive redo logfile is lost, even if the alter database open resetlogs is used, it does not mean that 100% of data is lost. In addition, when using the using backup controlfile keyword, you must use open resetlogs to open the database. After you open the database in resetlogs mode, you must perform a full backup of the database again, because the original backup set of the database becomes invalid once the incarnation changes.