dg_common.ksh is a common DataGuard library designed to allow DataGuard functions to be performed via many functions in the library
</pre>
#!/bin/ksh
############################################################################
# Script Name..: dg_common.ksh
# Description..: Common DataGuard functions
# Author.......: Michael Culp
# Date.........: 04/23/2010
# Version......: .65
# Modified By..: Michael Culp
# Date Modified:
# Comments.....: Change this library to make mods to all functions
# Script type..: function library
# Function list:
############################################################################
############################################################################
# This is a common funtion library that can be included in a script
# via . dg_common.ksh this will allow access to the functions
# db_conn - this provides the connectivity to the database
############################################################################
############################################################
# DataGuard Functions
# Use the following functions to install and maintain dataguard
############################################################
############################################################
# dg_init - Initialize variables or other functions
# dg_dbrd - Shows a dashboard
# dg_inst_order - Displays order of install of scripts
# dg_asm_dgs - Shows the ASM diskgroups to show the space avail and diskgroups that exist
# dg_cr_log - create dbfs install log
# dg_pre_inst - Check for OS params
# dg_srv_stat - Status of all services configured on the DB
# dg_stop_allsvc - Stop all services configured on the DB
# dg_start_allsvc - Start all services configured on the DB
#
# dbfs_chk_db_usr -
# dbfs_cr_tblspc - Create DBFS tablespace
# dbfs_drp_tblspc - Drop DBFS tablespace
# dbfs_cr_usr - Create DBFS user
# dbfs_drp_usr - Drop DBFS user
# dbfs_cr_objs - Create DBFS objects
# dbfs_cr_mnt_pnt - Create the DBFS mountpoint
# dbfs_test_access - Test access to the DBFS directories
# dbfs_inst_qa - QA the install
############################################################
# Set any variables needed here without calling init function
# Setting log directory for dg_common library
# Will change later to better strategy
export DGC_LOGDIR=/oracle/admin/scripts/dgcommon/LOGS
dg_init()
{
echo
echo "DG Init"
echo "Ver. 2.3 11/02/2018 MCulp"
echo "Include any variables here for use in scripts...."
echo
}
dg_dbrd()
{
echo
echo "DG Dashboard"
echo "Use this for displaying parameters or variables for functions...."
echo
}
dg_sho_fnc()
#########################################################################
# Shows the functions in this common library
#########################################################################
{
echo
echo "Show the functions in dg_common.ksh ..."
echo
cat dg_common.ksh|grep '()'
}
dg_sho_cmds()
#########################################################################
# Show the commands available
#########################################################################
{
clear
echo
echo " "
echo
echo "....Aliases that can executed directly on the command line...."
echo "=============================================================="
echo
echo
echo
echo "....Install scripts for installation of DataGuard............."
echo "=============================================================="
echo
echo "=============================================================="
}
dg_sho_so_order()
{
echo
echo "Order for the execution of Switchover...."
echo
echo
}
dg_sho_fo_order()
{
echo
echo "Order for the execution of Failover......"
echo
echo
}
dg_sho_role()
########################################################
# Show the current database role
########################################################
{
echo
echo "Shows the database role of the current database...."
echo
# echo "test"
sqlplus -s "/ as sysdba" <<EOF
select name, database_role from v\$database;
EOF
echo
echo
echo
#-- db_role=`sqlplus -s "/ as sysdba" <<EOF
#set head off feed off pages 0 trimspool on
#select database_role from v$database;
#exit;
#EOF`
#echo $db_role
}
dg_parm_chk()
############################################################################
# Description..: Parameter check for the primary server
############################################################################
# Script Name..:
# Description..: Parameter check for the primary server
# Author.......: M Culp
# Date.........: 05/14/2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################
{
sqlplus -s "/ as sysdba" <<EOF
spool dg_info.txt
spool off
EOF
#################################################################################
echo
echo "This is a parameter check for the primary server....."
echo
sqlplus -s "/ as sysdba" <<EOF
set head on
set pagesize 66
set linesize 500
col value for a40
col name for a40
-- db_name and db_unique_name must be the same for the primary server
select name, value from v\$parameter
where name in ('db_name',
'db_unique_name',
'log_archive_config',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server',
'fal_client',
'db_file_name_convert',
'log_file_name_convert',
'standby_file_management');
EOF
echo
sqlplus -s "/ as sysdba" <<EOF
-- Log files should be large and should be one more group than what is there
set pages 0 lines 300 trims on head off feed off ver off
select '# ---------------------------'||chr(10)||
'# -- Log File Information --'||chr(10)||
'# ---------------------------'||chr(10)||
'Log File Size '||max(bytes)||chr(10)||
'No of Logs '||count(1) from v\$log;
EOF
echo
sqlplus -s "/ as sysdba" <<EOF
set pages 66
set lines 120
column member format a65
select thread#
, group#
, sequence#
, members
, status
, archived
from v\$log;
select group#
, status
, type
, member
, is_recovery_dest_file
from v\$logfile order by group#;
EOF
sqlplus -s "/ as sysdba" <<EOF
set pages 0 lines 300 trims on head off feed off ver off
select '# ---------------------------'||chr(10)||
'# -- Instance Information --'||chr(10)||
'# ---------------------------'||chr(10)||
'Host Name: '||host_name||chr(10)||
'Instance Name: '||instance_name||chr(10)||
'Version: '||version||chr(10)||
'Startup Time: '||to_char(startup_time, 'DD-MON-RR HH24:MI:SS')||chr(10)||
'Instance Role: '||instance_role||chr(10)||
'Blocked: ' ||blocked
from gv\$instance
order by instance_name;
select '# ---------------------------'||chr(10)||
'# -- Database Information --'||chr(10)||
'# ---------------------------'||chr(10)||
'Name: '||name ||chr(10)||
'Database Role: '||database_role ||chr(10)||
'Created: '||created ||chr(10)||
'Log Mode: '||log_mode ||chr(10)||
'Open Mode: '||open_mode ||chr(10)||
'Protection Mode: '||protection_mode ||chr(10)||
'Protection Level: '||protection_level ||chr(10)||
'Current SCN: '||current_scn ||chr(10)||
'Flashback on: '||flashback_on||chr(10)||
'Open Mode: '||open_mode ||chr(10)||
-- 'Primary DB Unique Name: '||primary_db_unique_name ||chr(10)||
'DB Unique Name: '||db_unique_name ||chr(10)||
'Archivelog Change#: '||archivelog_change# ||chr(10)||
-- 'Archivelog Compression: '||archivelog_compression ||chr(10)||
'Switchover Status: '||switchover_status ||chr(10)||
'Remote Arachive: '||remote_archive||chr(10)||
'Supplemental Log PK: '||supplemental_log_data_pk||' - '||
'Supplemental Log UI: '||supplemental_log_data_ui||chr(10)||
'Data Guard Broker: ' ||dataguard_broker||chr(10)||
'Force Logging: '||force_logging
from v\$database;
EOF
}
dg_sw_stat_pri()
{
clear
echo
echo "*****************************************************"
echo "Check the switchover status.........................."
echo "*****************************************************"
echo
echo "This can be run on the Primary or Standby database..."
echo
echo "If the status is NOT ALLOWED that means this is the standby"
echo "or if this is the PRIMARY it has no standbys"
echo
echo "For UNRESOLVABLE GAP....."
echo
sqlplus -s "/ as sysdba" <<EOF
select name,
database_role,
switchover_status,
open_mode
from v\$database;
EOF
dg_sho_curr_scn
}
dg_sho_sw_stat()
{
dg_sw_stat_pri
}
dg_sho_curr_scn()
#############################################################################################
# Show the current SCN for this database
#############################################################################################
{
echo "This is the current SCN from this database"
sqlplus -s "/ as sysdba" <<EOF
select current_scn
from v\$database;
EOF
}
dg_parm_chg()
#############################################################################################
# Primary DB =
# Standby DB =
# Change the parameters in the lines below
#############################################################################################
{
sqlplus -s "/ as sysdba" <<EOF
spool
alter database force logging;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(CIMETP01,CIMETG01)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIMETP01' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=CIMETG01 ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIMETG01' scope=both sid='*';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';
alter system set log_archive_max_processes=8 scope=both sid='*';
alter system set fal_server=CIMETG01 scope=both sid='*';
alter system set db_file_name_convert='CIMETG01','CIMETP01' scope=spfile sid='*';
alter system set log_file_name_convert='CIMETG01','CIMETP01' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
EOF
}
dg_set_role()
########################################################################
#
########################################################################
{
echo
echo "Set the variable db_role for role value....."
echo
# db_role=`${ORACLE_HOME}/bin/sqlplus "/ as sysdba"<<EOF
db_role=`sqlplus -s "/ as sysdba"<<EOF
set head off feed off pages 0 trimspool on
select database_role from v\\$database;
exit;
EOF`
echo
echo "Show db_role db_role ..."
echo $db_role
echo
echo "Show db_role test_mrc..."
echo
test_mrc=`sqlplus -s "/ as sysdba" <<EOF
set head off
select trim(both ' ' from database_role) from v\\$database;
EOF`
echo $test_mrc
#if [ "$test_mrc" -eq "" ]; then
# echo "Database is not primary or standby"
#else
# echo "Database is "$test_mrc
#fi
########################################################################
# sqlplus -s "/ as sysdba" <<EOF
# select name, database_role, switchover_status from v\$database;
# EOF
########################################################################
}
dg_set_so_stat()
########################################################################
# Set a variable for switchover status
########################################################################
{
echo
echo "Set the variable so_stat for switchover status value....."
echo
# so_stat=`${ORACLE_HOME}/bin/sqlplus "/ as sysdba"<<EOF
so_stat=`sqlplus -s "/ as sysdba"<<EOF
set head off feed off pages 0 trimspool on
select switchover_status from v\\$database;
exit;
EOF`
# echo $so_stat
}
dg_mng_stdby_finish()
########################################################################
# Only for ORA-16139 error
#
########################################################################
{
dg_set_role
# echo "After dg_set_role..."
# echo $test_mrc
# echo
# if [ "$test_mrc" -eq "PRIMARY" ]; then
if [ "$db_role" -eq "PRIMARY" ]; then
echo
echo "Database is primary"
echo "Execution of media recovery will not occur...."
echo
else
echo "Database is standby or nothing"$test_mrc
echo
echo "This should only be run if the ORA-16139: media recovery required "
echo "occurs when running alter switchover..."
echo
# Next three lines will fix the issue with ORA-16139
#############################################################
sqlplus -s "/ as sysdba" <<EOF
recover managed standby database finish;
EOF
fi
}
dg_mrplog_apply_on()
############################################################################
# Script Name..: dg_mrplog_apply_on_fn.ksh
# Description..: Turns MRP on, checks the db role first
# Author.......:
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################
{
clear
echo
echo
echo
echo "Turns on MRP process after a switchover and successful startup ...."
echo
echo
echo
dg_set_role
if [ "$db_role" = "PRIMARY" ]; then
clear
echo
echo "This is not the STANDBY database."
echo "MRP should only run on STANDBY db"
echo
else
clear
echo
echo
echo "Attempting to start MRP on this database ....."
echo
echo
sqlplus -s "/ as sysdba" <<EOF
select database name,
open_mode
from v$database;
-- alter database recover managed standby database using current logfile disconnect from session;
EOF
fi
}
dg_mrp_on()
{
dg_log_apply_on
}
dg_arch_chk()
############################################################
# This function knows if it is running on PRI or STDBY
############################################################
{
dg_set_role
if [ "$db_role" != "PRIMARY" ]; then
clear
echo
echo "Executing the STANDBY archive log check ......"
echo "This is the STANDBY database."
echo
dg_arch_chk_stdby
else
clear
echo
echo "Executing the PRIMARY archive log check ......"
echo "This is the PRIMARY database."
echo
dg_arch_chk_pri
fi
}
dg_arch_chk_pri()
############################################################################
# Script Name..: dg_arch_chk_pri.ksh
# Description..:
# Author.......:
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
{
dg_set_role
if [ "$db_role" != "PRIMARY" ]; then
clear
echo
echo "Archive log check (primary)......"
echo "This is not the PRIMARY database."
echo
else
clear
echo
echo "Archive log check (primary)......"
echo "This shows the archive log status on the primary db...."
echo
sqlplus -s "/ as sysdba" <<EOF
PROMPT "PRIMARY Archive Log Report"
-- select "PRIMARY Archive Log Report" from dual;
select thread#
, max(sequence#) "Last Standby Seq Received"
from v\$archived_log val,
v\$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
EOF
fi
}
dg_arch_chk_stdby()
############################################################################
# Query specific to STANDBY database
############################################################################
{
dg_set_role
if [ "$db_role" = "PRIMARY" ]; then
clear
echo
echo "Archive log check (Standby)......"
echo "This is not the STANDBY database."
echo
else
clear
echo
echo "Archive log check (Standby)......"
echo "This shows the archive log status (applied) on the standby db ...."
echo
sqlplus -s "/ as sysdba" <<EOF
PROMPT "STANDBY Archive Log Report"
select thread#,
max(sequence#) "Last Standby Seq Applied"
from v\$archived_log val,
v\$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# order by 1;
EOF
fi
}
dg_cr_pfile_pri()
###############################################################################
# Create a pfile from the current spfile
# Do this before any failover !!!!!!!!!!!!!!!!!!!!!
###############################################################################
{
###############################################################################
# Primary DB =
# Standby DB =
###############################################################################
echo
sqlplus -s "/ as sysdba" <<EOF
create pfile='/oracle/admin/primaryinitpfile.ora' from spfile;
EOF
}
dg_chk_stdby()
###########################################################################
# Check destination
###########################################################################
{
sqlplus -s "/ as sysdba" <<EOF
set serveroutput on size 1000000
set lines 132
col error format a32
col destination format a35
set ver off head off feed off pages 0
DECLARE
pad10 CHAR(10) := ' ';
CURSOR c1 is
select s.db_unique_name,
s.database_mode,
s.dest_id id,
s.status stats,
s.recovery_mode,
s.protection_mode,
s.standby_logfile_count,
s.standby_logfile_active,
s.archived_thread#,
s.archived_seq#,
s.applied_thread#,
s.applied_seq#,
d.status,
d.destination,
d.archiver,
d.transmit_mode,
d.affirm,
d.async_blocks,
d.net_timeout,
d.delay_mins,
d.reopen_secs,
d.register,
d.binding,
-- d.compression,
d.error err
from v\$archive_dest_status s,
v\$archive_dest d
where d.dest_id=s.dest_id
and s.db_unique_name <> 'NONE'
and d.destination is not null;
BEGIN
dbms_output.put_line('---------------------------------------------------------------------------------------');
FOR r1 IN c1 LOOP
dbms_output.put_line('Dest ID: '||r1.id||pad10||'Status: '||r1.stats);
dbms_output.put_line('DB Name: '||r1.db_unique_name||pad10||'DB Mode: '||r1.database_mode);
dbms_output.put_line('Recovery Mode: '||r1.recovery_mode);
dbms_output.put_line('Protection Mode: '||r1.protection_mode);
dbms_output.put_line('SRL Count: '||r1.standby_logfile_count||pad10||'SRL Active: '||r1.standby_logfile_active);
dbms_output.put_line('Archived Thread#: '||r1.archived_thread#||pad10||'Archived Seq#: '||r1.archived_seq#);
dbms_output.put_line('Applied Thread#: '||r1.applied_thread#||pad10||'Applied Seq#: '||r1.applied_seq#);
dbms_output.put_line('Destination: '||r1.destination);
dbms_output.put_line('Archiver: '||r1.archiver);
dbms_output.put_line('Transmit Mode: '||r1.transmit_mode);
dbms_output.put_line('Affirm: '||r1.affirm);
dbms_output.put_line('Asynchronous Blocks: '||r1.async_blocks);
dbms_output.put_line('Net Timeout: '||r1.net_timeout);
dbms_output.put_line('Delay (Mins): '||r1.delay_mins);
dbms_output.put_line('Reopen (Secs): '||r1.reopen_secs);
dbms_output.put_line('Register: '||r1.register);
dbms_output.put_line('Binding: '||r1.binding);
-- dbms_output.put_line('Compression: '||r1.compression);
dbms_output.put_line('Error: '||r1.err);
dbms_output.put_line('---------------------------------------------------------------------------------------');
END LOOP;
END;
/
EOF
}
dg_mngd_stdby()
####################################################
# This gets executed only on one node standby
####################################################
{
sqlplus -s "/ as sysdba" <<EOF
-- alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;
EOF
}
dg_startup_nomount()
{
sqlplus -s "/ as sysdba" <<EOF
startup nomount pfile='/oracle/admin/DBFSXB01/pfile/initstdby.ora'
EOF
}
dg_dup_act_db()
{
rman target sys/ora98dba@DBFSXD01 auxiliary sys/ora98dba@DBFSXB01 <<EOF
duplicate target database for standby from active database;
EOF
}
dg_log_apply_rate()
############################################################################
# Script Name..: dg_log_apply_rate
# Description..: This shows the log apply rate on standby side
# Author.......:
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
############################################################################
{
dg_set_role
if [ "$db_role" = "PRIMARY" ]; then
clear
echo
echo "This is not the STANDBY database."
echo
else
clear
echo
echo "This shows the archive log apply rate on a standby db...."
echo
sqlplus -s "/ as sysdba" <<EOF
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values"
from v\$recovery_progress
where start_time=(select max(start_time)
from v\$recovery_progress);
EOF
fi
}
rman_stat()
{
rman target sys/ora98dba@DBFSXD01 <<EOF
list backup;
show all;
EOF
}
dg_chk_prc_stdby()
#############################################################
# Check to see if the standby MRP process is running
#############################################################
{
echo
echo "Checking MRP process for standby....."
echo "This should only be run on standby database mode..."
echo
sqlplus -s "/ as sysdba" <<EOF
select process from v\$managed_standby where process like 'MRP%';
EOF
}
dg_sd_stdby()
#########################################################
# DataGuard Shut Down Standby
# Shutdown standby instances all but first instance
# TX DEV primary database DBFSXD01
# VA DEV standby for primary DBFSXD01= DBFSXB01
#
# TX DEV standby for VA DBFSXD02 = DBFSXB02
# VA DEV primary database DBFSXD02
#########################################################
{
# Compare DB_NAME to the passed database to be sure they are
# the same value, if not drop out
# We do not care what database is passed as you should be
# set to the environment of the database you wish to shutdown
clear
echo
echo
echo
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH SHUTDOWN !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database instances NOT shutdown"
echo
exit
fi
# - to find out if this is really a standby
# database of what your environment is set to
dg_set_role
########################################################
# Check the database parm to see if it is standby role
# Should we run dg_set_role to make sure this is standby
########################################################
# This works if test equality
# if [ "$db_role" = "PRIMARY" ]; then
# if [ "$db_role" = "PRIMARY" ]; then
if [ "$db_role" != "PHYSICAL STANDBY" ]; then
# This works if test inequality
#if [ "$db_role" <> "STANDBY" ]; then
echo
echo "Database is "$test_mrc
echo "Not stopping database...."
echo
else
dg_db_stat
echo "DB_ROLE inside if statement.... "$db_role
echo
echo "Stopping standby database except for 1st instance .........."
echo
# srvctl stop instance -d $db -i $db"2",$db"3"
echo "Executing mock shutdown..."
dg_db_stat
fi
}
dg_db_stat()
#########################################################
# Show the current srvctl status of database from CRS
#########################################################
{
echo
echo "Getting srvctl database status....."
echo
srvctl status database -d $db
}
dg_db_config()
#########################################################
# Show the current srvctl status of database from CRS
#########################################################
{
echo
echo "Getting srvctl database status....."
echo
srvctl config database -d $db
}
dg_db_svc_config()
{
echo
echo "Getting services config ....."
echo
srvctl config service -d $db
}
dg_db_svc_status()
{
echo
echo "Getting srvctl services status....."
echo
srvctl status service -d $db
}
dg_srv_stat()
#########################################################
# Show the current srvctl status of all services on DB
#########################################################
{
echo
echo "Getting srvctl database status....."
echo
srvctl status service -d $db
}
dg_stop_allsvc()
{
clear
echo
echo
echo
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH STOPPING ALL SERVICES !!!!!"
echo
echo "Services status before stop " $db1
echo
dg_srv_stat
echo " Stopping All services from "$db1
echo
srvctl stop service -d $db
echo
echo "Services status after stop on : " $db1
echo
dg_srv_stat
else
echo
echo "Databases are not the same name"
echo "Exiting function, will NOT stop services"
echo
exit
fi
}
dg_start_allsvc()
{
clear
echo
echo
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH STARTING ALL SERVICES !!!!!"
echo
echo "Services status before start " $db1
echo
dg_srv_stat
echo
echo "Starting All services on : " $db1
echo
srvctl start service -d $db
echo
echo "Services status after start " $db1
echo
dg_srv_stat
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, will NOT start services"
echo
exit
fi
}
dg_stup_stdby()
#########################################################
# Startup standby instances all but first instance
# TX DEV Primary database DBFSXD01
# VA DEV Standby database DBFSXB01
#########################################################
{
db=$1
echo
echo "getting database status....."
echo
#srvctl status database -d DBFSXB01
srvctl status database -d $db
echo
echo "starting database .........."
echo
srvctl start instance -d $db -i $db"2",$db"3"
echo
echo "getting database status....."
echo
# srvctl status database -d DBFSXB01
srvctl status database -d $db
}
dg_stup_pri()
#########################################################
# Startup primary instances
# TX DEV Primary database DBFSXD01
# VA DEV Standby database DBFSXB01
#########################################################
{
db=$1
echo
echo "getting database status....."
echo
srvctl status database -d $db
echo
echo "starting database .........."
echo
# srvctl start instance -d $db -i $db"2",$db"3"
srvctl start database -d $db
echo
echo "getting database status....."
echo
srvctl status database -d $db
}
dg_bounce_pri()
{
db=$1
echo
echo "stopping database .........."
echo
srvctl stop database -d $db
echo
echo "starting database .........."
echo
srvctl start database -d $db
}
dg_umount_fs_pri()
#############################################
# Unmount the filesystems first
#############################################
{
fs=$1
# fusermount -uz /dbfs_test01
fusermount -uz /$fs
}
dg_sd_pri()
###################################################################
# Database instances shutdown on primary DB
# Shutdown all but the first node
###################################################################
{
clear
echo
echo "This function will attempt to shutdown the PRIMARY database"
echo "of a DataGuard pair"
echo "If the database is not the PRIMARY role, or if the database"
echo "specified in the parameter is not same as the database the "
echo "Current environment is set for, it will exit not shutting down"
echo "anything..."
echo "Please contact GG Hub team before changing anything "
echo
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH SHUTDOWN !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database instances NOT shutdown"
echo
exit
fi
# - to find out if this is really a primary
# database of what your environment is set to
dg_set_role
########################################################
# Check the database parm to see if it is primary role
# Should we run dg_set_role to make sure this is primary
########################################################
# This works if test equality
# if [ "$db_role" = "PRIMARY" ]; then
# if [ "$db_role" = "PRIMARY" ]; then
if [ "$db_role" != "PRIMARY" ]; then
echo
echo "Database is "$db_role
echo "Not stopping non PRIMARY database ...."
echo
else
echo
echo "Shutdown primary database....."
echo "Parameter is database name: "$db
echo
echo "Stop services on this cluster before shutting down ...."
echo
dg_db_svc_status
dg_db_stat
echo
echo "Attempting database instances shutdown........."
echo "Shutdown all instances except first "
echo
## srvctl stop instance -d $db -i $db"2",$db"3" -f
echo "Executing mock shutdown primary..."
dg_db_stat
fi
}
dg_sd_pri_all()
###################################################################
# Database instances shutdown on primary DB
# Shutdown all but the first node
###################################################################
{
db=$1
echo
echo "Shutdown primary database....."
echo "Parameter is database name: "$db
echo
echo "Stop services on this cluster before shutting down ...."
echo
echo
echo "Showing services config ...."
echo
srvctl config service -d $db
echo
echo "Showing database status...."
echo
srvctl status database -d $db
echo
echo "Database shutdown........."
echo
# srvctl stop instance -d $db -i $db"2",$db"3" -f
srvctl stop database -d $db
echo
echo "Showing database status...."
echo
srvctl status database -d $db
}
dg_switch_pri_stat()
#########################################################################
# Check switchover status, add decision tree for actions if possible
#########################################################################
{
sqlplus -s "/ as sysdba" <<EOF
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT DATABASE_ROLE from v\$DATABASE;
-- If the status is to primary we're good, otherwise take additional action
-- if you can fix it automatically, then take that action, if not
-- stop the script with a message to the logfile
--
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
-- SELECT DATABASE_ROLE from v\$DATABASE;
EOF
}
dg_switch_std_stat()
{
sqlplus -s "/ as sysdba" <<EOF
SELECT SWITCHOVER_STATUS FROM V\$DATABASE;
SELECT DATABASE_ROLE from v\$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SELECT DATABASE_ROLE from v\$DATABASE;
EOF
}
dg_mod_db_stdby()
# This will modify a newly STANDBY database in CRS to "standby"
{
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH STANDBY DB CRS MODIFY TO NEW STANDBY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, will NOT modify CRS"
echo
exit
fi
echo
echo "Showing DB status prior to change..."
echo
dg_db_config
dg_db_stat
### srvctl modify database -d $db -r physical_standby -s open
echo
echo "Showing DB status after change..."
echo
dg_db_config
dg_db_stat
}
dg_mod_svc_pri_2_stdby()
########################################################################################
# This changes service for the the old primary / new standby to be a physical standby
# This is only done after the switchover is completed
########################################################################################
{
db=$1
# srvctl modify database -d DBFSXD01 -r physical_standby -s open
srvctl modify database -d $db -r physical_standby -s open
srvctl config database -d $db
srvctl status database -d $db
}
dg_mod_db_pri()
# This will modify a newly PRIMARY database in CRS to "primary"
{
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH PRIMARY DB CRS MODIFY TO NEW PRIMARY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, will NOT modify CRS"
echo
exit
fi
echo
echo "Showing DB status prior to change..."
echo
dg_db_config
dg_db_stat
### srvctl modify database -d $db -r primary -s open
echo
echo "Showing DB status after change..."
echo
dg_db_config
dg_db_stat
}
dg_mod_svc_stdby_2_pri()
########################################################################################
# This changes service for the the old standby / new primary to be a primary
# This is only done after the switchover is completed
########################################################################################
{
db_mod_db_pri
}
dg_start_new_primary()
########################################################################################
# Start the new primary / old standby
########################################################################################
{
echo
echo
echo "Starting primary database up after switchover......"
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH STARTUP OF PRIMARY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database will NOT startup"
echo
exit
fi
echo
echo "Starting $db as a primary...."
echo
# Will be started on one node at this point, so let's shut it down first
### srvctl stop database -d $db
# Start it up on all nodes
### srvctl start database -d $db
# Show the database status
srvctl status database -d $db
}
dg_start_new_stdby()
########################################################################################
# Start the new standby / old primary
########################################################################################
{
echo
echo
echo "Starting standby database up after switchover......"
echo
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH STARTUP OF STANDBY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database will NOT startup"
echo
exit
fi
echo
echo "Starting $db as a standby...."
echo
# Will be started on one node at this point, so let's shut it down first
### srvctl stop database -d $db
# Start it up on all nodes
### srvctl start database -d $db
# Show the database status
srvctl status database -d $db
}
dg_arch_gen()
{
sqlplus -s "/ as sysdba" <<EOF
spool arch_gen_1mon.log
SELECT TRUNC(FIRST_TIME),
COUNT(*)
FROM V\$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
AND DEST_ID = 1
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1;
spool off
EOF
}
dg_so_pri()
############################################################################
#
# Database Switchover for current PRIMARY to go to STANDBY
# Check to make sure the database name passed is same as environment set
# Also checks the PRIMARY role for this db otherwise will drop out
# Will check switchover status to be sure we're in the clear no errors
# Will add functionality to correct common errors ie: log switch etc.
#
############################################################################
{
############################################################################
# Script Name..: dg_so_pri
# Description..:
# Author.......:
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
clear
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH SWITCHOVER TO STANDBY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database will NOT switchover"
echo
exit
fi
echo
echo
echo
echo
echo "Make sure this is done before standby side..."
echo
echo
echo
echo
dg_set_role
dg_set_so_stat
if [ "$db_role" != "PRIMARY" ]; then
clear
echo
echo "This is not the PRIMARY database."
echo "This function requires this to be the PRIMARY DB"
echo
else
if [ "$so_stat" = "TO STANDBY" ]; then
clear
echo
echo "Executing switchover to standby....."
echo
sqlplus -s "/ as sysdba" <<EOF
-- xxx alter database commit to switchover to standby with session shutdown;
select * from dual;
EOF
echo
echo
echo "Switchover to STANDBY should be complete....."
echo
else
echo "Status of Switchover is not allowed currently"
echo "Current Status...: "$so_stat
echo "Check log gaps, or errors"
echo
fi
fi
}
dg_so_stdby()
############################################################################
#
# Database Switchover for current STANDBY to go to PRIMARY
# Check to make sure the database name passed is same as environment set
# Also checks the STANDBY role for this db otherwise will drop out
# Will check switchover status to be sure we're in the clear no errors
#
############################################################################
{
############################################################################
# Script Name..: dg_so_stdby
# Description..:
# Author.......:
# Date.........: / /2012
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# alter session set current
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
clear
db=$1
if [ "$db" = "$DB_UNQ_NAME" ]; then
echo
echo "Databases are the same name"
echo "PROCEEDING WITH SWITCHOVER TO PRIMARY !!!!!"
echo
else
echo
echo "Databases are not the same name"
echo "Exiting function, database will NOT switchover"
echo
exit
fi
echo
echo
echo
echo
echo "Make sure this is done on PRIMARY first !!!!!!!!!!!!!!!!!!!!"
echo
echo
echo
echo
dg_set_role
dg_set_so_stat
if [ "$db_role" = "PRIMARY" ]; then
clear
echo
echo "This is not the STANDBY database."
echo "This function requires this to be the STANDBY DB"
echo
else
if [ "$so_stat" = "TO PRIMARY" ]; then
clear
echo
echo "Executing switchover to PRIMARY....."
echo
sqlplus -s "/ as sysdba" <<EOF
-- xxx alter database commit to switchover to primary with session shutdown;
select * from dual;
EOF
echo
echo
echo "Switchover to PRIMARY should be complete....."
echo
else
echo "Status of Switchover is not allowed currently"
echo "Current Status...: "$so_stat
echo "Check log gaps, or errors"
echo
fi
fi
}
<pre>