Restore database on another server - 2

Steps (1 to 10) to restore database on another server

1. create directory structure

2. add database to /etc/oratab or /var/opt/oracl/oratab

3. copy init.ora file from source

4. edit init.ora – replace controlfile name with diskgroup name such as ORADATA

5. startup nomount

6. start restore using restore shell script

  (NOTE : This will restore archive log files too)

7. monitor alert.log and restore log

8. after restore and recover completes

9. edit inot.ora file - replace controlfile parameter with real name

10. startup database; open for business

Sample init.ora file

  processes                = 500

  backup_tape_io_slaves    = TRUE

  sga_target               = 700m

  control_files            = "+ORADATA001", "+ORAFLASH001"

  control_file_record_keep_time= 14

  db_block_size            = 8192

  compatible               = 10.2.0.4.0

  log_archive_format       = %t_%s_%r.arc

  log_checkpoint_timeout   = 0

  db_file_multiblock_read_count= 16

  db_create_file_dest      = +ORADATA001

  db_create_online_log_dest_1= +ORADATA001

  db_create_online_log_dest_2= +ORAFLASH001

  db_recovery_file_dest    = +ORAFLASH001

  db_recovery_file_dest_size= 107374182400

  fast_start_mttr_target   = 900

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  undo_retention           = 18000

  remote_login_passwordfile= EXCLUSIVE

  job_queue_processes      = 10

  background_dump_dest     = /ora01/oracle/admin/demo/bdump

  user_dump_dest           = /ora01/oracle/admin/demo/udump

  core_dump_dest           = /ora01/oracle/admin/demo/cdump

  audit_file_dest          = /ora01/oracle/admin/demo/adump

  db_name                  = MZXP

  open_cursors             = 5000

  pga_aggregate_target     = 524288000

Sample restore command script : rman_restore_db.cmd

# script for restoring from an inconsistent backup, ie archivelog mode database

# change DBID  ###

# change NB_ORA_CLASS as appropriate ###

# change set option as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

#set DBID = DBID;

startup nomount pfile='/ora01/oracle/admin/demo/pfile/initDEMO.ora';    # because there is no controlfile

run {

  allocate channel t1 type sbt;

  allocate channel t2 type sbt;

  set command id to 'rman restore';

# step 1 - restore controlfile from autobackup, faster if give explicit file name from list backup

# restore controlfile from autobackup;

  restore controlfile from 'c-49-20110602-02';    # change file name

  alter database mount;    # start using the restored controlfile

#  step 2 - set the recover to time using scn, time, or sequence #

#  omitting recovers to current time

#  the scn and sequence number can be determined from the list backup output

#  set until scn 562449965260;

  set until time "to_date('06-02-2011 18:00:00','mm-dd-yyyy hh24:mi:ss')";

#  set until sequence nnnn thread 1;

# use set newname commands here if restoring to a different directory structure, for ex

# set newname for datafile '/db/ora01/oracle/admin/ORA_SID/link/system01.dbf'

#  to '/ora01/oracle/admin/ORA_SID/link/system01.dbf';

# step 3 - restore the datafiles from the full backup based on set until command

  restore database;

# switch datafile all;   # if used set newname command, changes the names in the contro

# step 4 - recover applies logs up to set until time. It will restore logs from tape if

  recover database;

# step 5 - open the database and recreate the online redo logs

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

Sample restore shell script : restore.sh

. ~/.profile

ORACLE_SID=DEMO;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

cd /ora01/oracle/admin/BACKUP/DEMO

rman nocatalog target / cmdfile rman_restore_db.cmd

status=$?

exit $status

Hope this helps! Rupam

Restore Archivelog with Particular sequence

Restore Archivelog with Particular sequence

RMAN> run {
allocate channel dsk1 type disk;
restore archivelog logseq 11645;
release channel dsk1;
 }

RAC - Restore Archivelog with Particular sequence and thread

RMAN> run {
allocate channel ch01 type disk format '/ora_backup/u0006/FIPRD3/archivelog/';
restore archivelog logseq 24208 thread 3;
}

Restore Archivelog with Particular sequence at diff. location

RMAN> run{
set archivelog destination to '/ora_backup/u0001/cloning/';
 restore archivelog sequence 6232 thread 3;
 }

generate awr report for database

// generate awr report for database ( not instance) for period 8 -9 am

conn sys/manager@&1 as sysdba
variable begin_snap_id number
variable end_snap_id number
variable db_id number
begin
  select distinct snap_id into :begin_snap_id from dba_hist_snapshot
  where BEGIN_INTERVAL_TIME like '01-SEP-13 07%AM%';
  :end_snap_id := :begin_snap_id + 1;
  select DBID into :db_id from v$database;
end;
/
define  num_days     = 1;
define  db_name      = &1;
define  dbid         = :db_id;
define  begin_snap   = :begin_snap_id;
define  end_snap     = :end_snap_id;
define  report_type  = 'html';
define  instance_numbers_or_ALL = '1,2,3'
define  time         = 8am
define  report_name  = /tmp/AWR/awr_&1._&time..html
@@?/rdbms/admin/awrgrpti
exit

dynamic tablespaces creation script

// dynamic tablespaces creation script

// crtbs.sh

while read tbs

   do

   echo "CREATE SMALLFILE TABLESPACE "$tbs " DATAFILE

   '/oradata01/oracle/oradata/${ORACLE_SID}/$tbs.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

   LOGGING

   ONLINE

   EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 10M

   SEGMENT SPACE MANAGEMENT AUTO

   ;"

   echo " "

   done< crtbs.dat

// crtbs.dat

MAX_DATA

MAX_INDX

list table parent / childs

//  finds child table in recursive . input parent table

Prompt By default starting tables are those that does not referenced other one
Accept Table_ CHAR PROMPT "Starting tables (% allowed) ? "
Set Termout OFF
Column table_ NOPRINT NEW_VALUE Table_
Select nvl(upper('&Table_'),'*NO REF*') table_ from dual
/
Set Termout ON
Column line_ FORMAT A120 HEADING "Foreign Key Tree"
Prompt
Set feedback off
With
  data as (
      /* Tables/FK along with the referenced table/constraints */
    select decode(b.table_name, a.table_name, '*SELF REF*', b.table_name)
             table_name,
           b.constraint_name,
           a.table_name r_table_name,
           a.constraint_name r_unq_name
    from user_constraints a, user_constraints b
    where b.r_owner = a.owner
      and b.r_constraint_name = a.constraint_name
      and b.constraint_type = 'R'
      and a.constraint_type in ('P', 'U')
    union all
      /* Referenced tables that referenced noone */
    select a.table_name, null, null, null
    from user_constraints a
    where exists ( select 1 from user_constraints b
                   where b.r_owner = a.owner
                     and b.r_constraint_name = a.constraint_name
                     and b.constraint_type = 'R'
                     and a.constraint_type in ('P', 'U') )
      and not exists ( select 1 from user_constraints b
                       where b.table_name = a.table_name
                         and b.constraint_type = 'R' )
  )
Select lpad(' ',3*(level-1)) ||
       decode(table_name, '*SELF REF*', r_table_name, table_name) ||
       decode(r_table_name,
              null, '',
              ' ('||constraint_name||' ref. '||
              r_table_name||'/'||r_unq_name||')' )||
       decode(table_name,
              '*SELF REF*', ' ...',
              decode(connect_by_iscycle, 1, ' ...'))
from data
connect by nocycle prior table_name = r_table_name
start with    ( '&Table_' = '*NO REF*' and r_table_name is null )
           or ( '&Table_' != '*NO REF*' and table_name like '&Table_' )
/