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>
Currently there is a status of UNRESOLVABLE GAP
on database DBFSXD01 and DBFSXD02
mrcche1de [DBFSXD021]> sql SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 23 14:54:50 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 8589934592 bytes Fixed Size 7663544 bytes Variable Size 1862271048 bytes Database Buffers 6710886400 bytes Redo Buffers 9113600 bytes ORA-01105: mount is incompatible with mounts by other instances ORA-01677: standby file name conversion parameters differ from other instance
status for listener on this node
Service "DBFSXD02.mrcconsulting.com" has 3 instance(s). Instance "DBFSXD021", status BLOCKED, has 1 handler(s) for this service... Instance "DBFSXD022", status READY, has 1 handler(s) for this service... Instance "DBFSXD023", status READY, has 1 handler(s) for this service... BLOCKED
During rolling patching of the cluster
First stop the service, then disable the service, then shutdown the instance
Non-rolling we will stop database
To stop service and instance on cluster
srvctl stop service -d <DB_NAME> -s <SERVICE_NAME> -i <INSTANCE_NAME> srvctl stop service -d DBFSXB01 -s <SERVICE_NAME> -i DBFSXB011 srvctl disable service -d <DB_NAME> -s <SERVICE_NAME> -i <INSTANCE_NAME> srvctl stop instance -d <DB_NAME> -n <HOSTNAME>
To start service and instance on cluster
srvctl start instance -d <DB_NAME> -n <HOSTNAME> srvctl enable service -d <DB_NAME> -s <SERVICE_NAME> -i <INSTANCE_NAME> srvctl start service -d <DB_NAME> -s <SERVICE_NAME> -i <INSTANCE_NAME>
Then we stop cluster as well
This is for rolling patching only
For non-rolling commands
Stop DB and service on the cluster
srvctl stop service -d <DB_NAME> srvctl disable service -d <DB_NAME> -s <SERVICE_NAME> srvctl stop database -d <DB_NAME>
To start DB and instance on cluster
srvctl start database -d <DB_NAME> srvctl enable service -d <DB_NAME> -s <SERVICE_NAME> srvctl start service -d <DB_NAME>
After stopping database/service we can stop CRS (cluster).
and is this on node for MRP
alter database recover managed standby database cancel;
to disconnect don't run anything just stop service and DB
DG is looking for thread1 archive 926 which is applied earlier and got deleted
so we need to re-image
As we don't have backup we need to restore DG from primary
we just have it doing DBFS
And increase archive retention from 7 days to 15 days so that it will kept for long
it may take 2 hr est if no issues
sqlplus "/as sysdba" startup nomount pfile='/oracle/admin/DBFSXB01/pfile/initstdby.ora' and then rman target sys/password@DBFSXD01 auxiliary sys/password@DBFSXB01 duplicate target database for standby from active database;
RMAN completed
now both Primary and standby are in sync
Primary : DBFSXD01
Standby : DBFSXB01
We already recreated standby for D01 to correct the issue
Rebuilt standby from primary using RMAN duplicate
if we have a failure how long DB duplicate from active will take
Actual copy took less than 30 mins. as DB is very small
Total time it took around 2 hrs.
i think that if we have the message we got at first we will have to rebuild that database
in the future and to prevent it from happening again
we will need to make sure we shutdown and startup properly
increase archive retention to 15 days. so that we don't need to rebuild as long as archives are not deleted in that 15 days.
or we make sure they are not deleted before being applied to the standby side
current script already checking applied and more than 7 days old
in this case it was applied and got deleted after 7 days was applied?
due to instance crash, it is looking for instance1 archive while recovering instance2 archive for crash recovery
but instane1 archive got deleted as it was applied previously
increasing archive retention to 15 days will avoid this issue
on node 1 of VA
I should first take down the mrp process on the standby side
And make sure no lag between primary and standby before switchover
First we need to stop all instances except first node on each side that is both primary and standby, normally MRP will run on standby first node.
After stopping other instances on both primary and standby
Run this command on primary :
SELECT DATABASE_ROLE from v$DATABASE; SELECT SWITCHOVER_STATUS FROM V$DATABASE; ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
after this is done we need to run below commands on standby side
SELECT SWITCHOVER_STATUS FROM V$DATABASE; ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; alter database open;
and then modify CRS configuration with SRVCTL as primary and standby.
This command we need to run on primary
srvctl modify database -d <new_primary> -r primary -s open
Now we need to start instances on all nodes of primary with SRVCTL
on new standby we need to stop existing instance and CRS configuration as standby by using below command
srvctl modify database -d <new_standby_db_name> -r physical_standby -s open
Now start DB with SRVCTL command
Recap again:
unmount all DBFS filesystems first on each node
fusermount -uz /dbfs_test01
First on the standby:
srvctl stop instance -d DBFSXB01 -i DBFSXB012,DBFSXB013
Second on the primary
srvctl stop instance -d DBFSXD01 -i DBFSXD012,DBFSXD013
once you have started all instances with SRVCTL
then we need to start MRP on standby
srvctl modify database -d DBFSXB01 -r physical_standby -s open
Here we are modifying the shell of what the name was previously
srvctl modify database -d DBFSXD01 -r physical_standby -s open
D01 and B02
D01 primary for B01
D02 primary for B02
i had failed over D01
D01 Primary for B01
we don't need to any thing with B01 on TX side just we need to make D01 as standby with this command
Previously this was the primary database
srvctl modify database -d DBFSXD01 -r physical_standby -s open
then
srvctl start database -d DBFSXD01
then we need to start MRP on just one node
And we need to open new primary with below command once open DB and then stop
srvctl modify database -d DBFSXB01 -r primary -s open
then we need to start DB on all nodes with SRVCTL
then we need to start MRP on Standby side
mrche1de [DBFSXB011]-> srvctl status database -d DBFSXB01
Instance DBFSXB011 is running on node mrche1de
Instance DBFSXB012 is running on node mrche1df
Instance DBFSXB013 is running on node mrche1dg
this is on VA cluster
Then we can start MRP on node 1 in TX in D01
alter database recover managed standby database using current logfile disconnect from session;
Steps for Dataguard failover
dg_shutdown_stdby_inst
dg_chk_stdby_pre
dg_shutdown_pre_inst
before primary instances 2,3
#!/bin/ksh #set -x Set_Env() { HOSTNAME=`hostname` HR2SPOOL=/tmp/spool4drmonh2.rep; MAXSPOOL=/tmp/spool4drmonmax.rep; DRSPOOLFILE=/tmp/spool4dr.rep; MREPORT="/oracle/admin/scripts/logs/dr_monitor_${ORA_SID}_at_`date +%H`.log" rm -f $MREPORT REMOTE_SID=DPODSB01 #NOTIFY2="mike.culp@gmail.com,test.culp@gmail.com" NOTIFY2="mike.culp@gmail.com" } Get_Info() { sqlplus -s /<<EOF set echo off verify off head off feed off pages0 trimspool on; spool $MAXSPOOL; select thread#, max(sequence#) from v\$archived_log val, v\$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1; spool off; set head on pages 100; spool $HR2SPOOL; select thread#, count(1) Count from v\$log_history where first_time>sysdate - 1 group by thread# order by 1; spool off; exit EOF } Get_From_DR() { sqlplus -s sys/ora98dba@$REMOTE_SID as sysdba<<EOF set echo off verify off head off feed off pages0 trimspool on; spool $DRSPOOLFILE; select thread#, max(sequence#) 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; spool off; exit EOF } Compile_Data() { echo "" >> $MREPORT echo "Following are the Current Log Numbers in Primary and DR" >> $MREPORT echo "" >> $MREPORT GTOTAL=0 echo "INST PRIMARY DR DIFF" >> $MREPORT echo "~~~~~~~~~~~~~~~~~~~~~~~~" >> $MREPORT cat $MAXSPOOL | while read TNO MNO do RMNO=`cat $DRSPOOLFILE | egrep -v "Lagging|Applied|Average" | grep " $TNO " | awk '{print $2}'` let THEDIFF=$MNO-$RMNO let GTOTAL=$GTOTAL+$THEDIFF; echo "$TNO $MNO $RMNO $THEDIFF" >> $MREPORT done echo "" >> $MREPORT echo "DR is lagging behind $GTOTAL logs from Primary " >> $MREPORT echo "" >> $MREPORT echo "Following are Log Generation in last one day " >> $MREPORT cat $HR2SPOOL >> $MREPORT echo "" >> $MREPORT } ########### # M A I N # ########### if [ "$1 " = " " ] then echo " Usage : DR_Mon.ksh <DBNAME> " exit fi DBNAME=$1 if [ `ps -ef | grep -v grep | grep pmon | grep -c ${DBNAME}` -eq 0 ] then print "\n\nDont see the database `tput smso`${DBANME}`tput rmso` Running... It may be running on local failover node. \n\n" exit 1 else export ORA_SID=`ps -ef|grep pmon|grep ${DBNAME}|awk '{print $8}'|sed 's/ora_pmon_//'` fi if [ "$ORA_SID " != " " ] then export NEW_ORACLE_SID=${ORA_SID}; . ~/.profile > /dev/null fi Set_Env; Get_Info; Get_From_DR; Compile_Data; cat $MREPORT | mailx -s "`date +%x_%X`:: $ORA_SID :DR Progress Detail" -r dg.consultants@mrcconsulting.com "$NOTIFY2"
Step #1 Check both sides
Step #2 Execute dg_
Starting with 11gR2, with the advent of the new Grid Infrastructure software, we have the ability to set up services in our databases that can now automatically be tied to the specific role that a database is playing in our Data Guard configuration. Data Guard Broker must be configured and running to use this new feature.
This can be used for both RAC and single instance databases, just so long as Grid Infrastructure had been installed. The services would have to be set up using SRVCTL rather than DBMS_SERVICE.
The great advantage to this for administrators is to simplify the management of client connections to our databases. We can now have clients connect to primary databases, logical standby databases, physical standby databases and snapshot databases. The challenge is to ensure that a client always connects to the "right" database. By connecting via services, and linking the service to only run in a database playing a specific role, we can simplify connection headaches.
The basic syntax for adding a service would be:
SRVCTL ADD SERVICE -d <db unique name> -s <service name> -l <PRIMARY | PHYSICAL STANDBY | LOGICAL STANDBY | SNAPSHOT STANDBY -y AUTOMATIC | MANUAL
The options for -l indicate which role the database must be in for the service to be running. -y indicates whether the service should be started automatically when the database instance starts in the specified role.
However, if the database is already running, you would have to manually start the service using:
SRVCTL START SERVICE -d <db unique name> -s <service name>
In this post, I will demonstrate automatic block media recovery introduced in 11g Data Guard. This feature can be used only if active dataguard is enabled. This feature enables the automatic repair of corrupt blocks transparent to the user and application. If corruption occurs on the primary database, block media recovery is performed automatically using a good copy of the block from the standby database and vice versa.
OVERVIEW:
– Create and populate a test table on primary
– Corrupt two blocks containing table data
– Check that blocks corrupted using dbv
– Flush buffer cache and issue query for corrupt blocks
– The query succeeds as blocks have been repaired automatically
– Verify automatic BMR using
. Alert log of Primary
. dbv utility
— IMPLEMENTATION –
– Create a test tablespace on primary
PRI> drop tablespace test including contents and datafiles; create tablespace test datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ size 30M; – CREATE A TEST USER ON PRIMARY PRI> create user test identified by test default tablespace test; grant connect, resource to test; – CREATE AND POPULATE TABLE TEST_TAB IN TEST TABLESPACE ON PRIMARY PRI> Create table test.test_tab as select * from hr.employees; insert into test.test_tab select * from test.test_tab; / commit; select table_name, tablespace_name from dba_tables where table_name=’TEST_TAB'; select count(*) from test.test_tab; – CHECK THE LEAST BLOCK OCCUPIED BY THE TABLE IN DATAFILE PRI> select min(dbms_rowid.rowid_block_number(rowid)) from test.test_tab; MIN(DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) —————————————– 131 – corrupt two blocks #dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/test01.dbf bs=8192 conv=notrunc seek=136 count=2 – check if datafile corrupted [oracle@host1 ~]$ dbv file=/u01/app/oracle/oradata/orcl/test01.dbf blocksize=8192 Page 136 is marked corrupt Corrupt block relative dba: 0x01800088 (file 6, block 136) Completely zero block found during dbv: Page 137 is marked corrupt Corrupt block relative dba: 0x01800089 (file 6, block 137) Completely zero block found during dbv: Total Pages Marked Corrupt : 2 -- FLUSH BUFFER CACHE AND ISSUE QUERY ON CORRUPTED BLOCKS - QUERY SUCCEEDS AS BLOCKS HAVE BEEN REPAIRED AUTOMATICALLY PRI>alter system flush buffer_cache; select count(*) from test.test_tab; – CHECK THE ALERT LOG OF PRIMARY DATABASE # tailf /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log —————— Tue Dec 18 12:43:01 2012 ALTER SYSTEM: Flushing buffer cache Tue Dec 18 12:43:26 2012 – BLOCK 136 IS FOUND CORRUPT AND IS DUMPED IN TRACE FILE Hex dump of (file 6, block 136) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12608.trc Corrupt block relative dba: 0x01800088 (file 6, block 136) Completely zero block found during multiblock buffer read Reading datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ for corruption at rdba: 0x01800088 (file 6, block 136) Reread (file 6, block 136) found same corrupt data – REQUEST FOR AUTO BMR SENT FOR BLOCK 136 Requesting Auto BMR for (file# 6, block# 136) – BLOCK 137 IS FOUND CORRUPT AND IS DUMPED IN TRACE FILE Hex dump of (file 6, block 137) in trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12608.trc Corrupt block relative dba: 0x01800089 (file 6, block 137) Completely zero block found during multiblock buffer read Reading datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ for corruption at rdba: 0x01800089 (file 6, block 137) Reread (file 6, block 137) found same corrupt data – REQUEST FOR AUTO BMR SENT FOR BLOCK 136 Requesting Auto BMR for (file# 6, block# 137) – AUTO BMR SUCCESSFUL FOR BLOCK 136 Waiting Auto BMR response for (file# 6, block# 136) Auto BMR successful – AUTO BMR SUCCESSFUL FOR BLOCK 137 Waiting Auto BMR response for (file# 6, block# 137) Auto BMR successful – CHECK THAT BLOCKS HAVE BEEN REPAIRED oracle@host1 ~]$ dbv file=/u01/app/oracle/oradata/orcl/test01.dbf blocksize=8192 Total Pages Marked Corrupt : 0
Thanks for your time. Your comments and suggestions are welcome !!!
References:
ABMR – Automatic Block Media Recovery
————————————————————————————–
Related links:
HOME
11g Dataguard Index
11g DataGuard : Automatic Client Failover
11g DataGuard : Automatic Job Failover
11g DataGuard: Flashback Standby After Resetlogs On Primary
Flashback Through Role Transition For Physical Standby
Recover Standby Datafile From Primary
———————————-
——————
DATAGUARD SWITCHOVER GUIDE (PHYSICAL STANDBY)