Showing posts with label DATAGUARD. Show all posts
Showing posts with label DATAGUARD. Show all posts

How to force log switch on every 15 minutes (ARCHIVE_LAG_TARGET)

For performance reason It is preferred that redo switches should not be less than 15 minutes .So we must resize our redo logs optimally (for detail look Redo logs sizing advisory article


When we plan our redo logs for peak hours.while off peak-hours the redo switch does not occur for hours .This situation has some risk


  1. In a case of catastrophic hardware failure, we may lose our current redo log having a enormous long redo data.There is no way to recover transactions that are sitting in the current redo log .

  2. Standby and Primary database may not be close synchronous If you use MAXIMUM AVAILABILITY

so It will be better to switch log for each specified interval.ARCHIVE_LAG_TARGET parameter simply forces a log switch at a specified interval, and that can be very useful.

In order to set 15 minutes (900 seconds )

ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 900 SCOPE=BOTH;
No comments:

NON-AUTOMATIC STANDBY DATABASE CREATION

Contents:



As you may know, Oracle SE (Standard Edition) does not include the automatic DataGuard feature, but that doesn't mean that you can't use it!!!!
Here is a description about how to "manually" setup a Dataguard or Standby:

Steps
On the Primary database (it should be in ARCHIVE LOG MODE):
1. Modify the init.ora files on PROD defining location for archived redo log files on both primary and the standby server. Example:
create pfile from spfile;
*.log_archive_dest_1='LOCATION=/u02/oradata/ARCH'
*.log_archive_format='CCOM%t_%s_%r.dbf'
# LOG_ARCHIVE_DEST_2 is the service name of a remote database accessible via Net8
# The REOPEN parameter specifies that if the standby is unreachable for
# some reason, the arch process will attempt to reconnect in 60 seconds.
#*.log_archive_dest_2 = "service=STDBY mandatory reopen=60"
#*.log_archive_dest_state_2 = enable
# Tell Oracle both destinations MUST succeed in order to mark the online redo ready for reuse. 
#*.log_archive_min_succeed_dest = 2
Note that the LOG_ARCHIVE_START initialization parameter is obsolete in Oracle Database 10g.
Archiving is automatically enabled when you put your database into archive log mode.

2.  Ensure that your primary database is in archive log mode
archive log list;
create spfile from pfile;alter system archive log current;alter system switch logfile;
   (insures consistency in backup, standby controlfile, and logfiles)
If archiving hasn't been enabled on your primary database, run the following:shutdown immediate;create spfile from pfile;startup mount;alter database archivelog;alter database open;archive log list;
If archiving has been enabled, re-start the DB with the modifications that you performed to the init.ora on step 1shutdown immediate;create spfile from pfile;startup;


3 Place the primary database in FORCE LOGGING mode
alter database force logging;
4. Backup the datafiles of your primary database. Cold backup is easier but you can use hot backup too.
$ cp /u02/oradata/prod/* /oracle/BCKUP 
or
select 'cp ' || name || ' /oracle/BCKUP' from v$datafile
UNION
select 'cp ' || member || ' /oracle/BCKUP' from  v$logfile;
5. Create the standby controlfile
alter database create standby controlfile as '/oracle/BCKUP/standby.ctl';


ON THE STANDBY
6. Copy the datafiles, archived redo logs, the standby control file, init.ora and passwd file to the standby host (be sure to place copies of this file with the appropriate name in the equivalent locations on the standby where the production control files would have existed, if you had copied them. Otherwise you will need to use the parameters in the standby database to change the directories). Make sure you do not copy the production control files.
--Delete files
cd
cd FROMPROD
rm *
cd /u01/app/oracle/OraHome_1/dbs
rm alert_CCOM.log hc_CCOM.dat initCCOM.ora orapwCCOM
rm /u01/app/oracle/oradata/CCOM/*
rm /u02/oradata/CCOM/*
rm /u02/oradata/ARCH/*
rm /u01/app/oracle/admin/CCOM/bdump/*
rm /u01/app/oracle/admin/CCOM/cdump/*
rm /u01/app/oracle/admin/CCOM/udump/*

--START COPY PROCESS
cp /mnt/prod/BCKP/CC_dat_*.dbf  /u02/oradata/CCOM
cp /mnt/prod/BCKP/CC_indx_*.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/system01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/undotbs01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/sysaux01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/users01.dbf /u01/app/oracle/oradata/CCOM
cp /mnt/prod/BCKP/orapwCCOM /u01/app/oracle/OraHome_1/dbs
cp /mnt/prod/BCKP/initCCOM.ora /u01/app/oracle/OraHome_1/dbs
cp /mnt/prod/BCKP/CCOM1_*.dbf /u02/oradata/ARCH
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control01.ctl
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control02.ctl
cp /mnt/prod/BCKP/ctrlfile.bin /u01/app/oracle/oradata/CCOM/control03.ctl
7. Modify the init.ora file. I recommend keeping the parameters the same as configured on the primary database, with the following modifications to the secondary server init.ora file:
vi /u01/app/oracle/OraHome_1/dbs/initCCOM.ora# location where archive redo logs are being written in standby environment*.standby_archive_dest ='/u02/oradata/ARCH'
#*.fal_client=CCOM
#*.fal_server=CREDPROD

fal_client and fal_server are new parameters that enable archive-gap management. In this example, standby1 is the Oracle Net name of the standby database and primary1 is the Oracle Net name of the primary database. The fetch archive log (FAL) background processes reference these parameters to determine the location of the physical-standby and primary databases.

Convert all datafile pathnames that contain the names of your databases. As you can see this works ONLY if you have all your files in one place. If you have them in different directories (as OFA recommends), then you don't need use this parameter, you MUST rename each one of the files after mounting the database with the command ALTER DATABASE RENAME FILE '/oldplace/filename' TO '/newplace/filename' ;
db_file_name_convert = "/path/from/prod/db","/path/in/stdby/db"
log_file_name_convert = "/path/from/prod/db","/path/in/stdby/db"

8. Start the standby database in recovery mode.
sqlplus "/ as sysdba"
create spfile from pfile;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
exit
If necessay perform :  ALTER DATABASE RENAME FILE '/oldplace/filename' TO '/newplace/filenam' ;
9. Put the database into sustained recovery mode:
set echo on
sqlplus "/ as sysdba"
spool c:\scripts\logapply.log
ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE UNTIL CANCEL;
alter database recover cancel;
spool off
exit;
--Check Alert
cd /u01/app/oracle/admin/CCOM/bdump
tail -f alert_CCOM.log

At this point, you should manually transfer the arch redo log files from PROD to the STDBY.
If by any reason you want to open the STDBY database in read more you can perform the following:
shutdown immediate
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
Alter database open read only; 

How to open the standby database in Read only mode.
Disadvantage is that the (sustained) recovery has to be cancelled. If the database is opened in read only mode and users (including System and Sys) need sorting,  a locally managed sort tablespace should be their default sort tablespace. Steps to open the standby database in read only mode:
1- Create a locally managed temporary tablespace (if you created before the backup go to step #3)
The new locally managed temporary tablespace is created  on the primary database and is  propagated to the standby database by applying the logs or can be created before the backup for the standby is made.
        CREATE TEMPORARY TABLESPACE temp_local TEMPFILE
        '/unix1/app/oracle/oradata/v816/oradata/v816/temp_local01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;
2- Be sure to set all the users that are going to make sorts in the standby DB (including sys and system) to have their temporary tablespace set to this locally managed temporary tablespace (alter user .. temporary tablespace temp_local;) , do this on the primary DB and have this propagated to the standby by the archives. Check carefully the location of the directories in both places. "Send" the archive log:
ALTER SYSTEM ARCHIVE LOG CURRENT
3- Cancel the sustained recovery
    Alter database recover managed standby database cancel;
4- Open the database in readonly mode
    Alter database open read only;
5- Add a temporary file at the standby database to the locally managed temporary tablespace
V$datafile or sys.file$ on the primary database do not show the created datafile belonging to the locally managed tablespace temp_local. The added tempfile on the primary database doesn't change sys.file$. The redo is not generated and not propagated to the standby database while the entry in sys.ts$ is, but ther's no file.  Issue on the standby database:
    alter tablespace temp_local add tempfile 'path/temp_local01.dbf' size 100M;
6- Afterward restart sustained or manual recovery when needed when no active sessions are connected. If necessary open a new session as internal and issue shutdown immediate;


ACTIVATE STANDBY DATABASE as PRODUCTION:
1. To activate the standby, first try to archive your current production database logs
ALTER SYSTEM ARCHIVE LOG CURRENT
2. Then copy the most recent archive logs and current online redo log to the standby and apply them.
3. If your standby database has not timed out from your recovery, simply open a new SQL session into the standby database, by using a DBA account, and issue
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Locate the end of the standby database's alert.log, and identify the last archived log that was applied. Manually apply any remaining logs to the standby database:
ALTER DATABASE RECOVER [FROM 'pathname'] STANDBY DATABASE;

4. When you have applied the remaining logs to the standby database, stop the recovery by issuing
ALTER DATABASE RECOVER CANCEL;
shutdown immediate;
startup nomount;
alter database mount standby database;
5. Convert the standby database to a production environment:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
shutdown immediate
startup;
6. Then shutdown the standby to reset file headers and clear all buffers. You can't copy online redo logs from the primary to the standby. The standby is dismounted when activated. The standby bit in the controlfile is now set, so you can never go back - the standby is now your primary database. Since the redo log sequence was reset when the standby was opened, it is a good idea to take a full backup at this point.



Some Scripts

There are 4 files:
. generic.sh : you can duplicate this file in order to set up as many standby as needed, etc. It calls other scripts in order to:
. archivemove.sh : Get the archived redo logs to the standby host
. recover.sh : Synch the standby
. getrecid.sql : get the maximum progess point on the Manual Standby (used by archivemove.sh)

These scripts are used from the standby host. Remember to throughly check it before relying on it for production.

generic.sh
#!/bin/sh
 
# Be sure environment variable are set. If not, then it might fail!
# These environment variables are those for the Manual Physical Standby host
export ORACLE_HOME=/logical/oracle/Ora9i
export ORACLE_BASE=/logical/oracle
export ORACLE_STANDBY=tns_alias
export ORACLE_STANDBY_SYSDBA_PASS=change_on_install
export PATH=$ORACLE_HOME/bin:$PATH
export SOURCE_HOST=primary_host
export SOURCE_DRIVE=/primary/absolute/path/to/archived/redo/logs
export LOCAL_ARC_PARTH=/path/to/logical/archive/dest
 
# Check the date command usage depending on the platform
dateexec=`date "+%Y-%m-%d-%H-%M"`
 
# copy archived redo logs from main database
archivemove.sh > $dateexec.generic.log
 
# recover/sync the Manual Standby Database
recover.sh >> $dateexec.generic.log


archivemove.sh
#!/bin/sh
 
 
echo ----------------------------------------------------------------
echo ----------------------------------------------------------------
echo Get what log has last been applied to: $ORACLE_STANDBY
echo ----------------------------------------------------------------
sqlplus /nolog @getrecid.sql $ORACLE_STANDBY
echo ----------------------------------------------------------------
maxval=`tail -1 recid.log`
echo maxval=$maxval
rm recid.log
echo ----------------------------------------------------------------
 
# Check source drive to see what we're missing locally (source = primary)
for filename in `remsh $SOURCE_HOST 'ls $SOURCE_DRIVE' | sort`
do
 # get archive number. 
 # WARNING here I'm based on MY archived redo log name format! Put yours for the cut
 filename_parsed=`echo $filename | cut -c12-16`
 # Check if the number is after the last one applied to standby
  if [ $filename_parsed -gt $maxval ]
  then
   # grab it!
   echo $filename
   rcp $SOURCE_HOST:$SOURCE_DRIVE/$filename $LOCAL_ARC_PARTH
  fi
done
 
echo ----------------------------------------------------------------
echo Removing old files
echo ----------------------------------------------------------------
# Check in local directory
for filename in `ls $LOCAL_ARC_PATH | sort`
do
 # WARNING again about filename format
 filename_parsed=`echo $filename | cut -c12-16`
 # Check the arc number...
  if [ $filename_parsed -lt `expr $maxval - 15` ]
  then
   # Delete it!
   echo $filename
   rm -f $LOCAL_ARC_PATH/$filename 
  fi
done
 
echo ----------------------------------------------------------------
echo end archivemove.sh
echo ----------------------------------------------------------------


recover.sh
#!/bin/sh
 
echo ----------------------------------------------------------------
echo Traitement de la base $ORACLE_STANDBY
echo ----------------------------------------------------------------
sqlplus /nolog << EOF
connect sys/$ORACLE_STANDBY_SYSDBA_PASS@$ORACLE_STANDBY as sysdba
SELECT MAX(RECID) "Log id now" FROM V\$LOG_HISTORY;
RECOVER AUTOMATIC DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
CANCEL
SELECT MAX(RECID) "Log id after recover" FROM V\$LOG_HISTORY;
exit;
EOF
echo ----------------------------------------------------------------
echo End of recovery process
echo ----------------------------------------------------------------


getrecid.sql
connect sys/change_on_install@&1 as sysdba
SET HEAD OFF FEEDBACK OFF VERIFY OFF TERMOUT ON ECHO OFF TRIMSPOOL ON SERVEROUTPUT OFF
SPOOL recid.log
SELECT MAX(RECID) FROM V$LOG_HISTORY;
SPOOL OFF
exit


Standby Database Startup Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.
start_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE    :  start_db_recover_mode.ksh
# | AUTHOR  :  Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC.   :  This script is responsible for starting the Oracle standby database
# |            in managed recovery mode. The major steps in this script include 
# |            mounting the database in standby mode, copy all archived redo log
# |            files from the primary database server (using rcp), applying all
# |            archived redo logs from the primary database, and finally putting
# |            the database in managed recovery mode - automatically accepting and
# |            applying archived redo logs from the primary database.
# |
# | NOTE    :  Since this script uses one of the r* commands, namely rcp, it
# |            assumes that a valid .rhosts (or /etc/hosts.equiv) exists on the
# |            primary host to ensure that the standby host can login as the 
# |            "oracle" user account.
# |
# | SYNTAX  :  nohup start_db_recover_mode.ksh ORACLE_SID
# |                                            PRIMARY_DB_SERVER
# |                                            PRIMARY_ARCH_LOG_DEST
# |                                            STANDBY_ARCH_LOG_DEST
# |
# | EXAMPLE
# |    CALL :  nohup start_db_recover_mode.ksh -
# |                ORA817 -
# |                linux3 -
# |                /u06/app/oradata/ORA817/archive -
# |                /u06/app/oradata/ORA817/archive > -
# |                /u01/app/oracle/common/log/start_db_recover_mode.log 2>&1 &
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 4 )); then
    echo " "
    echo "Usage: $SHORT_NAME ORACLE_SID PRIMARY_DB_SERVER PRIMARY_ARCH_LOG_DEST STANDBY_ARCH_LOG_DEST"
    echo " "
    echo "  Invalid number of arguments."
    echo " "
    exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

PRIMARY_DB_SERVER=$2
export PRIMARY_DB_SERVER

PRIMARY_ARCH_LOG_DEST=$3
export PRIMARY_ARCH_LOG_DEST

STANDBY_ARCH_LOG_DEST=$4
export STANDBY_ARCH_LOG_DEST

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +-------------------------------------+
# | CHECK FOR DATABASE INSTANCE OFFLINE |
# +-------------------------------------+

echo " "
echo "Checking for database instance offline..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? != 1 ]]; then
    echo "ERROR - Database Instance is up. Is this thing already in Managed Recovery Mode?"
    echo "        Process listing is to follow..."
    ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
    echo "        Exiting script."
    echo " "
    exit
fi

# +---------------------------------------------------------+
# | TRY TO GET ANY ARCHIVED LOG FILES FROM PRIMARY DATABASE |
# +---------------------------------------------------------+

echo " "
echo "Attempting to get any archived log files from ${PRIMARY_DB_SERVER}:${LOG_ARCH_DEST} ..."
echo " "

rcp ${PRIMARY_DB_SERVER}:${PRIMARY_ARCH_LOG_DEST}/* $STANDBY_ARCH_LOG_DEST

# +---------------------------------------------+
# | STARTUP/MOUNT STANDBY DB & RECOVER ALL LOGS |
# +---------------------------------------------+

echo " "
echo "Startup/Mount Database and recover all archived redo logs..."
echo " "

sqlplus /nolog << END
connect / as sysdba
startup nomount
alter database mount standby database;
recover standby database until cancel;
auto
exit;
END

# +--------------------------------------------+
# | STARTUP DB IN MANGED STANDBY RECOVERY MODE |
# +--------------------------------------------+

echo " "
echo "Startup Database in Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database;
connect / as sysdba
shutdown immediate
exit;
END


Standby Database Shutdown Script

A call to the following script can be added to your database startup/shutdown script, (i.e. /etc/init.d/dbora), on the database server hosting the Oracle Standby Database.
stop_db_recover_mode.ksh
#!/bin/ksh

# +--------------------------------------------------------------------------------
# | FILE    :  stop_db_recover_mode.ksh
# | AUTHOR  :  Jeffrey Hunter, Sr. Database Administrator
# |
# | DESC.   :  This script is responsible for stopping an Oracle standby database
# |            that is in managed recovery mode. The major steps in this script
# |            include checking that the database instance is indeed running and
# |            then canceling managed recovery mode for the given database.
# |
# | SYNTAX  :  stop_db_recover_mode.ksh ORACLE_SID
# |
# | EXAMPLE
# |    CALL :  stop_db_recover_mode.ksh ORA817 > /u01/app/oracle/common/log/stop_db_recover_mode.log 2>&1
# +--------------------------------------------------------------------------------

SHORT_NAME=`basename $0`

# +------------------------------+
# | VALIDATE INCOMING PARAMETERS |
# +------------------------------+

if (( $# != 1 )); then
    echo " "
    echo "Usage: $SHORT_NAME ORACLE_SID"
    echo " "
    echo "  Invalid number of arguments."
    echo " "
    exit 1
fi

# +----------------------+
# | SET GLOBAL VARIABLES |
# +----------------------+

echo " "
echo "Setting Global Variables..."
echo " "

ORACLE_SID=$1
export ORACLE_SID

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/8.1.7
export ORACLE_HOME

LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH

ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data
echo ORA_NLS33

ORACLE_DOC=${ORACLE_HOME}/doc
echo ORACLE_DOC

# +-----------------------------------------+
# | DISPLAYING ORACLE ENVIRONMENT VARIABLES |
# +-----------------------------------------+

echo " "
echo "Displaying Oracle Environment Variables..."
echo " "

echo " "
echo " >>> set | grep ^ORA"
echo " "
set | grep ^ORA
echo " "

# +------------------------------------+
# | CHECK FOR DATABASE INSTANCE ONLINE |
# +------------------------------------+

echo " "
echo "Checking for database instance online..."
echo " "

STATUS=`ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_`
if [[ $? == 1 ]]; then
  echo "ERROR - database not in recovery mode. Did someone already shutdown the DB Instance?"
  echo "        Process listing is to follow..."
  ps -fu oracle | grep -v grep | grep $ORACLE_SID | grep ora_
  echo "        Exiting script."
  echo " "
  exit
fi

# +---------------------------------------------+
# | TAKE DB OUT OF MANAGED RECOVERY MODE.       |
# +---------------------------------------------+

echo " "
echo "Take Database out of Managed Standby Recovery Mode..."
echo " "

sqlplus /nolog << END
connect / as sysdba
recover managed standby database cancel;
exit;
END


Resolving Errors
If you get an error because the Standby DB can't recognized a database file name that was added or dropped perform the following:
alter database recover automatic standby database until cancel;
alter database create datafile '/u01/app/oracle/OraHome_1/dbs/UNNAMED00002' as '/u01/app/oracle/oradata/CCOM/undotbs01.dbf';
No comments:

Manual Standby on Standard Edition


Pre-Installation Check


Verify the primary database is in ARCHIVELOG mode


SQL> select name from v$database;


NAME
---------
DBDPROD


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14487
Next log sequence to archive   14489
Current log sequence           14489


Add at least one LOG_ARCHIVE_DEST and LOG_ARCHIVE_START to INIT FILE


You can also use Flash Recovery Area if that is already configured.


SQL > ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/DG/archive/' SCOPE=spfile;


SQL> ALTER SYSTEM ARCHIVE LOG START;


System altered.


Note: Restart Database to verify new settings are in effect


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20


SQL> SELECT NAME FROM V$DATAFILE;


NAME
--------------------------------------------------------------------------------
/u02/oradata/DBDPROD/SYSTEM01.DBF
/u02/oradata/DBDPROD/SYSAUX01.DBF
/u02/oradata/DBDPROD/UNDOTBS01.DBF
/u02/oradata/DBDPROD/USERS01.DBF
/u02/oradata/DBDPROD/XXXXX_UOFR.DBF
/u02/oradata/DBDPROD/XXXXX_01.DBF
/u02/oradata/DBDPROD/XXXXX_UOFR_02.DBF
/u02/oradata/DBDPROD/AUDIT_AUX01.DBF
/u02/oradata/DBDPROD/audit.dbf
/u02/oradata/DBDPROD/statspack_data01.dbf
/u02/oradata/DBDPROD/XXXXX_EXXS_01.DBF


NAME
--------------------------------------------------------------------------------
/u02/oradata/TST11242/XXXXX_XADR_LOB_01.DBF
/u02/oradata/DBDPROD/XXXXX_XADR_01.DBF
/u02/oradata/DBDPROD/statspack_data02.dbf


SQL> sho parameter DB_RECOVERY_FILE_DEST


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u05/fast_recovery_area/
db_recovery_file_dest_size           big integer 80G
SQL>


SQL> select name from v$controlfile;


NAME
--------------------------------------------------------------------------------
/u02/oradata/DBDPROD/control01.ctl
/u02/oradata/DBDPROD/flash_recovery_area/control02.ctl


SQL> show parameter audit


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string          /u01/app/oracle/admin/DBDPROD/adump
audit_sys_operations          boolean     FALSE
audit_syslog_level                string
audit_trail                               string          DB
SQL>


Create Standby INIT FILE from SPFILE for Standby Database


SQL>  create pfile='/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/initDBDPROD.ora'  from spfile;


Create Standby CONTROL FILE for Standby Database


SQL>  ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/DBDPRODstdby.ctl';


Create Hot Backup of the Primary Database using RMAN


[oracle@xxxx-DB-01 DBDPROD]$ rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 5 11:17:50 2015


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: DBDPROD (DBID=3489014338)


RMAN
run
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
allocate channel c1 DEVICE TYPE DISK MAXPIECESIZE = 5G;
backup full format "/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/%d_DB_%u_%s_%p" database ;
copy current controlfile to "/u05/fast_recovery_area/DBDPROD/STANDBY_FILES/STDBY_control.ctl";
}


Starting backup at 23-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/DG/system01.dbf
input datafile file number=00002 name=/u01/oradata/DG/sysaux01.dbf
input datafile file number=00003 name=/u01/oradata/DG/undotbs01.dbf
input datafile file number=00004 name=/u01/oradata/DG/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-APR-14
channel ORA_DISK_1: finished piece 1 at 23-APR-14
piece handle=/u01/orabackupDG/DG_data_t845636933_s1_p1 tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-APR-14
channel ORA_DISK_1: finished piece 1 at 23-APR-14
piece handle=/u01/orabackupDG/DG_data_t845636978_s2_p1 tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-APR-14


Starting backup at 23-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/orabackup/controlfile_230414.ctl tag=TAG20140423T110941 RECID=2 STAMP=845636981
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-APR-14


RMAN> exit


Prepare the failover server (Standby Database) for restore
Create the directories that are needed for successfully restoring the database backup


****LOCATIONS NEED TO BE CREATED ON STANDBY ***


mkdir -p /u02/oradata/DBDPROD/
mkdir -p /u01/app/oracle/admin/DBDPROD/adump
mkdir -p /u01/app/oracle/admin/DBDPROD/dpdump
mkdir -p /u01/app/oracle/admin/DBDPROD/pfile
mkdir –p /u03/fast_recovery_area/DBDPROD/archivelog


Copy files to the failover server (Standby Database) for restore
At this point, you want to copy everything over to the standby server including RMAN backup, standby controlfile & Config (INIT) file


INIT FILE
[oracle@FCIMHCQADB1 ~]$ cp initDBDPROD.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/


STANDBY CONTROL FILE
[oracle@FCIMHCQADB1 STDBY_files]$ cp DBDPRODstdby.ctl /u02/oradata/DBDPROD/control01.ctl
[oracle@FCIMHCQADB1 STDBY_files]$ cp DBDPRODstdby.ctl /u02/oradata/DBDPROD/flash_recovery_area/control02.ctl

Startup Standby Database in NOMONT MODE


[oracle@ dbs]$ export ORACLE_SID=DBDPROD
[oracle@ dbs]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 29 16:17:52 2015


Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to an idle instance.


SQL> startup nomount;
ORACLE instance started.


Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            1677725072 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16904192 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;


Database altered.


SQL>
Restore Standby Database from RMAN backup of Primary Database


[oracle@ dbs]$ rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 29 16:22:58 2015


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: DBDPROD (DBID=3489014338, not open)


RMAN> catalog start with '/u03/fast_recovery_area/DBDPROD/STDBY_files';


Starting implicit crosscheck backup at 29-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 29-OCT-15


Starting implicit crosscheck copy at 29-OCT-15
using channel ORA_DISK_1
Crosschecked 14 objects
Finished implicit crosscheck copy at 29-OCT-15


searching for all files in the recovery area
cataloging files...
no files cataloged


searching for all files that match the pattern /u03/fast_recovery_area/DBDPROD/STDBY_files


List of Files Unknown to the Database
=====================================
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/STDBY_controlfile.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPRODstdby.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1


Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/STDBY_controlfile.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPRODstdby.ctl
File Name: /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1


RMAN>
RMAN> restore database;


Starting restore at 29-OCT-15
using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/DBDPROD/SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/DBDPROD/SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/DBDPROD/UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/DBDPROD/USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to /u02/oradata/DBDPROD/XXXXX_UOFR.DBF
channel ORA_DISK_1: restoring datafile 00006 to /u02/oradata/DBDPROD/XXXXX_01.DBF
channel ORA_DISK_1: restoring datafile 00007 to /u02/oradata/DBDPROD/XXXXX_UOFR_02.DBF
channel ORA_DISK_1: restoring datafile 00008 to /u02/oradata/DBDPROD/AUDIT_AUX01.DBF
channel ORA_DISK_1: restoring datafile 00009 to /u02/oradata/DBDPROD/audit.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u02/oradata/DBDPROD/statspack_data01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u02/oradata/DBDPROD/XXXXX_EITS_01.DBF
channel ORA_DISK_1: restoring datafile 00012 to /u02/oradata/TST11242/XXXXX_XADR_LOB_01.DBF
channel ORA_DISK_1: restoring datafile 00013 to /u02/oradata/DBDPROD/XXXXX_XADR_01.DBF
channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/DBDPROD/statspack_data02.dbf
channel ORA_DISK_1: reading from backup piece /u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1
channel ORA_DISK_1: piece handle=/u03/fast_recovery_area/DBDPROD/STDBY_files/DBDPROD_DB_a1qkp7e8_3393_1 tag=TAG20151027T163936
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:14:05
Finished restore at 29-OCT-15


RMAN>
Recovery Manager complete.


Create Script to ARCHIVELOGS / APPLY LOGS / RECOVER STANDBY / CLEAN ARCHIVES


******************************************************************************************
apply_archive_DBDPROD.sh
# !/bin/bash
# Declare your environment variables


#### Archive logs from production are rsync`d to standby ######


/home/oracle/scripts/mv_archive_PRDSTDBY.sh


#### Setup environment and recover database #####
ORACLE_SID=DBDPROD
export ORACLE_SID
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
export PATH=$PATH:$ORACLE_HOME/bin
cd /u01/app/oracle/product/11.2.0/dbhome_1/bin
./sqlplus sys/xxxxxx as sysdba @/home/oracle/scripts/recover_DBDPROD_STDBY.sh
rm /home/oracle/DBDPROD_stdby.lock
find /u03/fast_recovery_area/DBDPROD/archivelog/* -type d -mtime +3 -exec rm -rf {} \;


*******************************************************************************************


mv_archive_PRDSTDBY.sh
#!/bin/bash
if [ -e /home/oracle/DBDPROD_stdby.lock ]
then
 echo "Rsync job already running...exiting"
 exit
fi


touch /home/oracle/DBDPROD_stdby.lock


rsync -e ssh -avzh oracle@192.168.1.205:/u05/fast_recovery_area/DBDPROD/archivelog/* /u03/fast_recovery_area/DBDPROD/archivelog/.


*******************************************************************************************


recover_DBDPROD_STDBY.sh
set echo on
spool /home/oracle/DBDPROD_STDBY_apply.log
RECOVER AUTOMATIC STANDBY DATABASE until cancel;
alter DATABASE OPEN READ ONLY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
spool off
exit;


*******************************************************************************************


Verify REDO LOGS transfer to Failover Server (Standby Database)


Perform switch logfile to generate REDO LOGS and then run Shell Script (updateDG.sh) to copy Archive Logs to Standby Database


On Primary Database


SQL> alter system switch logfile;


System altered.


SQL> /


System altered.


SQL> /
System altered.


SQL> /


System altered.


SQL> /


System altered.


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oradata/DG/archive/
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
SQL> exit

Recover Standby Database by applying all REDO LOGS from Primary Database


SQL> RECOVER AUTOMATIC STANDBY DATABASE ;
ORA-00279: change 573811485 generated at 11/06/2015 09:45:09 needed for thread
1
ORA-00289: suggestion :
/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc
ORA-00280: change 573811485 for thread 1 is in sequence #14930
ORA-00278: log file
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u03/fast_recovery_area/DBDPROD/archivelog/2015_11_06/o1_mf_1_14930_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter DATABASE OPEN READ ONLY;


Database altered.


SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.


Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            1660947856 bytes
Database Buffers         1526726656 bytes
Redo Buffers               16904192 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;


Database altered.


SQL> spool off
SQL> exit;
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production



Resolving Errors
If you get an error because the Standby DB can't recognized a database file name that was added or dropped perform the following:
alter database recover automatic standby database until cancel;
alter database create datafile '/u01/app/oracle/OraHome_1/dbs/UNNAMED00002' as '/u01/app/oracle/oradata/CCOM/undotbs01.dbf';
No comments:
Subscribe to: Posts (Atom)