5). Create Oracle service

As described in section 2 of Part II, an Oracle service must be exist before a database is created. The service is created using the oradim utility, which must be run from the command line. The following commands show how to create and modify a service (comments in italics, typed commands in bold):

--create a new service with manual startup

C:\>oradim -new -sid ORCL -startmode m

--modify service to startup automatically

C:\>oradim -edit -sid ORCL -startmode a

Unfortunately oradim does not give any feedback, but you can check that the service exists via the Services administrative panel. The service has been configured to start automatically when the computer is powered up. Note that oradim offers options to delete, startup and shutdown a service. See the documentation for details.

6. Restore and recover database

Now it is time to get down to the nuts and bolts of database recovery. There are several steps, so we'll list them in order:
  1. Copy password and tnsnames file from backup: The backed up password file and tnsnames.ora files should be copied from the backup directory (e:\backup, in our example) to the proper locations. Default location for password and tnsnames files are ORACLE_HOME\database ORACLE_HOME\network\admin respectively.

  2. Set ORACLE_SID environment variable: ORACLE_SID should be set to the proper SID name (ORCL in our case). This can be set either in the registry (registry key: HKLM\Software\Oracle\HOME<X>\ORACLE_SID) or from the system applet in the control panel.

  3. Invoke RMAN and set the DBID: We invoke rman and connect to the target database as usual. No login credentials are required since we connect from an OS account belonging to ORA_DBA. Note that RMAN accepts a connection to the database although the database is yet to be recovered. RMAN doesn't as yet "know" which database we intend to connect to. We therefore need to identify the (to be restored) database to RMAN. This is done through the database identifier (DBID). The DBID can be figured out from the name of the controlfile backup. Example: if you use the controlfile backup format suggested in Part I, your controlfile backup name will be something like "CTL_SP_BAK_C-1507972899-20050228-00". In this case the DBID is 1507972899. Here's a transcript illustrating the process of setting the DBID:

    C:\>rman

    Recovery Manager: Release 9.2.0.4.0 - Production

    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

    RMAN> connect target /

    connected to target database (not started)

    RMAN> set dbid 1507972899

    executing command: SET DBID

    RMAN>

  4. Restore spfile from backup: To restore the spfile, you first need to startup the database in the nomount state. This starts up the database using a dummy parameter file. After that you can restore the spfile from the backup (which has been restored from tape in Section 3). Finally you restart the database in nomount state. The restart is required in in order to start the instance using the restored parameter file. Here is an example RMAN transcript for the foregoing procedure. Note the difference in SGA size and components between the two startups:

    RMAN> startup nomount

    startup failed: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITORCL.ORA'

    trying to start the Oracle instance without parameter files ...
    Oracle instance started

    Total System Global Area 97590928 bytes

    Fixed Size 454288 bytes
    Variable Size 46137344 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 667648 bytes

    RMAN> restore spfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050228-00';

    Starting restore at 01/MAR/05

    using target database controlfile instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=9 devtype=DISK
    channel ORA_DISK_1: autobackup found: e:\backup\CTL_SP_BAK_C-1507972899-20050228-00
    channel ORA_DISK_1: SPFILE restore from autobackup complete
    Finished restore at 01/MAR/05

    RMAN> startup force nomount

    Oracle instance started

    Total System Global Area 1520937712 bytes

    Fixed Size 457456 bytes
    Variable Size 763363328 bytes
    Database Buffers 754974720 bytes
    Redo Buffers 2142208 bytes

    RMAN>

    The instance is now started up with the correct initialisation parameters.

    We are now in a position to determine the locations of control file and archive destination, as this information sits in the spfile. This is done via SQL Plus as follows:

    C:\>sqlplus /nolog

    ....output not shown

    SQL>connect / as sysdba
    Connected.
    SQL> show parameter control_file

    ....output not shown

    SQL> show parameter log_archive_dest
    ....output not shown

    The directories listed in the CONTROL_FILES and LOG_ARCHIVE_DEST_N parameters should be created at this stage if they haven't been created earlier.

  5. Restore control file from backup: The instance now "knows" where the control files should be restored, as this is listed in the CONTROL_FILES initialisation parameter. Therefore, the next step is to restore these files from backup. Once the control files are restored, the instance should be restarted in mount mode. A restart is required because the instance must read the initialisation parameter file in order to determine the control file locations. At the end of this step RMAN also has its proper configuration parameters, as these are stored in the control file.

    Here is a RMAN session transcript showing the steps detailed here:

    RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050228-00';

    Starting restore at 01/MAR/05

    allocated channel: ORA_DISK_1
    hannel ORA_DISK_1: sid=13 devtype=DISK
    channel ORA_DISK_1: restoring controlfile
    channel ORA_DISK_1: restore complete
    replicating controlfile
    input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
    output filename=E:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL02.CTL
    output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
    Finished restore at 01/MAR/05

    RMAN> shutdown

    Oracle instance shut down

    RMAN> exit

    Recovery Manager complete.

    C:\>rman target /

    Recovery Manager: Release 9.2.0.4.0 - Production

    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

    connected to target database (not started)

    RMAN> startup mount;

    Oracle instance started
    database mounted

    Total System Global Area 1520937712 bytes

    Fixed Size 457456 bytes
    Variable Size 763363328 bytes
    Database Buffers 754974720 bytes
    Redo Buffers 2142208 bytes

    RMAN> show all;

    using target database controlfile instead of recovery catalog
    RMAN configuration parameters are:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
    CONFIGURE BACKUP OPTIMIZATION OFF; # default
    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:\backup\ctl_sp_bak_%F';
    CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
    CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
    CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
    CONFIGURE MAXSETSIZE TO UNLIMITED; # default
    CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFORCL.ORA'; # default

    RMAN>

    At this stage we can determine the locations of data files and redo logs if we don't know where they should go. This is done from SQL Plus as follows:

    C:\>sqlplus /nolog

    ...output not shown

    SQL> connect / as sysdba
    Connected.
    SQL> select name from v$datafile;

    ...output not shown

    SQL> select member from v$logfile;

    ...output not shown

    SQL>

    The directories shown in the output should be created manually if this hasn't been done earlier.

  6. Restore all datafiles: This is easy. Simply issue a "restore database" command from RMAN, and it will do all the rest for you:

    RMAN> restore database;

    Starting restore at 01/MAR/05

    using target database controlfile instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=11 devtype=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: sid=8 devtype=DISK
    channel ORA_DISK_1: starting datafile backupset restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
    restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
    restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS02.DBF
    channel ORA_DISK_2: starting datafile backupset restore
    channel ORA_DISK_2: specifying datafile(s) to restore from backup set
    restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
    restoring datafile 00005 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
    restoring datafile 00006 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS02.DBF
    channel ORA_DISK_2: restored backup piece 1
    piece handle=E:\BACKUP\80G6E1TT_1_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_1: restored backup piece 1
    piece handle=E:\BACKUP\81G6E1TU_1_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_2: restored backup piece 2
    piece handle=E:\BACKUP\80G6E1TT_2_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_1: restored backup piece 2
    piece handle=E:\BACKUP\81G6E1TU_2_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_1: restored backup piece 3
    piece handle=E:\BACKUP\81G6E1TU_3_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_1: restore complete
    channel ORA_DISK_2: restored backup piece 3
    piece handle=E:\BACKUP\80G6E1TT_3_1.BAK tag=TAG20041130T222501 params=NULL
    channel ORA_DISK_2: restore complete
    Finished restore at 01/MAR/05

    RMAN>

  7. Recover database: The final step is to recover the database. Obviously recovery is dependent on the available archived (and online) redo logs. Since we have lost our database server and have no remote archive destination, we can recover only up to the time of the backup. Further, since this is an incomplete recovery, we will have to open the database with resetlogs. Here's a sample RMAN session illustrating this:

    RMAN> recover database;