Cold Backup and Scenarios

Cold Backup Procedure:
———————————————-
Steps:
——
1.shut immediate
2.create required backup directory structure
3.copy CRD files to backup destination
4.start the database

root@node1># su – oracle
oracle@node1>$ cat /etc/oratab
oracle@node1>$ export ORACLE_SID=hrms
oracle@node1>$ sqlplus / as sysdba

SQL>startup

SQL>select name,open_mode,log_mode from v$database;

SQL>select instance_name,status from v$instance;

SQL>select name from v$datafile;

SQL>select name from v$controlfile;

SQL>select member from v$logfile;

step:1
——

SQL>shut immediate;

oracle@node1>$ cd /u02/app/oracle/hrms/
oracle@node1 hrms>$ cd

step:2
——

root@node1>mkdir -p /u03/coldbkp
root@node1>chown -R oracle:oinstall /u03/coldbkp
root@node1>chmod -R 777 /u03/coldbkp

step:3
——
oracle@node1 hrms>$ cp *.* /u03/coldbkp
hrms>$ cp control02.ctl /u03/coldbkp/

step:4
——
SQL>startup

SQL>archive log list;

SQL>alter user scott account unlock identified by tiger;

SQL>conn scott/tiger

SQL>insert into salgrade select * from salgrade;

SQL>/
/
/

SQL>commit;

SQL>archive log list;

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

SQL>alter system switch logfile;
—————————————————-
Scenario:1 (Loss of full Database)
—————————————————-
>Check the locations
SQL>select name from v$datafile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;

Oracle@Node1>cd /u02/app/oracle/hrms
hrms>ls
hrms>rm *
hrms>cd /u01/app/oracle/fast_recovery_area/hrms
hrms>ls
hrms>rm control02.ctl
SQL>select name from v$controlfile;
SQL>conn scott/tiger
error:ORA-27041 unable to open the file
SQL>conn /as sysdba
SQL>shut abort

step:1
——
oracle@node1>$ cd /u03/colbkp
coldbkp>$ls
coldbkp>cp *.* /u02/app/oracle/hrms
coldbkp>$ cp control02.ctl /u01/app/oracle/fast_recovery_area
step:2
——
SQL>startup mount

SQL>alter database recover automatic using backup controlfile until cancel;
error:ORA-27037 unable to obtain file status
Note:If it ask next archivelog file,when we give recover cancel upto that
it takes archivelogs.
SQL>recover cancel;

SQL>alter database open;

SQL>select open_resetlogs from v$database;

SQL>alter database open resetlogs;

—Again we take the backup for the new incarnation number.

SQL>shut immediate;

oracle@node1 hrms>$cd /u02/app/oracle/hrms/
hrms>ls
oracle@node1 hrms>$cp *.* /u03/coldbkp/
hrms>$ ls
oracle@node1 hrms> cp control02.ctl /u03/coldbkp/

SQL>startup

—————————————————————————————-
Scenario:2 (Loss of Non-System Datafile)
—————————————————————————————-
SQL>Select name from v$datafile;
oracle@node1>cd /u02/app/oracle/hrms
hrms>ls
oracle@node1 hrms>rm users01.dbf
—-for knowing if database is there or not
SQL>conn scott/tiger
SQL>insert into salgrade select * from salgrade;
ORA-01116 error in opening database file 4
ORA-01110 datafile 4 /u02/app/oracle/hrms/users01.dbf

SQL>conn /as sysdba
SQL>desc v$datafile;
SQL>select file#,error,status from v$datafile_header;

4 cannot open file ONLINE
steps:1
——-
SQL>alter database datafile 4 offline;
SQL>select * from v$recover_file
4 OFFLINE

step:2
——-
oracle@node1>$ cd /u03/coldbkp/
coldbkp>$ cp users01.dbf /u02/app/oracle/hrms/

Note:Before recovering the datafile,we check these commands.

SQL>select file#,checkpoint_change#, from v$datafile_header;
file# checkpoint_change#
—– ——————
1 782497
2 782497
3 782497
4 782497

SQL>save dfh.sql
SQL>select file#,checkpoint_change# from v$datafile;
(it gets information from controlfile)
SQL>save df.sql

step:3
——
SQL>recover datafile 4;
(media recovery complete)
SQL>alter database datafile 4 online;

SQL>@dfh.sql

SQL>@df.sql

(both script value should match)

——————————————————————————————–
Scenario:3 (Loss of system datafile)
——————————————————————————————–

SQL>select name,open_mode from v$database;
SQL>select name from v$datafile;

Another terminal:
—————–
root@node1> su – oracle
oracle@node1>cd /u02/app/oracle/hrms/
hrms>ls

oracle@node1 hrms> rm system01.dbf

SQL>shut abort

oracle@node1>cd /u03/coldbkp
coldbkp>cp system01.dbf /u02/app/oracle/hrms/

SQL>startup mount;

SQL>recover database;

SQL>alter database open;

——————————————————————————————–
Scenario:4 (Loss of Control Files)
——————————————————————————————–

SQL>select name from v$controlfile;

SQL>alter system switch logfile;

SQL>cd /u02/app/oracle/hrms/

hrms>$ ls

hrms>$ rm *.ctl

hrms>cd /u01/app/oracle/fast_recovery_area/hrms/

hrms>ls

hrms>rm control02.ctl

SQL>shut abort;

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 coldbkp>$ ls

oracle@node1 coldbkp>$ cp control01.ctl /u02/app/oracle/hrms/

oracle@node1 coldbkp>$ cp control02.ctl /u01/app/oracle/fast_recovery_area/hrms/

SQL>startup mount;

SQL>recover database using backup controlfile until cancel;

specify log:auto

ORA-10879:error signaled
ORA-01547 WARNING….
ORA-01194:FILE 1 needs more recovery…

SQL>recover cancel;

SQL>select member from v$logfile;

SQL>recover database using backup controlfile until cancel;

specify log:
/u02/app/oracle/hrms/redo03.log
(again it will throw error so again cancel the recovery)

SQL>recover cancel;

SQL>recover automatic using backup controlfile until cancel;

specify log:
/u02/app/oracle/hrms/redo02.log

log applied
Media recovery completed

SQL>ALTER DATABASE OPEN RESETLOGS;

SQL>Shut immediate

Now again we will take the coldbkp of database and controlfile

oracle@node1 hrms>$ cp * /u03/coldbkp

oracle@node1 hrms>$cd /u01/app/oracle/fast_recovery_area/hrms

oracle@node1 hrms>$ cp control02.ctl /u03/coldbkp

——————————————————————————————
Scenario:5 (Loss of Redolog File)
——————————————————————————————

SQL>startup;

SQL>select member from v$logfile;

oracle@node1>$ cd /u02/app/oracle/hrms/

oracle@node1 hrms>$ rm *.log

SQL>shut abort

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 coldbkp>$ cp *.dbf /u02/app/oracle/hrms/

SQL>startup mount;

SQL>recover database until cancel;

SQL>alter database open resetlogs;

SQL>shut immediate;

oracle@node1>$ cd /u02/app/oracle/hrms/

oracle@node1 hrms>$ cp *.* /u03/coldbkp/

SQL>startup;

—————————————————–
Scenario:6 (Loss of Datafile which was not in backup)
—————————————————–

SQL>select name from v$datafile;

SQL>create tablespace ssss
datafile ‘/u02/app/oracle/hrms/ssss01.dbf’ size 5m;

SQL>create user u1 identified by u1 default tablespace ssss;

SQL>grant connect,resource to u1;

SQL>conn u1/u1;

SQL>create table a (a number);

SQL>insert into a values(1);

SQL>insert into a select * from a;

SQL>/
/
/
/
/
/

SQL>commit;

SQL>select count(*) from a;

SQL>conn /as sysdba;

SQL>select name from v$datafile;

oracle@node1>$ cd /u02/app/oracle/hrms/

hrms>ls

hrms>rm ssss01.dbf

SQL>select name,file# from v$datafile;

SQL>alter database datafile 5 resize 6m;

ORA-01565 error in identifying file….

SQL>alter database datafile 5 offline;

SQL>alter database create datafile
‘/u02/app/oracle/hrms/ssss01.dbf’;

SQL>recover datafile 5;

SQL>alter database datafile 5 online;

SQL>conn u1/u1

SQL>select count(*) from a;

——————————————————————–
Scenario:7 (Performing point in time recovery)
——————————————————————–

SQL>select sysdate from dual;

SQL>set time on;

SQL>select username from all_users where username=’SCOTT’;

SQL>drop user scott cascade;

SQL>shut immediate;

oracle@node1>$ cd /u03/coldbkp/

oracle@node1 hrms>$ cp *.* /u02/app/oracle/hrms/

oracle@node1 hrms>$cp control02.ctl /u02/app/oracle/fast_recovery_area/hrms/

SQL>startup mount;

SQL>alter database recover automatic using backup controlfile until time
’25-APR-2009 22:57:23′;

SQL>alter database open;

SQL>alter database open read only;

SQL>recover cancel;

SQL>alter database open read only;

SQL>conn scott/tiger

SQL>conn /as sysdba

SQL>shut immediate;

SQL>startup mount;

SQL>alter database open resetlogs;

Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: