Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the use of SNAPSHOT CONTROLFILE". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

According to my understanding:

Snapshot control file is only used when synchronizing catalog or when controlling file backups.

(a SNAPSHOT CONTROLFILE is backed up before backing up the control file)

The purpose of SNAPSHOT CONTROLFILE, as its name implies, should be to restore control files. Here are my test results:

The conclusion is:

1.SNAPSHOT CONTROLFILE cannot be directly copy into control file, if the database is already shutdown

two。 You can restore SNAPSHOT CONTROLFILE to a control file through rman

3. If the database does not have shutdown, it can be directly copy SNAPSHOT CONTROLFILE into a control file, but it still needs to be restored (because it is old)

Here are my test steps:

-

View the backup settings for snapshot controlfile:

RMAN > SHOW SNAPSHOT CONTROLFILE NAME

RMAN configuration parameters are:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/ u01qora9a Universe productUniverse 9.2 CONFIGURE SNAPSHOT CONTROLFILE NAME TO'/ u01According ora9a Universe; # default

RMAN >

The following simulation controls the recovery of lost files

$ls

Control01.ctl cwmlite01.dbf indx01.dbf redo02.log temp01.dbf users01.dbf

Control02.ctl drsys01.dbf odm01.dbf redo03.log tools01.dbf xdb01.dbf

Control03.ctl example01.dbf redo01.log system01.dbf undotbs01.dbf

$rm cont*-Delete the control file

$ls

Cwmlite01.dbf example01.dbf odm01.dbf redo02.log system01.dbf tools01.dbf users01.dbf

Drsys01.dbf indx01.dbf redo01.log redo03.log temp01.dbf undotbs01.dbf xdb01.dbf

$

After the control file is deleted, the database can still operate normally:

SQL > insert into test (no) values (4)

1 row created.

SQL > commit

Commit complete.

SQL > alter system switch logfile

System altered.

SQL > alter system checkpoint

System altered.

SQL > /

System altered.

SQL >

SQL > select no,to_char (vdate,'yyyy-mm-dd hh34:mi:ss') from test

NO TO_CHAR (VDATE,'YYYY-MM-DDHH24:MI:SS')

1 2009-02-24 10:16:09

2 2009-02-24 10:25:30

3 2009-02-24 10:26:56

4 2009-02-24 10:30:59

SQL >

Restart the database:

SQL > connect / as sysdba

Connected.

SQL > shutdown immediate

ORA-00210: cannot open the specified controlfile

ORA-00202: controlfile:'/ u01Universe ora9a control 01.ctl'

ORA-27041: unable to open file

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

There is no control file, shutdown immediate error

SQL >

SQL > shutdown abort

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 320569400 bytes

Fixed Size 741432 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 1060864 bytes

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

SQL >

There is no control file, the database cannot mount, now SNAPSHOT CONTROLFILE copy to the corresponding directory

$cp / u01/ora9a/product/9.2/dbs/snapcf_tt9a.f / u01/ora9a/oradata/tt9a/control01.ctl

$cp / u01/ora9a/oradata/tt9a/control01.ctl / u01/ora9a/oradata/tt9a/control0t.ctl

$cp / u01/ora9a/oradata/tt9a/control01.ctl / u01/ora9a/oradata/tt9a/control03.ctl

$ls-lrt / u01qora9a Universe oradataUniverse tt9a Universe *

-rw-r- 1 ora9a dba 1531904 Feb 24 10:36 / u01/ora9a/oradata/tt9a/control01.ctl

-rw-r- 1 ora9a dba 1531904 Feb 24 10:36 / u01/ora9a/oradata/tt9a/control0t.ctl

-rw-r- 1 ora9a dba 1531904 Feb 24 10:36 / u01/ora9a/oradata/tt9a/control03.ctl

$

SQL > startup

ORACLE instance started.

Total System Global Area 320569400 bytes

Fixed Size 741432 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 1060864 bytes

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

Still can't mount database.

-

Try to recover the control file with rman:

$rman target /

Recovery Manager: Release 9.2.0.1.0-64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

Connected to target database (not started)

RMAN > startup nomount

Oracle instance started

Total System Global Area 320569400 bytes

Fixed Size 741432 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 1060864 bytes

RMAN > restore controlfile from'/ u01qora9a Universe produce 9.2Compact dbsAccording to snapcfpromott9a.f'

Starting restore at 24-FEB-09

Using target database controlfile instead of recovery catalog

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: sid=13 devtype=DISK

Channel ORA_DISK_1: copied controlfile copy

Replicating controlfile

Input filename=/u01/ora9a/oradata/tt9a/control01.ctl

Output filename=/u01/ora9a/oradata/tt9a/control02.ctl

Output filename=/u01/ora9a/oradata/tt9a/control03.ctl

Finished restore at 24-FEB-09

RMAN > alter database mount

Database mounted

RMAN >

Ok, which can be recovered with rman to mount the data.

Restore the database

RMAN > recover database

Starting recover at 24-FEB-09

Using channel ORA_DISK_1

Starting media recovery

Archive log thread 1 sequence 1 is already on disk as file / u01/ora9a/oradata/tt9a/redo01.log

Archive log thread 1 sequence 2 is already on disk as file / u01/ora9a/oradata/tt9a/redo02.log

Archive log thread 1 sequence 3 is already on disk as file / u01/ora9a/oradata/tt9a/redo03.log

Archive log filename=/u01/ora9a/oradata/tt9a/redo01.log thread=1 sequence=0

Archive log filename=/u01/ora9a/oradata/tt9a/redo02.log thread=1 sequence=2

Archive log filename=/u01/ora9a/oradata/tt9a/redo03.log thread=1 sequence=3

Media recovery complete

Finished recover at 24-FEB-09

RMAN > alter database open

RMAN-00571: =

RMAN-00569: = ERROR MESSAGE STACK FOLLOWS =

RMAN-00571: =

RMAN-03002: failure of alter db command at 02/24/2009 10:47:36

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN > alter database open resetlogs

Database opened

RMAN >

View previous insert data:

SQL > select no,to_char (vdate,'yyyy-mm-dd hh34:mi:ss') from test

NO TO_CHAR (VDATE,'YYYY-MM-DDHH24:MI:SS')

1 2009-02-24 10:16:09

2 2009-02-24 10:25:30

3 2009-02-24 10:26:56

4 2009-02-24 10:30:59

SQL >

The data is still there, the recovery is successful!

= =

The following is still under normal operation of the database, delete the control file, and still test the recovery method of direct copy SNAPSHOT CONTROLFILE:

SQL > insert into test (no) values (5)

1 row created.

SQL > commit

Commit complete.

SQL >

SQL >!

$pwd

/ u01/ora9a/oradata/tt9a

$ls

Control01.ctl control0t.ctl example01.dbf redo01.log system01.dbf undotbs01.dbf

Control02.ctl cwmlite01.dbf indx01.dbf redo02.log temp01.dbf users01.dbf

Control03.ctl drsys01.dbf odm01.dbf redo03.log tools01.dbf xdb01.dbf

$rm contr*

$ls

Cwmlite01.dbf example01.dbf odm01.dbf redo02.log system01.dbf tools01.dbf users01.dbf

Drsys01.dbf indx01.dbf redo01.log redo03.log temp01.dbf undotbs01.dbf xdb01.dbf

$

$exit

SQL > select * from v$controlfile

STATUS

-

NAME

/ u01/ora9a/oradata/tt9a/control01.ctl

/ u01/ora9a/oradata/tt9a/control02.ctl

/ u01/ora9a/oradata/tt9a/control03.ctl

SQL > select dbid from v$database

Select dbid from v$database

*

ERROR at line 1:

ORA-00210: cannot open the specified controlfile

ORA-00202: controlfile:'/ u01Universe ora9a control 01.ctl'

ORA-27041: unable to open file

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

SQL >

No control file, query failed

Now change the SNAPSHOT CONTROLFILE copy to the corresponding directory and try to restore it directly to the control file:

SQL >!

$pwd

/ u01/ora9a/oradata/tt9a

$cp / u01/ora9a/product/9.2/dbs/snapcf_tt9a.f control01.ctl

$cp control01.ctl control02.ctl

$cp control01.ctl control03.ctl

$ls-lrt cont*

-rw-r- 1 ora9a dba 1531904 Feb 24 10:55 control01.ctl

-rw-r- 1 ora9a dba 1531904 Feb 24 10:55 control02.ctl

-rw-r- 1 ora9a dba 1531904 Feb 24 10:55 control03.ctl

$

Note that do not start and stop the database at this time, otherwise the control files recovered in this way will not be available

Try to query control file information

SQL > select dbid from v$database

DBID

-

3459515476

SQL >

Ok, successful

Query data file header information

SQL > select CHECKPOINT_CHANGE# from v$datafile_header

Select CHECKPOINT_CHANGE# from v$datafile_header

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [2170], []

SQL >

Fail

Now restart the database to see if you can reach the mount state:

SQL > shutdown immediate

ORA-00600: internal error code, arguments: [kccchb_1], [356], [353], [0], [353], [0], [], []

SQL > shutdown abort

ORACLE instance shut down.

SQL >

SQL > startup

ORACLE instance started.

Total System Global Area 320569400 bytes

Fixed Size 741432 bytes

Variable Size 285212672 bytes

Database Buffers 33554432 bytes

Redo Buffers 1060864 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL >

Ok, the database can be mount at this time

SQL >

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

Alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:'/ u01Universe ora9a Universe oradataUniplex tt9a Universe system01.dbf'

Prompt to restore

SQL > recover database using backup controlfile

ORA-00279: change 349227 generated at 02/24/2009 10:49:58 needed for thread 1

ORA-00289: suggestion: / u01/arch/1_1.dbf

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

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

AUTO

ORA-00328: archived log ends at change 347581, need later change 349227

ORA-00334: archived log:'/ U01Plus Archetype 1. DBF'

SQL > recover database using backup controlfile

ORA-00279: change 349227 generated at 02/24/2009 10:49:58 needed for thread 1

ORA-00289: suggestion: / u01/arch/1_1.dbf

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

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

/ u01/ora9a/oradata/tt9a/redo01.log

Log applied.

Media recovery complete.

SQL > alter database open resetlogs

Database altered.

SQL >

Recovery succeeded

SQL > select no,to_char (vdate,'yyyy-mm-dd hh34:mi:ss') from test order by vdate

NO TO_CHAR (VDATE,'YYYY-MM-DDHH24:MI:SS')

1 2009-02-24 10:16:09

2 2009-02-24 10:25:30

3 2009-02-24 10:26:56

4 2009-02-24 10:30:59

5 2009-02-24 10:51:01

SQL >

Ok, the newly inserted data is also here.

This is the end of the content of "what is the use of SNAPSHOT CONTROLFILE"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.