One database recovery experiment

A friend asked me a question,
Ask you a restoration question about Oracle database:
Suppose a full cold copy is made to database a at noon (instead of using tools or commands for backup, but directly copying files). By, the database went down, if the redo file is not damaged on another hard disk, how can I recover the database by using the cold copy at and the undamaged redo file?
Thank you.

I just asked instructor Yao,
There are three recovery methods:
1. Use your cold copy at for restoration, but only restore.
2. If you want to restore to, you need to take out the cold copy (excluding three redo records)
And add the redo that you haven't damaged at to this set.
Alter database Mount;
Recover database until cancel;
Specify a redo with the online log file of. (try all three and use an online redo file ),
It should be restored successfully.
3. If the logs are archived at that time,
You can also extract the file from the archive log for restoration,
Finally, Open Database resetlogs,
Data will not be lost.

The experiment process is as follows:

Initial status:
[Oracle @ vlg app] $ sqlplus/As sysdba

SQL * Plus: Release 10.2.0.4.0-production on Thu Feb 5 11:04:54 2009

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

Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options

SQL> select count (*) from tabs;

Count (*)
----------
708

SQL> select * from CC;

Name age
--------------------
Cx 26
CJ 29
CST 25

SQL & gt; Set line 200
SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim
-------------------------------------------------------------------------------------------
1 1 1 52428800 1 Yes inactive 2675377 05-feb-09
2 1 2 52428800 1 no current 2675378 05-feb-09
3 1 0 52428800 1 Yes unused 0

Make a cold backup of the current database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
(Close the database)

 

(Cold backup)

[Oracle @ vlg OEMs] $ CD ..
[Oracle @ vlg app] $ pwd
/APP
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs
[Oracle @ vlg app] $ mkdir oemsoldbakup
[Oracle @ vlg app] $ CP-RF OEMs oemsoldbakup /&
[1] 12439

[Oracle @ vlg app] $ PS-Ef | grep CP
Root 11 7 0 feb04? 00:00:00 [kacpid]
Root 2632 1 0 feb04? 00:00:00/usr/sbin/acpid
68 2835 2829 0 feb04? 00:00:00 Hald-addon-ACPI: listening on acpid socket/var/run/acpid. Socket
Root 3172 3165 0 feb04 tty7 00:00:01/usr/bin/Xorg: 0-Br-Audit 0-auth/var/TPD/: 0. Xauth-nolisten TCP vt7
Oracle 12714 8935 0 00:00:00 pts/3 grep CP
[1] + done CP-RF OEMs oemsoldbakup/

Open the database, perform some insert operations, commit, and checkpoint

[Oracle @ vlg app] $ sqlplus/As sysdba

SQL * Plus: Release 10.2.0.4.0-production on Thu Feb 5 11:15:35 2009

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

Connected to an idle instance.

SQL> startup
Oracle instance started.

Total system global area 360710144 bytes
Fixed size 1267500 bytes
Variable Size 130025684 bytes
Database buffers 226492416 bytes
Redo buffers 2924544 bytes
Database mounted.
Database opened.

SQL> insert into CC values ('xf', '24 ');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL & gt; Set line 200
SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim
-------------------------------------------------------------------------------------------
1 1 1 52428800 1 Yes inactive 2675377 05-feb-09
2 1 2 52428800 1 no current 2675378 05-feb-09
3 1 0 52428800 1 Yes unused 0

Close the database, back up the current database to oemsckpt
SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

[Oracle @ vlg app] $ CP-RF OEMs oemsckpt &
[1] 13894
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemsoldbakup
[Oracle @ vlg app] $ PS-Ef | grep CP
Root 11 7 0 feb04? 00:00:00 [kacpid]
Root 2632 1 0 feb04? 00:00:00/usr/sbin/acpid
68 2835 2829 0 feb04? 00:00:00 Hald-addon-ACPI: listening on acpid socket/var/run/acpid. Socket
Root 3172 3165 0 feb04 tty7 00:00:01/usr/bin/Xorg: 0-Br-Audit 0-auth/var/TPD/: 0. Xauth-nolisten TCP vt7
Oracle 14073 2890 0 00:00:00 pts/2 grep CP
[1] + done CP-RF OEMs oemsckpt

Open the database, perform the switchlogfile operation, disable the database, and back up the database file to the oemslogswitch.
SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status first_change # first_tim
-------------------------------------------------------------------------------------------
1 1 1 52428800 1 Yes inactive 2675377 05-feb-09
2 1 2 52428800 1 Yes active 2675378 05-feb-09
3 1 3 52428800 1 no current 2680540 05-feb-09
(The current log has been archived .)

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
(Close the database)
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemsoldbakup
[Oracle @ vlg app] $ CP./OEMs/redo0 *. log./oemslogswitch/
[Oracle @ vlg app] $ CD oemslogswitch/
[Oracle @ vlg oemslogswitch] $ ls
Redo01.log redo02.log redo03.log
[Oracle @ vlg oemslogswitch] $ CD ..
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemslogswitch oemsoldbakup
[Oracle @ vlg app] $ CD OEMs
[Oracle @ vlg OEMs] $ ls
Control01.ctl control03.ctl Mgmt. DBF redo01.log redo03.log system01.dbf undotbs01.dbf
Control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[Oracle @ vlg OEMs] $ CD ..
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemslogswitch oemsoldbakup
(Cold backup of the current database)
Rename the current database to simulate a fault
[Oracle @ vlg app] $ MV OEMs oemsguzhang
Copy the old library file and add the redo file of the ckpt library.
[Oracle @ vlg app] $ mkdir OEMs
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemsguzhang oemslogswitch oemsoldbakup
[Oracle @ vlg app] $ CD oemsoldbakup/
[Oracle @ vlg oemsoldbakup] $ ls
OEMs
[Oracle @ vlg oemsoldbakup] $ CD OEMs/
[Oracle @ vlg OEMs] $ ls
Control01.ctl control03.ctl Mgmt. DBF redo01.log redo03.log system01.dbf undotbs01.dbf
Control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[Oracle @ vlg OEMs] $ MV *. */APP/OEMs/
[Oracle @ vlg OEMs] $ ls
[Oracle @ vlg OEMs] $ CD ..
[Oracle @ vlg oemsoldbakup] $ ls
OEMs
[Oracle @ vlg oemsoldbakup] $ CD ..
[Oracle @ vlg app] $ ls
Flash_recovery_area lost + found OEMs oemsckpt oemsguzhang oemslogswitch oemsoldbakup
[Oracle @ vlg app] $ CD OEMs
[Oracle @ vlg OEMs] $ ls
Control01.ctl control03.ctl Mgmt. DBF redo01.log redo03.log system01.dbf undotbs01.dbf
Control02.ctl example01.dbf mgmt_ecm_depot1.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[Oracle @ vlg OEMs] $ MV redo *. Log ../oemsoldbakup/OEMs/
[Oracle @ vlg OEMs] $ ls
Control01.ctl control03.ctl Mgmt. DBF sysaux01.dbf temp01.dbf users01.dbf
Control02.ctl example01.dbf mgmt_ecm_depot1.dbf system01.dbf undotbs01.dbf
[Oracle @ vlg OEMs] $ CP ../oemsckpt/Redo *. log ./
[Oracle @ vlg OEMs] $ sqlplus/As sysdba

SQL * Plus: Release 10.2.0.4.0-production on Thu Feb 5 11:49:08 2009

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

Connected to an idle instance.

SQL> startup Mount;
Oracle instance started.

Total system global area 360710144 bytes
Fixed size 1267500 bytes
Variable Size 130025684 bytes
Database buffers 226492416 bytes
Redo buffers 2924544 bytes
Database mounted.
SQL> alter database open;
Alter database open
*
Error at line 1:
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: Online log 2 thread 1: '/APP/OEMs/redo02.log'

SQL> recover database using backup controlfile;
ORA-00279: Change 2677299 generated at 02/05/2009 11:07:47 needed for thread 1
ORA-00289: Suggestion:
/APP/flash_recovery_area/OEMs/archivelog/2009_02_05/o1_mf_1_2 _ % u _. Arc
ORA-00280: Change 2677299 for thread 1 is in sequence #2

Specify log: {<RET> = suggested | filename | auto | cancel}
/APP/OEMs/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

SQL> select * from CC;

Name age
--------------------
Cx 26
CJ 29
CST 25
XF 24

In this case, the database can be restored (using ckpt redo)

Case 3:

If you perform the logswitch operation, you only need to find the generated archive file,

You can specify this archive in recover database using backup controlfile.

However, we also need to recover the database using backup controlfile until cancel,

Open Database resetlogs to restore the file.