Cold Backup Procedure:
———————————————-
Steps:
——
1.shut immediate
2.create required backup directory structure
3.copy CRD files to backup destination
4.start the database
[email protected]># su – oracle
[email protected]>$ cat /etc/oratab
[email protected]>$ export ORACLE_SID=hrms
[email protected]>$ 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;
[email protected]>$ cd /u02/app/oracle/hrms/
[email protected] hrms>$ cd
step:2
——
[email protected]>mkdir -p /u03/coldbkp
[email protected]>chown -R oracle:oinstall /u03/coldbkp
[email protected]>chmod -R 777 /u03/coldbkp
step:3
——
[email protected] 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;
[email protected]>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
——
[email protected]>$ 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;
[email protected] hrms>$cd /u02/app/oracle/hrms/
hrms>ls
[email protected] hrms>$cp *.* /u03/coldbkp/
hrms>$ ls
[email protected] hrms> cp control02.ctl /u03/coldbkp/
SQL>startup
—————————————————————————————-
Scenario:2 (Loss of Non-System Datafile)
—————————————————————————————-
SQL>Select name from v$datafile;
[email protected]>cd /u02/app/oracle/hrms
hrms>ls
[email protected] 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
——-
[email protected]>$ 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:
—————–
[email protected]> su – oracle
[email protected]>cd /u02/app/oracle/hrms/
hrms>ls
[email protected] hrms> rm system01.dbf
SQL>shut abort
[email protected]>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;
[email protected]>$ cd /u03/coldbkp/
[email protected] coldbkp>$ ls
[email protected] coldbkp>$ cp control01.ctl /u02/app/oracle/hrms/
[email protected] 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
[email protected] hrms>$ cp * /u03/coldbkp
[email protected] hrms>$cd /u01/app/oracle/fast_recovery_area/hrms
[email protected] hrms>$ cp control02.ctl /u03/coldbkp
——————————————————————————————
Scenario:5 (Loss of Redolog File)
——————————————————————————————
SQL>startup;
SQL>select member from v$logfile;
[email protected]>$ cd /u02/app/oracle/hrms/
[email protected] hrms>$ rm *.log
SQL>shut abort
[email protected]>$ cd /u03/coldbkp/
[email protected] coldbkp>$ cp *.dbf /u02/app/oracle/hrms/
SQL>startup mount;
SQL>recover database until cancel;
SQL>alter database open resetlogs;
SQL>shut immediate;
[email protected]>$ cd /u02/app/oracle/hrms/
[email protected] 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;
[email protected]>$ 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;
[email protected]>$ cd /u03/coldbkp/
[email protected] hrms>$ cp *.* /u02/app/oracle/hrms/
[email protected] 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;
