Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
RMAN is a tool also recommended by Oracle for backup and recovery.
There are many recovery scenarios. The main ones are:
- Recovering a database file
- Recovering a tablespace
- Recovering the database
1 – Recovering a database file:
This is the process that should be done when you receive an error similar to the following:
1 2 3 4 5 | ORA-01116: error in opening database file 66 ORA-01110: data file 66: ‘datafile path/datafilename’ ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
The following commands can be used to recover the related file:
1 2 3 4 5 6 | run { sql ‘alter database datafile 66 offline’; restore datafile 66; recover datafile 66; sql ‘alter database datafile 66 online’; } |
2- Recovering a Tablespace:
It is painful for users but very simple operation for DBAs.
Solution:
1 2 3 4 5 6 | run { sql ‘alter tablespace tablespace_name offline immediate’; restore tablespace tablespace_name; recover tablespace tablespace_name; sql ‘alter tablespace tablespace_name online’; } |
3- Recovering the database:
This is the most difficult situation that can happen to you. If the database does not open and many files have been deleted, you will need to return the database from the backup. If you try to open the database and there is a situation similar to the following:
1 2 3 4 5 6 7 8 | SQL> startup ORACLE instance started. Total System Global Area 2.3622E+10 bytes Fixed Size 2126480 bytes Variable Size 1.1341E+10 bytes Database Buffers 1.2264E+10 bytes Redo Buffers 14647296 bytes ORA-00205: error in identifying control file, check alert log for more info |
Don’t panic, everything’s under control.
Run the following command from a rman session.
1 | startup nomount; |
Later
1 | restore controlfile; |
Some different parameters may be required for the “Conrolfile” return operation. The location of the backup, the place to return, etc. You should see the output similar to the following when you return.
1 2 3 4 5 6 7 8 9 10 11 12 13 | Starting restore at 13-MAR-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=162 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring controlfile channel ORA_DISK_1: restored backup piece 1 piece handle=/path/o1_mf_s_617126064_2zfl9jy0_.bkp tag=TAG20070313T155424 channel ORA_DISK_1: restore complete output filename=/oradata/ueltest/control01.ctl output filename=/oradata/ueltest/control02.ctl output filename=/oradata/ueltest/control03.ctl Finished restore at 13-MAR-07 |
Then run the following command from a rman session.
1 2 3 | RMAN> alter database mount; database mounted released channel: ORA_DISK_1 |
Then run the following command.
1 | RMAN> host; |
Connect with SQL PLus to access the latest stable SCN information of the database with the following command:
1 | sqlplus / as sysdba |
1 | SQL> select archivelog_change#-1 from v$database; |
Close all rman sessions and then reconnect.
With the following command, let’s bring the database to the final stable SCN.
1 2 3 4 5 6 | run{ set until scn (you must enter the number returned from the previous query here); restore database; recover database; alter database open resetlogs; } |
These operations can take a long or short period depending on the read speed of the backup environment. When everything goes well, the database will be open and running.
Make sure you get a full backup immediately after each database recovery. Because the previous backups will be invalid.
