Database Backup & Recovery


Backup & Recovery-User Managed Recovery Scenarios

Complete Open Database Recovery. Non system tablespace is missing

  • If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open. Pre requisites: A closed or open database backup and archived logs.

[oracle@prmyrclone]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 22 17:49:34 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area       740724736 bytes
Fixed Size                                   1339092 bytes
Variable Size                              440402220 bytes
Database Buffers                      293601280 bytes
Redo Buffers                             5382144 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/rclone/users01.dbf’

  • Use OS commands to restore the missing or corrupted datafile to its original location,

[oracle@prmy ~]$ cp /u01/app/backup/users01.dbf /u01/app/oracle/oradata/rclone/

  • Login to sql and recover the tablespace

SQL> recover tablespace users;
ORA-00279: change 1160429 generated at 07/16/2015 11:09:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/RCLONE/archivelog/2015_07_16/o1_mf_1_8_btgoz
jvd_.arc
ORA-00280: change 1160429 for thread 1 is in sequence #8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.
Media recovery complete.

SQL> alter database open;
Database altered

Complete Closed Database Recovery. System tablespace is missing

  • If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed. Pre requisites: A closed or open database backup and archived logs

1

  • Use OS commands to restore the missing or corrupted system datafile to its original location,

2

  • startup mount;
  • recover datafile 1
  • alter database open

3

Complete Open Database Recovery (when the database is initially closed). Non system tablespace is missing

  •  If a non-system tablespace is missing or corrupted and the database crashed, recovery can be performed after the database is open. Pre requisites: A closed or open database backup and archived logs. When startup; (you will get ora-1157 ora-1110 and the name of the missing data file, the database will remain mounted)

[root@prmy ~]# su – oracle
[oracle@prmy ~]$ export ORACLE_SID=rclone
[oracle@prmy ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 23 10:22:09 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.

SQL>startup
ORACLE instance started.

Total System Global Area     740724736 bytes

Fixed Size                                 1339092 bytes
Variable Size                            440402220 bytes
Database Buffers                    293601280 bytes
Redo Buffers                           5382144 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/rclone/users01.dbf’

  • Use OS commands to restore the missing or corrupted data file to its original location,

[oracle@prmyrclone]$ cp /u01/app/backup/users01.dbf /u01/app/oracle/oradata/rclone/

SQL> alter database datafile 4 offline;
Database altered.

SQL> alter database open;
Database altered.

SQL> recover datafile 4;
ORA-00279: change 1160429 generated at 07/16/2015 11:09:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/RCLONE/archivelog/2015_07_16/o1_mf_1_8_btgoz
jvd_.arc
ORA-00280: change 1160429 for thread 1 is in sequence #8
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

AUTO
Log applied.
Media recovery complete.

SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.

Recovery of a Missing Datafile that has no backups (database is open).

  • If a non-system datafile that was not backed up since the last backup is missing, recovery can be performed if all archived logs since the creation of the missing datafile exist. Pre requisites: All relevant archived logs.

SQL> alter tablespace prod online;
Tablespace altered.

SQL> alter tablespace prod offline;
Tablespace altered.

SQL> alter database create datafile ‘/u01/app/oracle/oradata/prod01.dbf’;
Database altered.

SQL> recover tablespace prod;
Media recovery complete.

SQL> alter tablespace prod online;
Tablespace altered.

Restore and Recovery of a Datafile to a different location.

  • If a non-system datafile is missing and its original location not available, restore can be made to a different location and recovery performed. Pre requisites: All relevant archived logs.
  • Use OS commands to restore the missing or corrupted datafile to the new location

[oracle@prmyoradata]$ cp /u01/backup/orcl/prod01.dbf /u01/app/oracle/oradata/prod/
SQL> alter tablespace prod offline;
Tablespace altered.

SQL> alter tablespace prod rename datafile
‘/u01/app/oracle/oradata/prod01.dbf’  to
‘/u01/app/oracle/oradata/prod/prod01.dbf’;
Tablespace altered.

SQL> recover tablespace prod;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter tablespace prod online;
Tablespace altered.

Control File Recovery

  •  Always multiplex your controlfiles. Controlfiles are missing, database crash. Pre requisites: A backup of your controlfile and all relevant archived logs.

startup; (you get ora-205, missing controlfile, instance start but database is not mounted)

SQL>startup
ORACLE instance started.

Total System Global Area          740724736 bytes
Fixed Size                                      1339092 bytes
Variable Size                                440402220 bytes
Database Buffers                         293601280 bytes
Redo Buffers                                5382144 bytes

ORA-00205: error in identifying control file, check alert log for more info

  •  Use OS commands to restore the missing controlfile to its original location:

[oracle@prmyorcl]$ cp /u01/backup/orcl/control01.ctl /u01/app/oracle/oradata/prod/
[oracle@prmyorcl]$ cp /u01/backup/orcl/control02.ctl /u01/app/oracle
/flash_recovery_area/prod/

SQL> alter database mount;
Database altered.

SQL> recover automatic database using backup controlfile;

ORA-00279: change 805822 generated at 07/25/2015 14:17:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_07_25/o1_mf_1_4_%u_.arc

ORA-00280: change 805822 for thread 1 is in sequence #4
ORA-00278: log file

‘/u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_07_25/o1_mf_1_4_%u_.ar
c’ no longer needed for this recovery
ORA-00308: cannot open archived log
‘/u01/app/oracle/flash_recovery_area/PROD/archivelog/2015_07_25/o1_mf_1_4_%u_.arc’
ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prod/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;
Database altered.

 

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *