Explore Oracle Incomplete recovery-cancel-based recovery Article 2

Explore Oracle Incomplete recovery-cancel-based recovery Article 2

Explore Oracle Incomplete recovery-cancel-based recovery Article 2

Cancel-based Inconsistency Recovery (archive loss) Article 2

Create test table

SQL> conn wwl/wwl

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

WWL001 TABLE

SQL> create table WWL002 as select * from wwl001;

Table created.

SQL> conn/as sysdba

Connected.

Switch logs

SQL> alter system switch logfile;

System altered.

And then create the second table.

SQL> conn wwl/wwl

Connected.

SQL> create table wwl003 as select * from wwl001;

Table created.

View the current log group, and determine the log Group of the current activity. Set it to group 4.

SQL> conn/as sysdba

Connected.

SQL & gt; set line 200

SQL> select * from v $ log;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM

-------------------------------------------------------------------------------------------

4 1 2 134217728 2 yes active 1716929 30-JUL-12

5 1 134217728 2 yes inactive 1692728 27-JUL-12

6 1 3 134217728 2 no current 1720396 30-JUL-12

7 1 0 134217728 2 yes unused 0

Locate the log files in the current log group. There are two log files.

SQL> col member format a30

SQL> select * from v $ logfile;

GROUP # status type member is _

---------------------------------------------------------

7 ONLINE/DBBak2/oradata/WWL/redo7a. log NO

7 ONLINE/DBBak2/oradata/WWL/redo7b. log NO

6 ONLINE/DBBak2/oradata/WWL/redo6a. log NO

6 ONLINE/DBBak2/oradata/WWL/redo6b. log NO

5 stale online/DBBak2/oradata/WWL/redo5a. log NO

5 stale online/DBBak2/oradata/WWL/redo5b. log NO

4 ONLINE/DBBak2/oradata/WWL/redo4a. log NO

4 ONLINE/DBBak2/oradata/WWL/redo4b. log NO

8 rows selected.

Delete the current log group file to simulate online transaction loss:

SQL>! Rm-f/DBBak2/oradata/WWL/redo4a. log

SQL>! Rm-f/DBBak2/oradata/WWL/redo4b. log

Simulate server Power Failure

SQL> shutdown abort;

Oracle instance shut down.

Recovery steps:

1. The current log is lost when you try to start the database.

SQL> startup

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-00313: open failedfor members of log group 4 of thread 1

ORA-00312: online log 4 thread 1: '/DBBak2/oradata/WWL/redo4b. Log'

ORA-27037: unable toobtain file status

Linux Error: 2: No suchfile or directory

Additional information: 3

ORA-00312: online log 4 thread 1: '/DBBak2/oradata/WWL/redo4a. Log'

ORA-27037: unable toobtain file status

Linux Error: 2: No suchfile or directory

Additional information: 3

2. Try Clear redo4

SQL> alter database clear logfile group4;

Alter database clear logfile group 4

*

ERROR at line 1:

ORA-01624: log 4 needed for crash recoveryof instance WWL (thread 1)

ORA-00312: online log 4 thread 1: '/DBBak2/oradata/WWL/redo4a. Log'

ORA-00312: online log 4 thread 1: '/DBBak2/oradata/WWL/redo4b. Log'

3. Cancel-based Incomplete recovery in the current database

SQL> recover database until cancel;

ORA-00279: change 1716930 generated at07/30/2012 11:03:51 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761098.dbf

ORA-00280: change 1716930 for thread 1 isin sequence #2

Specify log :{ = Suggested | filename | AUTO | CANCEL}

Auto

ORA-00279: change 1720396 generated at07/30/2012 13:37:21 needed for thread 1

ORA-00289: suggestion:/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf

ORA-00280: change 1720396 for thread 1 isin sequence #3

ORA-00278: log file '/DBSoft/product/10.2.0/db_1/dbs/arch1_2_789761_8.dbf' no longer needed forthis recovery

ORA-00308: cannot open archived log '/DBSoft/product/10.2.0/db_1/dbs/arch1_3_789761098.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded butOPEN RESETLOGS wocould get error below

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1: '/DBBak2/oradata/WWL/system01.dbf'

After recovery, you must use the resetlogs option to open the database:

SQL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1: '/DBBak2/oradata/WWL/system01.dbf'

Recreate the control file:

SQL> CREATE CONTROLFILE REUSE DATABASE "WWL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

'/DBBak2/oradata/WWL/redo4a. log ',

MAXDATAFILES 100

MAXINSTANCES 8

GROUP 5 (

# MAXLOGHISTORY 292

LOGFILE

GROUP 4 (

'/DBBak2/oradata/WWL/redo4a. log ',

'/DBBak2/oradata/WWL/redo4b. Log'

GROUP 6 (

) SIZE 128 M,

GROUP 5 (

'/DBBak2/oradata/WWL/redo5a. log ',

'/DBBak2/oradata/WWL/redo5b. Log'

'/DBBak2/oradata/WWL/redo7b. Log'

) SIZE 128 M,

GROUP 6 (

'/DBBak2/oradata/WWL/redo6a. log ',

'/DBBak2/oradata/WWL/redo6b. Log'

) SIZE 128 M,

GROUP 7 (

'/DBBak2/oradata/WWL/redo7a. log ',

'/DBBak2/oradata/WWL/redo7b. Log'

) SIZE 128 M

-- STANDBY LOGFILE

DATAFILE

'/DBBak2/oradata/WWL/system01.dbf ',

'/DBBak2/oradata/WWL/undotbs01.dbf ',

'/DBBak2/oradata/WWL/sysaux01.dbf ',

'/DBBak2/oradata/WWL/users01.dbf ',

'/DBBak2/oradata/WWL/wwl001 ',

'/DBBak2/oradata/WWL/wwl002 ',

'/DBBak2/oradata/WWL/wwl003'

Character set ZHS16CGB231280

34;

Control file created.

Open it again, but the result still does not work.

SQL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to beconsistent

ORA-01110: data file 1: '/DBBak2/oradata/WWL/system01.dbf'

You can use the implicit parameter _ allow_resetlogs_uption to open the database.

SQL> alter system set "_ allow_resetlogs_uption" = true scope = spfile;

System altered.

After modifying the parameters, restart the database to the mount status.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

The ORA-01589: must use RESETLOGS orNORESETLOGS option for database open

Start the database in resetlogs Mode

SQL> alter database open resetlogs;

Database altered.

Remember to disable this parameter.

SQL> alter system set "_ allow_resetlogs_uption" = FALSE scope = spfile;

System altered.

Enable the parameter to take effect and start the database again

SQL> startup force;

ORACLE instance started.

Total System Global Area 100663296 bytes

Fixed Size 1217884 bytes

Variable Size 88083108 bytes

Database Buffers 8388608 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter_allow_resetlogs_uption

NAME TYPE VALUE

-----------------------------------------------------------------------------

_ Allow_resetlogs_uption boolean FALSE

SQL>

SQL> ALTER TABLESPACE TEMP01 ADDTEMPFILE '/DBBak2/oradata/WWL/temp01.dbf' REUSE;

Tablespace altered.

Check Data

SQL> conn wwl/wwl

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

-----------------------------------------------

WWL001 TABLE

WWL002 TABLE

Related reading:

Exploring the RMAN_01 concept of Oracle

Explore the basic use of RMAN_02 in Oracle

Explore Oracle-based RMAN_03 non-consistent backup

Explore Oracle-based RMAN_04 non-consistent backup

Explore RMAN_05 Incremental Backup in Oracle

Exploring Oracle's RMAN_06 backup policy

Explore Oracle RMAN_07 restoration of single data file loss

Explore Oracle RMAN_07 recovery of whole business tablespace loss

Explore Oracle RMAN_07 disk corruption data loss recovery

Explore Oracle RMAN_07 recovery of all database files lost

Explore Oracle RMAN_07 redo log redu file loss recovery

Explore Oracle RMAN_07 parameter file loss recovery

Explore Oracle RMAN_07 control file loss recovery

Explore Oracle RMAN_07 system tablespace loss recovery