Restore Oracle database from Netbackup..Or…”Gimme Back My Database!”

When switching over from regular FRA backups to Netbackup, things changed a bit in the way restores are done. At least at our site. The good news is, although the backup and scheduling is not the task of the DBA anymore, but of the storage/backup manager, the DBA still maintains control of the restore procedure.

Although it is kind of sad to read in the manual of Netbackup under the restore procedure: “Contact your DBA..”

So in order to fill in this “gap” in the documentation, this post will document the steps needed to restore a full database from a Netbackup.

Before attempting to restore the database make sure:

  • The control-file is also backed up and available in the backup-list.
  • There is enough space to hold the restored database.
  • The DBID is known (see other post to find the DBID from the backup).

The restore will be done on the machine where the database was located (we simulated a complete loss by dropping the database) and the user ORACLE will do this restore. This makes sure the environment is set correctly.

Step 1: Get the SPFILE from the backup.

Create a dummy pfile in the location $ORACLE_HOME/dbs:

$ vi initdummydb.ora

Paste the following into this file:

db_name='dummydb'
memory_target=1G
processes = 150
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest_size=2G
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'

Save and close.

Startup the dummydb instance in nomount mode.

sql> startup nomount;

Since there is no spfile, the instance will use the pfile just created, and defined by the SID we have set.

Now we can either let the RMAN figure out which of the many files contain the spfile/controlfile, or we can provide a small hint, speeding things up a bit. As you may guess, the lookup through a lot of backups takes a long time.

Determine the file we need:

$ /usr/openv/netbackup/bin/bplist -C servername -t 4 -l -R /

-rw-rw---- oracle    dba          47972352 Nov 17 12:53 /c-1089834388-20151117-05
-rw-rw---- oracle    dba          47972352 Nov 17 12:52 /ctrl_dDUMMYDB01_uvbqmg5vk_s4075_p1_t896014324
-rw-rw---- oracle    dba          47972352 Nov 17 12:51 /c-1089834388-20151117-04
-rw-rw---- oracle    dba           9699328 Nov 17 12:50 /arch_dDUMMYDB01_uv9qmg5sb_s4073_p1_t896014219
-rw-rw---- oracle    dba          47972352 Nov 17 12:49 /c-1089834388-20151117-03
-rw-rw---- oracle    dba          16777216 Nov 17 12:48 /bk_dDUMMYDB01_uv7qmg5om_s4071_p1_t896014102
-rw-rw---- oracle    dba          47972352 Nov 17 10:09 /c-1089834388-20151117-02
-rw-rw---- oracle    dba          47972352 Nov 17 10:08 /ctrl_dDUMMYDB01_uv4qmfsd8_s4068_p1_t896004520
-rw-rw---- oracle    dba          47972352 Nov 17 10:07 /c-1089834388-20151117-01
-rw-rw---- oracle    dba          71565312 Nov 17 10:06 /arch_dDUMMYDB01_uv2qmfs9c_s4066_p1_t896004396
-rw-rw---- oracle    dba          47972352 Nov 17 10:05 /c-1089834388-20151117-00
-rw-rw---- oracle    dba         61254912K Nov 17 09:39 /bk_dDUMMYDB01_uv0qmfqlk_s4064_p1_t896002740

*UPDATE*: The file we need is the LATEST /c-1089834388-20151117-05 file..otherwise we will run into errors like: “ORA-01152: file XX was not restored from a sufficiently old backup”.

If the list is to extensive (we have one client backing up a lot of databases) try the following:

/usr/openv/netbackup/bin/bplist -C client_name -t 4 -l -R -s 01/18/2016 -e 01/18/2016 -I -keyword "*dummy*" /

This will provide a list of backups started and ended on 18th of January and have the case-insensitive  phrase “dummy”.

We need the /c-1089834388-20151117-05 file, so lets see if we can get this from netbackup:

RMAN > run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore spfile to '/tmp/spfile' from '/c-1089834388-20151117-05';
release channel t1;
}

In short: We create a channel to the net-backup media, send some info so the agent knows a bit about the environment and we ask politely to get the spfile from the auto-backup where we say it is.

Output:

allocated channel: t1
channel t1: SID=194 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 7.6 (2013111313)

sent command to channel: t1

Starting restore at 20-NOV-15

channel t1: restoring spfile from AUTOBACKUP /c-1089834388-20151117-05
channel t1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-NOV-15

released channel: t1
RMAN>

The spfile is now located in the /tmp location. This is not really necessary but since you are reading this blog, it is better to be safe than sorry and take small steps..  😉 .

Copy the spfile to the $ORACLE_HOME/dbs location and rename it to the original database name, eg spfiledummydb01.ora

NOTE: If the spfile is not in the backup, just take a deep breath, but don’t panic. The spfile is not the biggest loss, just continue with a new one.

Now we have the database started in nomount mode with a dummy pfile, and a restored spfile.

Bounce the database, and make sure to use the restored spfile to start the db in nomount mode (ergo: delete the dummy pfile before starting the db).

Connect rman to the instance (nomount) since we don’t have the control files yet.

$ rlwrap rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 11:31:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DUMMYDB01 (not mounted)
RMAN>

Using the bplist from before, we are going to restore the controlfile also to the /tmp location:

run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore controlfile to '/tmp/controlfile.ctl' from '/c-1089834388-20151117-05';
release channel t1;
}

When using ASM diskgroups, you would use the command:

run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore controlfile to '+DATADG' from '/c-1089834388-20151117-05';
release channel t1;
}

The output would be something like:

using target database control file instead of recovery catalog
allocated channel: t1
channel t2: SID=926 device type=SBT_TAPE
channel t2: Veritas NetBackup for Oracle - Release 7.6 (2013111313)
sent command to channel: t1
Starting restore at 19-JAN-16
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:01:05
Finished restore at 19-JAN-16

released channel: t1
RMAN>

Note: Rome was not build in one day, and neither was this document..hence the leap in dates.. 😉

The restored controlfile is located in the /tmp, or in the +DATADG diskgroup. In case of a /tmp restore, place the controlfile in the correct location and rename if needed. Just make sure to reflect these changes in the pfile/spfile later on.

Make sure to use the control file found in the +DATADG location!

Create a pfile from the spfile, shutdown the database, and alter the pfile to reflect the correct location of the control file;

*.audit_file_dest='/oracle/11.2.0.4/base/db/dbhome_1/admin/DUMMYDB01/adump'
*.cluster_database=FALSE
*.compatible='11.2.0.0.0'
*.control_files='+DATADG/DUMMYDB01/controlfile/current.309.901542237'
*.core_dump_dest='/oracle/11.2.0.4/base/diag/rdbms/DUMMYDB01/DUMMYDB01/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='DUMMYDB01'
*.db_recovery_file_dest='+FRADG'
*.db_recovery_file_dest_size=245722M
*.diagnostic_dest='/oracle/11.2.0.4/base'
*.log_buffer=16285696# log buffer update
*.memory_max_target=10G
*.memory_target=10G
*.open_cursors=3500
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.optimizer_use_sql_plan_baselines=FALSE
*.pga_aggregate_target=512M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_listener='servername:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=41952K
*.sga_target=8G
*.skip_unusable_indexes=TRUE

SQL> startup mount pfile='/oracle/11.2.0.4/base/db/dbhome_1/dbs/initdummydb01.ora';
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3690989952 bytes
Database Buffers         6979321856 bytes
Redo Buffers               16900096 bytes
Database mounted.

SQL> create spfile from pfile='/oracle/11.2.0.4/base/db/dbhome_1/dbs/initdummydb01.ora';
File created.
SQL>

*UPDATE* We can either restore until RMAN runs out of archivelogs and throws an error, or we just declare where to stop:

$ rlwrap rman target /
RMAN> list backup of archivelog all;

 List of Archived Logs in backup set 2449
 Thrd Seq Low SCN Low Time Next SCN Next Time
 ---- ------- ---------- --------- ---------- ---------
 1 5086 6491516245 28-SEP-16 6491926033 29-SEP-16
 1 5087 6491926033 29-SEP-16 6491926067 29-SEP-16

With this information from the control file, we know we can restore until 5088(!) and using thread 1.

Now to get the database files back:

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 12:45:51 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMYDB01 (DBID=1089834399, not open)

RMAN> run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
SET UNTIL SEQUENCE 5088 THREAD 1;
restore database;
switch datafile all; # Update control file with new location of data files.
recover database;
}

allocated channel: t1
channel t1: SID=992 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 7.6 (2013111313)

sent command to channel: t1

executing command: SET until clause

Starting restore at 19-JAN-16
Starting implicit crosscheck backup at 19-JAN-16
Crosschecked 23 objects
Finished implicit crosscheck backup at 19-JAN-16

Starting implicit crosscheck copy at 19-JAN-16
Crosschecked 61 objects
Finished implicit crosscheck copy at 19-JAN-16

searching for all files in the recovery area
cataloging files...
cataloging done

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to +DATADG/dummydb01/datafile/system.302.896027315
channel t1: restoring datafile 00002 to +DATADG/dummydb01/datafile/sysaux.265.896026599
channel t1: restoring datafile 00003 to +DATADG/dummydb01/datafile/undotbs1.275.896027083
.....
channel t1: restoring datafile 00061 to +DATADG/dummydb01/datafile/some_data.314.896027867
channel t1: reading from backup piece bk_dDUMMYDB01_ud1qrgm1h_s4513_p1_t901273649

channel t1: piece handle=bk_dDUMMYDB01_uddqrnfhr_s4525_p1_t901496379 tag=TAG20160118T233938
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=355
channel t1: reading from backup piece arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=355
channel t1: reading from backup piece arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540
channel t1: piece handle=arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540 tag=TAG20160118T234220
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05
archived log file name=+FRADG/dummydb01/archivelog/2016_01_19/thread_1_seq_355.256.901545167 thread=1 sequence=355
channel default: deleting archived log(s)
archived log file name=+FRADG/dummydb01/archivelog/2016_01_19/thread_1_seq_355.256.901545167 RECID=10007 STAMP=901545168
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-JAN-16
released channel: t1

RMAN>
Recovery Manager complete

Start the database in open mode, resetting archivelogs:

SQL> alter database open resetlogs;

Database altered.

SQL>

And that’s basically it. The database is restored, and ready to be abused.

Note: in this example the database runs on only one control file, make sure to create a backup one in the FRA before releasing the database to the users.

Also: in case of a missing SPFILE, now is the time to adjust the database to your liking, not when users are accessing the database. Most values are dynamic, but a few important ones are not.

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s