1. How to Schedule backups using enterprise manager?
2. How to relocate archive-log backups to different disk location?
3. How to recover whole database without backup if all Control Files are lost?
4. How to restore and recover from loss of datafiles and tablespaces?

1. Recovering the Whole Database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

RMAN> STARTUP MOUNT
RMAN> RESTORE DATABASE;     
RMAN> RECOVER DATABASE;
 
#Examine the output to see if recovery was successful. 
 RMAN> SQL ‘ALTER DATABASE OPEN’;
 
#--[Option-1]-- if DB contail Read-Only Tablespace:
# The read-only tablespace does not need media recovery only restore from 
# backup is sufficient By default, the restore operation will skip 
# read-only tablespaces.
 
#To Force RMAN to restore any missing datafiles belonging to read-only 
# tablespaces. Replace step-2 with following command
RMAN> RESTORE DATABASE CHECK READONLY;
 
#--[Option-2:]-- Delete archivelog restored during media recovery. Replace 
# step-2 with following command:
RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

2. Recovering from Loss of All Control Files without backup

You can recover the database in case if have last all control files.

Data used by Recovery Manager will be lost If you were using the control file as the RMAN repository.

Assuming you are doing this on Windows, the files will be locked. This is the directory snap where your datafile, controlfile and redolog files are residing.

The control files will be renamed and then a startup will be attempted.

1
2
3
4
5
6
7
8
9
10
11

-- Clean shutdown to release the locks on the files
SQL> conn sys/oracle AS sysdba
SQL> shutdown immediate
 
-- Rename all control files using OS commands
 
--Startup issued with the control files missing
SQL> startup
ORA-00205: error IN identifying control file, CHECK alert log FOR more info
 
 

Now we can treat this situation as having to create control files. If the control files are missing, then the database cannot be mounted, Any attempt to mount will fail because of the missing files.

1
2
3
4
5
6
7

SQL> SELECT STATUS FROM v$instance;
STATUS 
------------ 
STARTED
 
SQL> ALTER DATABASE mount;
ERROR at line 1: ORA-00205: error IN identifying control file, CHECK alert 
log FOR more info

No problem, we’ll use the command to create a control file from trace.

1
2

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\TRACE.TXT';
ERROR at line 1: ORA-01507: DATABASE NOT mounted

As you can see, not being mounted is a show stopper for this approach. What is required is to manually create a statement. We need the names of all of the datafiles and redolog files in the database for this step (see the first diagram).

We have to match the redo log filename to the redo log group. That can be tricky because with N groups, there will be N! ways of making those assignments.

Using what Windows is reporting as the file sizes (in KB), we can construct a CREATE CONTROLFILE statement as shown:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE CONTROLFILE 
       REUSE               --Existing data files will be used
       DATABASE "DEVTEST"  --Name of the DB 
       NORESETLOGS         --Existing log files will be used
       ARCHIVELOG          --Database was in archiveing mode
MAXLOGFILES 16       --A Typical value of oracle 10g
MAXLOGMEMBERS 3      --A Typical value of oracle 10g
MAXDATAFILES 100     --A Typical value of oracle 10g
MAXINSTANCES 8       --A Typical value of oracle 10g
MAXLOGHISTORY 292    --A Typical value of oracle 10g
 
LOGFILE 
GROUP 1 'C:\oracle_data_files\DEVTEST\redo01.log' SIZE 50M, 
GROUP 2 'C:\oracle_data_files\DEVTEST\redo02.log' SIZE 50M, 
GROUP 3 'C:\oracle_data_files\DEVTEST\redo03.log' SIZE 50M 
 
DATAFILE 
'C:\oracle_data_files\DEVTEST\EXAMPLE01.DBF', 
'C:\oracle_data_files\DEVTEST\SYSAUX01.DBF', 
'C:\oracle_data_files\DEVTEST\SYSTEM01.DBF', 
'C:\oracle_data_files\DEVTEST\UNDOTBS01.DBF', 
'C:\oracle_data_files\DEVTEST\USERS01.DBF' ;

If get the error look like this you need to adjust redo log or datafile sizes

1
2
3
4
5
6
7
8
9
10
11
12

ORA-01503: CREATE CONTROLFILE failed 
ORA-01163: SIZE clause indicates 9921 (blocks), but should match header 9920
ORA-01110: DATA file 4: 'D:\oracle\product\10.2.0\oradata\db10\users01.dbf'
 
--[For Datafiles]---
SQL> SHOW parameter db_block --get the DB block size default is 8192 in 10g
Expected size = Expected # of blocks * db_block_size / 1024 
9920 * 8192 /1024 = 79360K
 
--[For RedoLog files]---
Expected size = Expected # of blocks * 512 / 1024 
9920 * 512 /1024 = 4960K

Now that the control file(s) has been created, what is the state of the database? Verify that your files have been created, and then select the status from V$INSTANCE and see MOUNTED. If the database is mounted, can it be opened? The answer is yes.

1
2

SQL> ALTER DATABASE MOUNT;
SQL> ALTER DATABASE open;

You can notice there is no temp file associated with database we need to add temp file.

1
2
3

ALTER TABLESPACE "TEMP" 
ADD TEMPFILE 'C:\ORACLE_DATA_FILES\DEVTEST\TEMP01' 
SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Your DB is now opened and full functional.

3. Restoring and Recovering Datafiles and Tablespaces

The database is open, and some but not all of the datafiles are damaged. You want to restore and recover the damaged tablespace, while leaving the database open so that the rest of the database remains available. You discover that the damaged datafiles are from the tablespaces users.

Background

Pre-requests

• Make sure the database is mounted or open
• A Closed or open DB backup and archivelog is required

Create Scenario

1
2
3
4
5
6
7
8
9

SQL> connect sys/srdc 
SQL> select file_name from dba_data_files where tablespace_name = 'SYSTEM';
     C:\ORACLE_DATA_FILES\DEVTEST\SYSTEM01.DBF
 
SQL> shutdown immediate; 
SQL> startup; 
 
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file 
ORA-01110: data file 1: 'C:\ORACLE_DATA_FILES\DEVTEST\SYSTEM01.DBF' 

Solutions-1 [Default Location]

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#Connect to the target database
C:\RMAN TARGET SYS/SRDC 
 
#Take the affected tablespaces offline if they are not already offline.
RMAN> SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
 
#Restore the tablespace or datafile
RMAN> RESTORE TABLESPACE users;
 
#Rrecover the tablespace or datafile
RMAN> RECOVER TABLESPACE users;
 
#If RMAN reported no errors, then bring the tablespace back online:
RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Solutions-2 [New Location]

Assume that the old datafiles were stored in directory /olddisk and the new ones will be stored in /newdisk.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

RUN {
SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
SQL 'ALTER TABLESPACE tools OFFLINE IMMEDIATE';
# specify the new location for each datafile
SET NEWNAME FOR DATAFILE '/olddisk/users01.dbf' TO '/newdisk/users01.dbf';
SET NEWNAME FOR DATAFILE '/olddisk/tools01.dbf' TO '/newdisk/tools01.dbf';
RESTORE TABLESPACE users, tools;
SWITCH DATAFILE ALL;   # update control file with new filenames
RECOVER TABLESPACE users, tools;
} 
 
#If recovery is successful, then bring the tablespaces online:
SQL 'ALTER TABLESPACE users ONLINE';
SQL 'ALTER TABLESPACE tools ONLINE';

Solutions-3 [System Tablespace is missing]

if a non-system tablespace is missing or corrupted while database is open, recovery can be performed while DB remains open.

if the system tablespace is missing or corrupted the DB can not be started up so a complete closed based recovery must be performed