How to restore an Oracle database from a cold backup?
The below procedure describes how to restore a database Oracle 11204 EBS from a cold backup.
The database I will restore comes from a backup that a colleague in charged of the material used in the certifications uses, it took it from a production EBS installation and share it with me, it is a 260GB database compressed to 16GB, so, I will need a partition with at least 350GB to start.
What we are going to do is leave it running in our environment for us to play with later.
The backup consists of the following files
DB11 is the file that has the Oracle home with the data files, on the other hand, contains the applications that use the database, in this case, we will discard the later and test_cfg is a script that will recreate the configuration of the database on its new home 🙂
Step1
decompress the DB11.tar.gz on its final destination, in my case, I will use /databases/product/ which is where I have other databases installed.
for the decompression, I used tar -xf /tmp/shared/ebs/DB11.tar.gz after a few minutes, I got the following files:
Step2
Set the ORACLE_HOME, ORACLE_BASE, and ORACLE_SID to make your life easier
export ORACLE_HOME=/databases/product/DB11/11.2.0.4
export ORACLE_BASE=/databases
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=test
and execute the following command to relink the binaries
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE=$ORACLE_BASE ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=ebsdbhome
finally as root execute
sudo $ORACLE_HOME/root.sh
then, modify the file $ORACLE_HOME/rdbms/libs/config.c and make sure your oracle user’s groups match its content.
it is commonly dba too, it depends on the groups the user oracle is in
then execute
mv config.o config.bck.001
make -f ins_rdbms.mk ioracle
step3
Startup the database in nomountstate and run the config_test.sql if you don’t have it, you will have to create your own, it is a script that recreates the database to link it its datafiles (below its content).
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607
LOGFILE
GROUP 1 (
'/scratch/app/oradata/ebs/data/log01a.dbf',
'/scratch/app/oradata/ebs/data/log01b.dbf'
) SIZE 1000M BLOCKSIZE 512,
GROUP 2 (
'/scratch/app/oradata/ebs/data/log02a.dbf',
'/scratch/app/oradata/ebs/data/log02b.dbf'
) SIZE 1000M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/scratch/app/oradata/ebs/data/system01.dbf',
'/scratch/app/oradata/ebs/data/system02.dbf',
'/scratch/app/oradata/ebs/data/system03.dbf',
'/scratch/app/oradata/ebs/data/system04.dbf',
'/scratch/app/oradata/ebs/data/system05.dbf',
'/scratch/app/oradata/ebs/data/ctxd01.dbf',
'/scratch/app/oradata/ebs/data/owad01.dbf',
'/scratch/app/oradata/ebs/data/a_queue02.dbf',
'/scratch/app/oradata/ebs/data/odm.dbf',
'/scratch/app/oradata/ebs/data/olap.dbf',
'/scratch/app/oradata/ebs/data/sysaux01.dbf',
'/scratch/app/oradata/ebs/data/apps_ts_tools01.dbf',
'/scratch/app/oradata/ebs/data/system12.dbf',
'/scratch/app/oradata/ebs/data/a_txn_data04.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind06.dbf',
'/scratch/app/oradata/ebs/data/a_ref03.dbf',
'/scratch/app/oradata/ebs/data/a_int02.dbf',
'/scratch/app/oradata/ebs/data/sysaux02.dbf',
'/scratch/app/oradata/ebs/data/system13.dbf',
'/scratch/app/oradata/ebs/data/system14.dbf',
'/scratch/app/oradata/ebs/data/system15.dbf',
'/scratch/app/oradata/ebs/data/system16.dbf',
'/scratch/app/oradata/ebs/data/system17.dbf',
'/scratch/app/oradata/ebs/data/system18.dbf',
'/scratch/app/oradata/ebs/data/system19.dbf',
'/scratch/app/oradata/ebs/data/system20.dbf',
'/scratch/app/oradata/ebs/data/system21.dbf',
'/scratch/app/oradata/ebs/data/system22.dbf',
'/scratch/app/oradata/ebs/data/system23.dbf',
'/scratch/app/oradata/ebs/data/system24.dbf',
'/scratch/app/oradata/ebs/data/system25.dbf',
'/scratch/app/oradata/ebs/data/a_ref04.dbf',
'/scratch/app/oradata/ebs/data/a_ref05.dbf',
'/scratch/app/oradata/ebs/data/a_ref06.dbf',
'/scratch/app/oradata/ebs/data/temp_prasad.dbf',
'/scratch/app/oradata/ebs/data/system10.dbf',
'/scratch/app/oradata/ebs/data/system06.dbf',
'/scratch/app/oradata/ebs/data/portal01.dbf',
'/scratch/app/oradata/ebs/data/system07.dbf',
'/scratch/app/oradata/ebs/data/system09.dbf',
'/scratch/app/oradata/ebs/data/system08.dbf',
'/scratch/app/oradata/ebs/data/system11.dbf',
'/scratch/app/oradata/ebs/data/undo01.dbf',
'/scratch/app/oradata/ebs/data/a_txn_data01.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind01.dbf',
'/scratch/app/oradata/ebs/data/a_ref01.dbf',
'/scratch/app/oradata/ebs/data/a_int01.dbf',
'/scratch/app/oradata/ebs/data/a_summ01.dbf',
'/scratch/app/oradata/ebs/data/a_nolog01.dbf',
'/scratch/app/oradata/ebs/data/a_archive01.dbf',
'/scratch/app/oradata/ebs/data/a_queue01.dbf',
'/scratch/app/oradata/ebs/data/a_media01.dbf',
'/scratch/app/oradata/ebs/data/a_txn_data02.dbf',
'/scratch/app/oradata/ebs/data/a_txn_data03.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind02.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind03.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind04.dbf',
'/scratch/app/oradata/ebs/data/a_txn_ind05.dbf',
'/scratch/app/oradata/ebs/data/a_ref02.dbf'
CHARACTER SET AL32UTF8
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/scratch/app/oradata/ebs/data/archive/1_1_931329208.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/scratch/app/oradata/ebs/data/archive/1_1_931329283.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/scratch/app/oradata/ebs/data/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/scratch/app/oradata/ebs/data/temp0001.dbf' REUSE;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/scratch/app/oradata/ebs/data/temp02.dbf' REUSE;
change /scratch/app/oradata/ebs/for the new location, in my case /databases/product/DB11
Then, make sure that the pfiles’ control files are pointing to the right locations
Make sure that under $ORACLE_HOME/dbs there is one pfile for the database with correct configuration, in my case I have the following files.
In my case, I will use inittest.ora
then execute the script, once finished, bounce the instance
all set!
