Can I roll back or restore a database from a cold backup?

Source: Internet
Author: User

For the cold backup to restore the database can be rolled back to restore the problem, itpub forum on the relevant links in the http://www.itpub.net/showthread.php? S = & threadid = 220971.
 
Experiments show that cold backup and database restoration can be performed before rollback and recovery:
The experiment process is as follows:

SQL> connect internal/Oracle
Connected.
SQL> archive log list
Database Log mode archive Mode
Automatic Archival Enabled
Archive destination/opt/Oracle/ARCH/orcl
Oldest online log sequence 747
Next log sequence to archive 749
Current Log sequence 749

SQL> select * from V $ log;

Group # thread # sequence # bytes members arc status
---------------------------------------------------------------------
First_change # first_tim
----------------------
1 1 748 51200000 1 Yes inactive
5209950 21-sep-04

2 1 749 51200000 1 no current
5222702 23-sep-04

3 1 747 51200000 1 Yes inactive
5205433 21-sep-04

SQL> select * from V $ logfile;

Group # status
-----------------
Member
--------------------------------------------------------------------------------
1
/Opt/Oracle/DB02/oradata/orcl/redo01.log

2
/Opt/Oracle/db03/oradata/orcl/redo02.log

3
/Opt/Oracle/DB04/oradata/orcl/redo03.log

SQL> connect test/test
Connected.
SQL> select * From user_tables;

No rows selected

SQL> Create Table T (A varchar2 (10 ));

Table created.

SQL> alter system switch logfile; (switch logs to generate an archive log file)

System altered.

SQL> insert into T values ('1 ');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile; (switch logs to generate an archive log file)

System altered.
SQL> insert into T values ('2 ');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile; (switch logs to generate an archive log file)

System altered.

SQL> insert into T values ('3 ');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile; (switch logs to generate an archive log file)

System altered.

SQL> host
$ CD/opt/Oracle/ARCH/orcl

$ Ls (four archived logs are generated in total)
Arch_316749.arc arch_0000750.arc arch_0000751.arc arch_0000752.arc
SQL> exit
 
SQL> select * from T;

A
----------
1
2
3

SQL> insert into T values ('4'); (insert record 4, but do not switch logs. This record is in redo03.log)

1 row created.

SQL> commit;

Commit complete.

Close the database:
SQL> connect internal/Oracle
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.

SQL> host
$ CP/opt/Oracle/DB04/oradata/orcl/redo03.log/opt/Oracle/(back up the current log redo03.log to the/opt/Oracle directory separately)
$ RM/opt/Oracle/DB02/oradata/orcl /*
$ RM/opt/Oracle/db03/oradata/orcl /*
$ RM/opt/Oracle/DB04/oradata/orcl /*

In this case, all data files (including online log files and control files) during CP cold backup are returned.

SQL> startup Mount
Oracle instance started.

Total system global area 1134141116 bytes
Fixed size 102076 bytes
Variable Size 311750656 bytes
Database buffers 819200000 bytes
Redo buffers 3088384 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel; (Note that the using backup controlfile until cancel command must be used here)
ORA-00279: Change 5224492 generated at 11:00:18 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_316749.arc
ORA-00280: Change 5224492 for thread 1 is in sequence #749

Specify log: {<RET> = suggested | filename | auto | cancel}
Auto
ORA-00279: Change 5224671 generated at 10:27:50 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_0000750.arc
ORA-00280: Change 5224671 for thread 1 is in sequence #750
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_316749.arc' no longer needed
This recoveryORA-00279: Change 5224683 generated at 10:31:23 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_rj753.arc
ORA-00280: Change 5224683 for thread 1 is in sequence #753

Specify log: {<RET> = suggested | filename | auto | cancel}
/Opt/Oracle/redo03.log (the/opt/Oracle/redo03.log file backed up is specified here)
Log applied.
Media recovery complete.

SQL> conn test/test
Connected.
SQL> select * from T;

ORA-00279: Change 5224675 generated at 10:29:00 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_0000751.arc
ORA-00280: Change 5224675 for thread 1 is in sequence #751
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000750.arc' no longer needed
This recovery

ORA-00279: Change 5224679 generated at 10:30:44 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_rj752.arc
ORA-00280: Change 5224679 for thread 1 is in sequence #752
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000751.arc' no longer needed
This recovery

ORA-00279: Change 5224683 generated at 10:31:23 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_rj753.arc
ORA-00280: Change 5224683 for thread 1 is in sequence #753
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000752.arc' no longer needed
This recovery

ORA-00308: cannot open archived log '/opt/Oracle/ARCH/orcl/arch_0000753.arc'
ORA-27037: unable to obtain File status
Svr4 error: 2: no such file or directory
Additional information: 3

SQL> alter database open resetlogs; (Open resetlogs is used to open the database)

Database altered.

SQL> connect test/test
Connected.
SQL> select * from T;

A
----------
1
2
3

The results showed that the database was not completely restored, only three records were recovered, and 4th records were lost because redo03.log was overwritten after cold backup, this record is not found in the redo03.log during cold backup. The last record 4 is actually recorded in the redo03.log before the cold backup fails. Therefore, only three records can be recovered.
If the redo03.log backed up in the/opt/Oracle directory is applied, the database can be completely restored.

At the time of restoration, specify the redo03.log of the backup for the last restoration:
SQL> recover database using backup controlfile until cancel;
ORA-00279: Change 5224492 generated at 11:00:18 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_316749.arc
ORA-00280: Change 5224492 for thread 1 is in sequence #749

SQL> recover database using backup controlfile until cancel;

Specify log: {<RET> = suggested | filename | auto | cancel}
Auto
ORA-00279: Change 5224671 generated at 10:27:50 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_0000750.arc
ORA-00280: Change 5224671 for thread 1 is in sequence #750
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_316749.arc' no longer needed
This recovery

ORA-00279: Change 5224675 generated at 10:29:00 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_0000751.arc
ORA-00280: Change 5224675 for thread 1 is in sequence #751
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000750.arc' no longer needed
This recovery

ORA-00279: Change 5224679 generated at 10:30:44 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_rj752.arc
ORA-00280: Change 5224679 for thread 1 is in sequence #752
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000751.arc' no longer needed
This recovery

ORA-00279: Change 5224683 generated at 10:31:23 needed for thread 1
ORA-00289: Suggestion:/opt/Oracle/ARCH/orcl/arch_rj753.arc
ORA-00280: Change 5224683 for thread 1 is in sequence #753
ORA-00278: Log File '/opt/Oracle/ARCH/orcl/arch_0000752.arc' no longer needed
This recovery

ORA-00308: cannot open archived log '/opt/Oracle/ARCH/orcl/arch_0000753.arc'
ORA-27037: unable to obtain File status
Svr4 error: 2: no such file or directory
Additional information: 3

A
----------
1
2
3
4

The data is completely recovered.

Conclusion: When restoring the database by rolling back all files in cold backup, you must specify the recover database using backup controlfile util cancel until all the archives are archived, apply Online logs manually after the application is completed. Otherwise, an error indicating that the control file is old and does not need to be restored will be reported. Note that the last log must be restored using online logs.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.