Step-by-step Cold Backup SAP DB refresh

Step-by-step Cold Backup

STEP 1: while source db is online do the following:
 -create backup of controlfile to trace
 “alter database backup controlfile to trace”.
 -create pfile from spfile
 SQL>create pfile from spfile
and push above two to target destination i.e. at $ORACLE_HOME/dbs directory.

STEP 2: Shutdown the source database


STEP 3: Copy all data files into the new directories on the new server.
You may change the file names if you want, but you must edit the controlfile to reflect the new data files
names on the new server.

    scp /u01/oradata/trgtdb/* newhost:/u01/oradata/dupdb
    scp /u01/oradata/trgtdb/* newhost:/u01/oradata/dupdb
    scp /u03/oradata/trgtdb/* newhost:/u03/oradata/dupdb
    scp /u04/oradata/trgtdb/* newhost:/u04/oradata/dupdb


STEP 4: start the source db:
STEP 5: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile
creation script by changing the following:
This will put the create database syntax in the trace file directory.
The trace keyword tells oracle to generate a script containing a create controlfile command and
store it in the trace directory identified in the user_dump_dest parameter of the init.ora file.
It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PROD/system01.dbf',
  '/u01/app/oracle/oradata/PROD/undotbs01.dbf',
  '/u01/app/oracle/oradata/PROD/sysaux01.dbf',
  '/u01/app/oracle/oradata/PROD/users01.dbf',
  '/u01/app/oracle/oradata/PROD/example01.dbf',
  '/u01/app/oracle/oradata/PROD/testtbs.dbf',
  '/u01/app/oracle/oradata/PROD/test02.dbf'
CHARACTER SET AL32UTF8
;


Old:

    CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS

New:

    CREATE CONTROLFILE SET DATABASE "DUPDB" RESETLOGS


    

STEP 6: Re-names of the data files names that have changed.



CREATE CONTROLFILE SET DATABASE "DUPDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/DUPDB/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/DUPDB/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/DUPDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/DUPDB/system01.dbf',
  '/u01/app/oracle/oradata/DUPDB/undotbs01.dbf',
  '/u01/app/oracle/oradata/DUPDB/sysaux01.dbf',
  '/u01/app/oracle/oradata/DUPDB/users01.dbf',
  '/u01/app/oracle/oradata/DUPDB/example01.dbf',
  '/u01/app/oracle/oradata/DUPDB/testtbs.dbf',
  '/u01/app/oracle/oradata/DUPDB/test02.dbf'
CHARACTER SET AL32UTF8
;

Save as db_create_controlfile.sql.

STEP 7: Create the bdump, udump and cdump directories

    cd $ORACLE_BASE/admin
    mkdir newlsq
    cd newlsq
    mkdir bdump
    mkdir udump
    mkdir cdump
    mkdir $ORACLE_BASE/oradata/dupdb
   


STEP 8: Copy-over the old init.ora file
 from the source db
 SQL>create pfile from spfile;

    scp $ORACLE_HOME/dbs/pfile newhost:$ORACLE_HOME/dbs/pfile
SETP 9: Edit init.ora file to reflect the duplicate database
 -controlfile path
 -dump file path
 -db_name

STEP 10: Start the target database with nomount option
 SQL>startup nomount pfile=' ';

     SQL>@db_create_controlfile.sql

STEP 11: Place the new database in archivelog mode.
STEP 12: Open the database with restlogs.


CREATING ORACLE PASSWORD File

d:\oracle\dpf\102\database\orapwd file=PWDDPF.ora password=dpfsap01 entries=20

How to calculate SAP/Oracle Performance parameter
http://gagankaushal.blogspot.nl/2009/03/how-to-calculate-saporacle-performance_22.html