Hot and Cold Backup Restore full process

Hot and Cold Backup Restore full process

********************************************************************************************************************************
Backup Restore test from HotBackup -
Source Database Name - srcdb
Source Host Name - Server1
Target Database Name - dstdb
Target Host Name - Server2
Oracle Version on Test and Destination - 9.2.0.8

** As the Database HOtBackup resotre getting tested on same shared home Database name has been changed wihle restore.
*********************************************************************************************************************************

Steps while Implementation.

1) Creating Directory Strecture on the Target server (Server2)

$ cd /mounts/dstdb_data
$ mkdir -p oradata/dstdb/dbfiles

$ cd /mounts/dstdb_temp
$ mkdir -p oradata/dstdb
$ cd oradata/dstdb
$ mkdir adump bdump cdump create exp pfile udump tmpfiles

$ cd /mounts/dstdb_arch
$ mkdir -p dstdb/arch

2) Copy data from Source server (Server1) to Target Server (Server2).

$ scp -r *.dbf oracle@Server2.servername.com:/mounts/dstdb_data/oradata/dstdb/dbfiles/
$ scp -r *.rdo oracle@Server2.servername.com:/mounts/dstdb_data/oradata/dstdb/dbfiles/

3) copy Pfile from Source server(Server1) to Target serve (Server2).

$ cd /mounts/ora_home/app/oracle/product/9.2.0.8/dbs/
$ scp -r initsrcdb.ora oracle@Server2.servername.com:/mounts/dstdb_temp/oradata/dstdb/pfile

** Edit the file and change the path according to new server locaiton.

4) Copy Controlfile trace from Source server(Server1) to Target serve (Server2).

** Connect database and issue " alter database backup controlfile to trace "

$ cd /mounts/clntrcog_temp/oradata/clntrcog/udump
$ cp oracle@Server2.servername.com:/mounts/dstdb_temp/oradata/dstdb/pfile

5) Edit contrlfile as below.

CREATE CONTROLFILE SET DATABASE "dstdb" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log1asrcdb.rdo' SIZE 20M,
GROUP 2 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log2asrcdb.rdo' SIZE 20M,
GROUP 3 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log3asrcdb.rdo' SIZE 20M
DATAFILE
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_undotbs1.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_users.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_tools.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/clintrace.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/coding.dbf'
CHARACTER SET US7ASCII
;

** save the script as below crectl.sql ( /mounts/dstdb_temp/oradata/dstdb/pfile/crectl.sql)
6) Make an Entry in oratab.

$ vi /var/opt/oracle/oratab
** add a line as below
dstdb:/mounts/ora_home/app/oracle/product/9.2.0.8:N

7) Set environment Verialbles.

$ oenv

1\) dstdb

2\) otrdb

Choose an ORACLE_SID [1-2]:1

8) Start database in no mount stage with Pfile.

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 24 11:23:13 2008

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> startup pfile=/mounts/dstdb_temp/oradata/dstdb/pfile/initdstdb.ora nomount;
ORACLE instance started.

Total System Global Area 338657376 bytes
Fixed Size 729184 bytes
Variable Size 234881024 bytes
Database Buffers 100663296 bytes
Redo Buffers 2383872 bytes

9) Create control file that created.

SQL> @/mounts/dstdb_temp/oradata/dstdb/pfile/crectl.sql

Control file created.

10) Open Database

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309509 generated at 06/23/2008 21:00:09 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc
ORA-00280: change 5309509 for thread 1 is in sequence #306


Specify log: {=suggested filename AUTO CANCEL}
AUTO
ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309509 generated at 06/23/2008 21:00:09 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc
ORA-00280: change 5309509 for thread 1 is in sequence #306


Specify log: {=suggested filename AUTO CANCEL}
AUTO
ORA-00279: change 5309625 generated at 06/23/2008 21:03:37 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc
ORA-00280: change 5309625 for thread 1 is in sequence #307
ORA-00278: log file
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

**Copy Archive logs from Source server(Server1) to Target serve (Server2)

$ cd /mounts/srcdb_arch/srcdb
$ scp -r ORACLE_SID_arch0000000306.arc oracle@Server2.servername.com:/mounts/dstdb_arch/dstdb/arch/


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309625 generated at 06/23/2008 21:03:37 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc
ORA-00280: change 5309625 for thread 1 is in sequence #307


Specify log: {=suggested filename AUTO CANCEL}
AUTO
ORA-00279: change 5322298 generated at 06/24/2008 07:34:24 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000308.arc
ORA-00280: change 5322298 for thread 1 is in sequence #308
ORA-00278: log file
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc' no longer
needed for this recovery

Server1-> $ cd /mounts/srcdb_arch/srcdb
Server1-> $ scp -r ORACLE_SID_arch0000000307.arc
Server1-> oracle@Server2.servername.com:/mounts/dstdb_arch/dstdb/arch/


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000308.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

11) Open database resetlogs

SQL> alter database open resetlogs;

Database altered.

12) create temp tablespace

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/mounts/dstdb_temp/oradata/dstdb/tmpfiles/srcdb_temp.tpf'SIZE 100M REUSE AUTOEXTEND OFF;

13) Shutdown database and put the database back in archive log mode.

SQL> shutdown immediat;
SQL> startup mount;
SQL> alter database archivelog;
sQL> alter database open;

14) Create SPfile.

SQL> create spfile from pfile='/mounts/dstdb_temp/oradata/dstdb/pfile/initdstdb.ora';


SQL> select * from v$instance;


INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 dstdb
Server2
9.2.0.8.0 24-JUN-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

15) Create password file

orapwd file=orapwdstdb password=******

Tablespace usage - Script

Tablespace usage - Script
set pages 999col tablespace_name format a40
col "Size MB" format 999,999,999
col "Free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name union all select tablespace_name ' **TEMP**' , sum(bytes)/1024/1024 used_mb from dba_temp_files group by tablespace_name) tsu, (select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) tsfwhere tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/

On fly compression ..Export or Import Database on fly

On fly compression -> Export or Import Database on fly… ( On the FLY Compression)

This generally done through pipes on Unix environments.. the process involved in creating a pipe and start the export which run the compression in background.

As an example, here you can find a largely used script to perform on the fly compression of an export operation

#Creating or making a pipe
mknod expdb1.dmp p
#Start compress the pipe in background
gzip -c expdb1.dmp.gz &
& makes the script run in background.
# Wait start the export
sleep 5
# Start the export
exp scott/tiger file=expdb1.dmp
A typical script to execute an import operation reading directly from a compressed file is
# Make a pipe
mknod expdb1.dmp p
# Start decompress to the pipe in background
gzip -c <> expdb1.dmp > &
# Wait start the import
sleep 5
# Start the import
imp scott/tiger file=expdb1.dmp

Find Command......

Most case we come across Arch space full. In multiple if Database and multiple folders on same mount point, getting in to each folder and zipping is time consuming. Find the find command, very helpful in this case.

find <Mount Point> -name <filename.extension> -exec <operaton>;

Few examples.

find /mounts/arch/* -name “*.arc” –exec /user/bin/gzip {} \;

Above script will find all .arc files in the specified mount files and zip all the files.

find /mounts/arch/* -name “*.arc” –mtime +7 –exec /user/bin/gzip {} \;

This scrip will find the find and zip .arc files older than 7 days.

find /mount/arch/* -name “*.arc” –mtime +7 –exec rm {} \;

This scrip will find the find and removes .arc files older than 7 days

Hence can modified .. the way we want........ depending the context.....

My Blog.. My .. Oracle.......

welcome you all ... to the world of Oracle...... This blog will run through some .. good implementations... in ORACLE..... mostly mine..........

This is mostly our blog more then mine....... suggestions and corrections .. always welcome.... you can reach me any time at snsivaprasad@gmail.com or sivaprasad.sn@gmail.com