במסגרת שבוע אורקל העברתי מצגת על Oracle Data Guard – A to Z. זה הסמינר האהוב עלי – יצרתי אותו לפני כשנתיים ובשנה שעבר לא העברתי אותו מסיבות בירוקרטיות-טכניות. במסגרת הסמינר אני מעביר המון אינפורמציה על הארכיטקטורה של הפתרון, איך משתמשים בו ואיך עובדים איתו – את כל זה ניתן למצוא בפוסט שפרסמתי כאן עם המצגת של הסמינר.

במסגרת הסמינר אני מבצע גם דמו ארוך ועתיר שלבים שמציג איך יוצרים, עובדים ומנהלים סביבה כזו של Data Guard ו-DG Broker.

הנה הסקריפט שהשתמשתי בו בזמן המצגת כמו שהוא, לא ערוך וללא הסברים לטובת כל מי שביקש את זה ממני בזמן הסמינר. אני אכין פוסט חדש ובו הסבר איך יצרתי את הסביבה, איך להשתמש בחלקים השונים של הסקריפט והסברים על כל חלק וחלק בו.

שימו לב שהתצוגה של הסקריפט קצת מתחרפנת בגלל שינויים בוורדפרס – הכל שם רק צריך לזוז להתחלה בצד שמאל (כנראה)…:)

(P)-run on Primary (S)-run on Standby
-------------------------------------
OS (p):
--connect to sqlplus
sqlplus / as sysdba

SQL (p):
-- Set the sqlplus environment
set linesize 600
set pagesize 20
set newpage 0

--Check if the Primary db is in archive log mode (must be in archivelog mode!)
archive log list;

shutdown immediate;
startup mount;
alter database archivelog;

--For later use. In order to issue the reinstate command after failover
alter database flashback on;

alter database open;

archive log list;

---------------------------------------------------------
-- Start Configuaring the Primary DB for the Dataguard --
---------------------------------------------------------
alter database force logging;

--Check the number and destination of online redo log files
col member form a100
select group#,member,type from v$logfile;

--Check the sizes of the online redo log files
select bytes/1024/1024 from v$log;

-- Standby logfiles should be in the same size as redo-logfiles
ALTER DATABASE ADD STANDBY LOGFILE '/home/oracle/app/oracle/oradata/orcl/sredo01.log' SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/home/oracle/app/oracle/oradata/orcl/sredo02.log' SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/home/oracle/app/oracle/oradata/orcl/sredo03.log' SIZE 50M REUSE;
ALTER DATABASE ADD STANDBY LOGFILE '/home/oracle/app/oracle/oradata/orcl/sredo04.log' SIZE 50M REUSE;

--view the stanby log files
select group#,member,type from v$logfile;

-- reset all dataguard parameters or bad things might happen
alter system set log_archive_config='' scope=both;
alter system set log_archive_dest_2='' scope=both;
alter system set FAL_SERVER='' scope=both;
alter system set FAL_CLIENT='' scope=both;

--check the db_unique_name
select * from V$DATAGUARD_CONFIG;

--add a db_unique_name
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)';

select * from V$DATAGUARD_CONFIG;

-- Set to where the redo logs should be transferred - "SERVICE" as in the TNSNAMES
alter system set log_archive_dest_2='SERVICE=orcldg VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg';

--Check if the parameter is enabled
show parameter log_archive_dest_state_2

--Set the FAL SERVER and CLIENT parameters
alter system set FAL_SERVER=orcldg scope=both;
alter system set FAL_CLIENT=orcl scope=both;

--Check if the parameter remote_login_passwordfile  is Exclusive
show parameter remote_login_passwordfile
exit

--Transfer the password file to the standby DB as root user(P)
scp $ORACLE_HOME/dbs/orapworcl lnx-ora-server2:$ORACLE_HOME/dbs

-- Configure the tnsnames on both Servers: (P+S)
--Set the TnsNames.ora file
vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lnx-ora-server1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl_dgmgrl)))
ORCLDG =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lnx-ora-server2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcldg_dgmgrl)))

-- Configure the listener:
--Set The Listener.ora file (P)
vi $ORACLE_HOME/network/admin/listener.ora

-- Insert the following
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (SID_NAME = orcl)
   )
   (SID_DESC =
      (GLOBAL_DBNAME = orcl_dgmgrl)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (SID_NAME = orcl)
      (SERVICE_NAME = orcl)
   )
 )

--reload listener
lsnrctl reload

--Set The Listener.ora file (S)
vi $ORACLE_HOME/network/admin/listener.ora

-- Insert the following
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (SID_NAME = orcldg)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcldg_dgmgrl)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_2)
      (SID_NAME = orcldg)
      (SERVICE_NAME = orcldg)
    )
  )

--reload listener
lsnrctl reload
lsnrctl status

-----(S):
--switch to standby server (as root) and create pfile

--insert the following value
echo DB_NAME=orcldg > $ORACLE_HOME/dbs/initorcldg2.ora
vi $ORACLE_HOME/dbs/initorcldg2.ora

-- (S)
--rename the password file we copied
mv $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcldg

#--create adump folder
mkdir -p /home/oracle/app/oracle/admin/orcldg/adump

#--create data files folder -
mkdir -p /home/oracle/app/oracle/oradata/orcldg

#--create recovery_area folder
mkdir -p /home/oracle/app/oracle/flash_recovery_area

--Set ORACLE_SID
export ORACLE_SID=orcldg

--connect to the standby db and start him in nomount mode with pfile
sqlplus / as sysdba
startup nomount pfile=?/dbs/initorcldg2.ora
exit

-------------------------------------------------------------------------------
--RMAN (P):
rman
connect target sys/oracle
connect auxiliary sys/oracle@orcldg

run
{
   allocate channel c1 type disk;
   allocate channel c2 type disk;
   allocate channel c3 type disk;
   allocate channel c4 type disk;
   allocate channel c5 type disk;
   allocate channel c6 type disk;
   allocate auxiliary channel a1 type disk;
   allocate auxiliary channel a2 type disk;
   allocate auxiliary channel a3 type disk;
   allocate auxiliary channel a4 type disk;
   allocate auxiliary channel a5 type disk;
   allocate auxiliary channel a6 type disk;
    duplicate TARGET DATABASE
    FOR STANDBY
    FROM ACTIVE DATABASE
    SPFILE
       parameter_value_convert 'orcl','orcldg'
       SET control_files='/home/oracle/app/oracle/oradata/orcldg/control01.ctl', '/home/oracle/app/oracle/oradata/orcldg/control02.ctl'
       SET DB_UNIQUE_NAME= 'orcldg'
       SET instance_name= 'orcldg'
       set db_file_name_convert='/home/oracle/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/orcldg/'
       set log_file_name_convert='/home/oracle/app/oracle/oradata/orcl/','/home/oracle/app/oracle/oradata/orcldg/'
	   set db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
       set audit_file_dest= '/home/oracle/app/oracle/admin/orcldg/adump/'
       SET FAL_CLIENT="orcldg" COMMENT "Is standby"
       SET FAL_SERVER="orcl" COMMENT "Is primary"
	   set standby_file_management='AUTO'
       set log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl';
}

--Connect to the standby db and run the following (S):
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
shutdown immediate;
startup mount;
alter database flashback on;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--Check the DG (S)
select process,status,sequence# from V$MANAGED_STANDBY;

--Switch log file (P)
alter system switch logfile;

--Check the DG (S) , if all is good the sequence number should change
select process,status,sequence# from V$MANAGED_STANDBY;

--Check the DG (S)
col message form a80
set lines 300 pages 500
SELECT * FROM v$dataguard_status;

--Check the DG (S)
SELECT name, value, time_computed FROM v$dataguard_stats;

------------------------------------------------------------------------------
--- DATA GUARD BROKER CONFIGURATION ------
------------------------------------------------------------------------------
-- Configure the tnsnames on both servers: (P+S)

--Set the TnsNames.ora file
vi $ORACLE_HOME/network/admin/tnsnames.ora

orcl_DGMGRL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lnx-ora-server1)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SID = orcl)))
orclDG_DGMGRL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lnx-ora-server2)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SID = orcldg)))

-- Run the following SQL commands on both DBs (p+s):
sqlplus / as sysdba
alter system set dg_broker_start=true scope=both;

-- Use DGMGRL tool (p):
dgmgrl
connect sys/oracle

--Configure the primary
create configuration 'my_dg' as primary database is 'orcl' CONNECT identifier is 'orcl';

--Configure the standby
add database 'orcldg' as connect identifier is 'orcldg';

--Enable
ENABLE CONFIGURATION;

--Check statuses
show database verbose orcl;
show database verbose orcldg;

----- SWITCHOVER ---
--In order to switchover, issue the following in the DGMGRL in the Primary DB
switchover to 'orcldg';

----- FAILOVER ---
--Connect to the primary and shut it down
sqlplus sys/oracle as sysdba
shutdown immediate

--Connect to the standby and use dgmgrl to check the primary status
dgmgrl sys/oracle

DGMGRL> show configuration

Configuration - my_dg

  Protection Mode: MaxPerformance
  Databases:
    orcl   - Primary database
    orcldg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "orcl"
DGM-17017: unable to determine configuration status
-----------------------------------------------------
--Issue the following command
 show configuration
DGMGRL> switchover to orcl
Performing switchover NOW, please wait...
Error: ORA-01034: ORACLE not available
Error: ORA-16625: cannot reach database "orcldg"

Failed.
Unable to switchover, primary database is still "orcldg"

DGMGRL> failover to orcl
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl"

---------------------------
--- REINSTATE
---------------------------
--connect to lnx-ora-server1 and to the database and start it to mount mode
sqlplus / as sysdba
startup mount;

--connect to lnx-ora-server2, and check orcl status should be disable
dgmgrl
show configuration;

--connect to lnx-ora-server1 and run the following commande in dgmgrl:
dgmgrl sys/oracle
reinstate database orcldg;
show configuration;

---------------------------
--- snapshot database
---------------------------
dgmgrl sys/oracle
convert database 'orcldg' to snapshot standby;
show configuration

sqlplus sys/oracle@orcldg
create table testing as select * From dba_objects;

dgmgrl sys/oracle
CONVERT DATABASE 'orcldg' to PHYSICAL STANDBY;
show configuration