Restore database by using resetlog recovery Control File

Environment: rhel5.5, Oracle10.2.0.1xmanager4.0, after backing up the control file, perform a series of operations on the database to close the database; restore the control file, start

Environment: rhel 5.5, Oracle10.2.0.1 xmanager4.0 after backing up the control file, you can perform a series of operations on the database to close the database. Restore the control file and start

Environment:

Rhel 5.5, Oracle10.2.0.1

Xmanager4.0

After backing up the control file, you can perform a series of operations on the database to close the database. Restore the control file, start to mount, and re-create the control file using resetlogs to retrieve our data.

Backup Control File

Backupdatafile 1;

RMAN> backup datafile 1;

RMAN> backup datafile 1;

Startingbackup at 26-JUN-12

Usingchannel ORA_DISK_1

ChannelORA_DISK_1: starting compressed full datafile backupset

ChannelORA_DISK_1: specifying datafile (s) in backupset

Inputdatafile fno = 00001 name =/opt/oracle/product/10.2.0/oradata/cuug/system01.dbf

ChannelORA_DISK_1: starting piece 1 at 26-JUN-12

ChannelORA_DISK_1: finished piece 1 at 26-JUN-12

Piecehandle =/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/backupset/2012_06_26/partition _. bkptag = tag20120626t111_1 comment = NONE

ChannelORA_DISK_1: backup set complete, elapsed time: 00:08:06

ChannelORA_DISK_1: throttle time: 0:06:39

Finishedbackup at 26-JUN-12

StartingControl File Autobackup at 26-JUN-12

Piecehandle =/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/autobackup/2012_06_26/o1_mf_n_786973989_7yldk 6mm _. bkpcomment = NONE

FinishedControl File Autobackup at 26-JUN-12

Perform some operations and log Switching

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

GROUP # SEQUENCE # STATUS

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

1 9 INACTIVE

2 10 CURRENT

3 7 INACTIVE

4 8 INACTIVE

[Oracle @ rhel5cuug] $ cp cuug01.dbf cuug01.dbf _ bak

SQL> select username, default_tablespace from dba_users where username = 'Scott ';

USERNAME DEFAULT_TABLESPACE

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

SCOTT CUUG

SQL> create table scott. a as select * from tab;

Tablecreated.

SQL> create table scott. aa as select * fromtab;

SQL> select count (*) from scott.;

COUNT (*)

----------

3642

SQL> create tablespace test datafile '/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf' size 100 m;

Tablespacecreated.

Tablespacecreated.

SQL> alter system switch logfile;

Systemaltered.

SQL> select file_name from dba_data_files;

FILE_NAME

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

/Opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/users01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/system01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/test01.dbf

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

GROUP # status sequence #

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

1 INACTIVE 9

2 CURRENT 10

3 INACTIVE 7

4 INACTIVE 8

SQL> shutdown abort

ORACLEinstance shut down.

Delete the control file and modify the data file of cuug.

[Oracle @ rhel5cuug] $ mkdir bak

[Oracle @ rhel5cuug] $ mv *. ctl bak/

[Oracle @ rhel5cuug] $ mv cuug01.dbf cuug01.bak

SQL> startup

ORACLEinstance started.

TotalSystem Global Area 218103808 bytes

FixedSize 1218604 bytes

VariableSize 62916564 bytes

DatabaseBuffers 150994944 bytes

RedoBuffers 2973696 bytes

ORA-00205: error in identifying control file, check alert log for more info

Restore Control File

[Oracle@rhel510.2.0] $ rman target/

RecoveryManager: Release 10.2.0.1.0-Production on Mon Jun 25 10:26:22 2012

Copyright (c) 1982,200 5, Oracle. All rightsreserved.

Connectedto target database: orcl (not mounted)

RMAN> restore controlfile from '/opt/oracle/product/10.2.0/flash_recovery_area/CUUG/autobackup/2012_06_26/o1_mf_n_786973989_7yldk 6mm _. bkp ';

Startingrestore at 26-JUN-12

Usingchannel ORA_DISK_1

ChannelORA_DISK_1: restoring control file

ChannelORA_DISK_1: restore complete, elapsed time: 00:00:03

Outputfilename =/opt/oracle/product/10.2.0/oradata/cuug/control01.ctl

Outputfilename =/opt/oracle/product/10.2.0/oradata/cuug/control02.ctl

Outputfilename =/opt/oracle/product/10.2.0/oradata/cuug/control03.ctl

Finishedrestore at 26-JUN-12

Control File

Mark the trace file

SQL> alter session set tracefile_identifier = 'cuug ';

Sessionaltered.

SQL> alter database mount;

Databasealtered.

SQL> alter database backup controlfile to trace;

Databasealtered.

Re-create the control file, shut down the database, delete the control file that was previously recovered from the backup, and start the nomount phase.

SQL> shutdown immediate

ORA-01109: database not open

Databasedismounted.

ORACLEinstance shut down.

SQL> startup nomount;

ORACLEinstance started.

TotalSystem Global Area 218103808 bytes

FixedSize 1218604 bytes

VariableSize 79693780 bytes

DatabaseBuffers 134217728 bytes

RedoBuffers 2973696 bytes

Use noresetlogs to create a logstore. Because online logs are still running, you can use the noresetlogs method to create a logstore.

[Oracle @ rhel5orcl] $ ls *. ctl

Control01.ctl control02.ctl control03.ctl

[Oracle @ rhel5orcl] $ rm *. ctl

[Oracle @ rhel5udump] $ vi cuug_ora_4744_cuug.trc

Createcontrolfile reuse database "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES16

MAXLOGMEMBERS3

Maxdatafile100

MAXINSTANCES8

MAXLOGHISTORY292

LOGFILE

GROUP 1 '/opt/oracle/product/10.2.0/oradata/orcl/redo01.log' SIZE 50 M,

GROUP 2 '/opt/oracle/product/10.2.0/oradata/orcl/redo02.log' SIZE 50 M,

GROUP 3 '/opt/oracle/product/10.2.0/oradata/orcl/redo03.log' SIZE 50 M,

GROUP 4'/opt/oracle/product/10.2.0/oradata/orcl/redo04.log 'SIZE 50 M

-- STANDBY LOGFILE

DATAFILE

'/Opt/oracle/product/10.2.0/oradata/orcl/system01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/users01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/rmans01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/cuug01.dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/ORCL/datafile/o1_mf_zxm_7ycm0twl _. dbf ',

'/Opt/oracle/product/10.2.0/oradata/orcl/ORCL/datafile/o1_mf_cuug_7ycm20jq _. dbf'

CHARACTERSET UTF8

;

An error is prompted when you open the database.

First, restore the data file.

Query data files

SQL> select name from v $ datafile;

NAME

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

/Opt/oracle/product/10.2.0/oradata/cuug/system01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/users01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf

/Opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00007

SQL> alter database rename file '/opt/oracle/product/10.2.0/db_1/dbs/UNNAMED00007' to '/opt/oracle/product/10.2.0/oradata/cuug/test01.dbf ';

SQL> select name from v $ datafile;

NAME

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

/Opt/oracle/product/10.2.0/oradata/cuug/system01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/users01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf

/Opt/oracle/product/10.2.0/oradata/cuug/test01.dbf

Currently, the data files are inconsistent, and media recovery is required, but the using clause is not required.

SQL> recover database using backup controlfile;

ORA-00279: change 708399 generated at 06/26/2012 12:11:13 needed for thread 1

ORA-00289: suggestion:

/Opt/oracle/product/10.2.0/flash_recovery_area/CUUG/archivelog/2012_06_26/o1_mf _

Limit 10 _ % u _. arc

ORA-00280: change 708399 for thread 1 is in sequence #10

Specifylog :{ = Suggested | filename | AUTO | CANCEL}

/Opt/oracle/product/10.2.0/oradata/cuug/redo02.log

Logapplied.

Mediarecovery complete

SQL> alter database open resetlogs;

Databasealtered.

Query recovery status

Selectfile_name, tablespace_name, bytes/1024/1024 MB fromdba_data_files

FILE_NAME TABLESPACE_NAME MB

Certificate ----------------------------------------------------------------------------------------------------

/Opt/oracle/product/10.2.0/oradata/cuug/cuug01.dbf cuug200

/Opt/oracle/product/10.2.0/oradata/cuug/rmans01.dbf RMANS 500

/Opt/oracle/product/10.2.0/oradata/cuug/users01.dbf USERS 5

/Opt/oracle/product/10.2.0/oradata/cuug/sysaux01.dbf SYSAUX 250

/Opt/oracle/product/10.2.0/oradata/cuug/undotbs01.dbf UNDOTBS1 25

/Opt/oracle/product/10.2.0/oradata/cuug/system01.dbf SYSTEM 480

/Opt/oracle/product/10.2.0/oradata/cuug/test01.dbf TEST 100

SQL> select count (*) from scott.

2;

COUNT (*)

----------

3642

SQL> select count (*) from scott. aa;

COUNT (*)

----------

3642

Recovery completed.