Please send your Questions & Answers or Feedback to "dvmadhavreddy@javabook.org"

Please send your Questions & Answers or Feedback to "mohan@javabook.org"

Scenario : Loss of Full Database

Steps to recovery :
1.shut abort
2.startup nomount
3.restore the CRD files
4.alter database mount
5.alter database recover automatic using backup controlfile until cancel;
6.recover cancel;
7.alter database open resetlogs;

**** SQL> create pfile from memory; *****
Execute the above command when we lost the pfile when the database is up and running.

SQL> archive log list;
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
Go to the CRD files location and remove all the files to crash the database and try to connect to scott and try to insert some records to check.
SQL> conn scott/tiger
scott> insert into salgrade select * from salgrade;
scott> /
scott> conn / as sysdba
SQL> shut immediate
The above command throws an error as shut immediate is a graceful command.
SQL> shut abort
SQL> startup nomount

oracle> cd /u03/coldbkp
oracle coldbkp> cp *.* /u03/app/oracle/venu

SQL> alter database mount;
SQL> select file#,checkpoint_change# from v$datafile;
save df.sql
SQL> select file#,checkpoint_change# from v$datafile_header;
save dfh.sql
SQL> alter database recover automatic using backup controlfile until cancel;
SQL> recover cancel;
SQL> @df.sql
SQL> @dfh.sql
SQL> select open_resetlogs from v$database;
To know the resetlogs are required for our database or not.
SQL> alter database open resetlogs;

Note : In case of full database crash recovery, after applying the archive logs, log sequence mismatch occurs. To overcome this problem we are going to open the database with resetlogs. Resetlogs is going to synchronise the log sequence number by creating brand new online redo logs starting with the log sequence one.

Checking after recovery :
SQL> startup
SQL> conn scott/tiger
scott> select count(*) from salgrade;
scott> conn / as sysdba
SQL> shut immediate
When the database is opened with resetlogs we need to take the cold backup again as the checkpoint_change numbers were changed.

Related Posts Plugin for WordPress, Blogger...

Flag Counter