[Oracle] Restore backup from filesystem

Hi all,

in this tutorial we are going to restore a database backup from filesystem.

Setting-up instance

The first thing to do is to setup the database instance. The following parameter file can be used, it sets up only the required parameters to start up an instance.

*.cluster_database=FALSE
*.compatible='12.2.0.1.0'
*.control_files='+DG_DAT1/DATABASE_NAME/control01.ctl'
*.cpu_count=16
*.db_block_size=8192
*.db_create_file_dest='+DG_DATI'
*.db_files=1024
*.db_name='DATABASE_NAME'
*.db_recovery_file_dest='+DG_RECO'
*.db_recovery_file_dest_size=214748364800
*.db_unique_name='DATABASE_NAME'
*.diagnostic_dest='/u01/app/oracle'
*.heat_map='ON'
*.java_jit_enabled=TRUE
*.local_listener=''
*.open_cursors=300
*.pga_aggregate_limit=0
*.pga_aggregate_target=5G
*.processes=1024
*.remote_login_passwordfile='exclusive'
*.remote_os_authent=TRUE
*.sessions=665
*.sga_max_size=9126805504
*.sga_target=9126805504
*.use_large_pages='ONLY'

Suppose that this parameter file is saved in /tmp/pfile.ora, the next step is to create a spfile in the ASM diskgroup:

create spfile='+DG_DATI/DATABASE_NAME/spfileDATABASE_NAME.ora' from pfile='/tmp/pfile';

Next, the initialization file need to be created in the ORACLE_HOME/dbs directory with the following entry:

SPFILE='+DG_DATI/DATABASE_NAME/spfileDATABASE_NAME.ora'

Last thing to do before restore is to start the istance in nomount state from SQL*Plus:

SQL> startup nomount

Restore controlfile

The next step is to restore the controlfile from RMAN:

RMAN> restore controlfile from 'BACKUP_LOCATION/controlfile_backup.bck';

And then mount the database:

SQL> alter database mount;

Restore and recover of datafiles

Before restoring datafiles, there is the need to catalog them in the controlfile with the following command:

RMAN> catalog start with 'BACKUP_LOCATION';

NOTE: if the backup is splitted among multiple directories, the previous command needs to be issued for each of them.

Next, the following command have to launched to start restoring datafiles and then recovering them:

run {
set command id to 'RMAN_restore';

allocate channel t01 type disk format 'BACKUP_LOCATION';

set until SCN POINT_IN_TIME;
RESTORE DATABASE;
RECOVER DATABASE;

release channel t01;
}

NOTE: if there are multiple backup directories, allocate one channel to each of them.

NOTE: the ‘set until’ clause is not mandatory, if it is not present, the process will recover the database until the last moment it can. It is useful if the exact point in time is not known.

Post-restore activities

There are some activities that may be necessary when the restore process finishes.

Activate a standby database

It can happen that the restored backup has been taken from a standby database in a dataguard configuration. In order to put it in READ WRITE mode, there is the need to stop the recover process, clear all logfiles and activate the database. So, just issue the following commands:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> alter database recover managed standby database finish;
SQL> spool clear_logfiles.sql
SQL> select 'alter database clear logfile group '||group#||';' from v$logfile where type='standby';
SQL> start clear_logfiles.sql
SQL> alter database activate standby database;
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Put the database in noarchivelog mode

Usually, when a production database is restored in a non-production environment, there is no need to save all the archived logs. So, the following steps need to be performed to put the database in noarchivelog mode:

SQL> startup force mount;
SQL> alter database noarchivelog
SQL> startup force

The following commands delete all the generated and restored archived logs during the restore process:

RMAN> crosscheck archivelog all;
RMAN> configure archivelog deletion policy to none;
RMAN> delete expired archivelog all;
RMAN> delete archivelog all;

Put the database in cluster

The restored database is in single-instance mode. If usually the databases are in a cluster configurations among multiple nodes, the follwing steps needs to be performed:

• Copy the initialization and password files in the $ORACLE_HOME/dbs directory to all the nodes in the cluster
• Edit the /etc/oratab files with the correct entries (one for the database name and one for the instance name on that node)
• Add the cluster resource using the “srvctl add database” command specifying the node names involved in the cluster and the Oracle Home location
• Set the parameter “cluster_database” to “true” and restart the database

Following there are examples of the commands to launch:

srvctl add database -d DATABASE_NAME -o ORACLE_HOME -n NODE1,NODE2

SQL> alter system set cluster_database=true scope=spfile;

srvctl stop database -d DATABASE_NAME
srvctl start database -d DATABASE_NAME

That’s all. See you in the next tutorial!