Primary Database : TIGGER
DBNAME : TIGGER
DB UNIQUE NAME : TIGGER
Instances : TIGGER on dgprm.racattack
Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
GRID Homes: /u01/app/oracle/product/12.1.0/grid
Standby Database : TIGGER_STBY
DBNAME : TIGGER
DB UNIQUE NAME : TIGGER_STBY
Instances : TIGGER on dgstby.racattack
Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
GRID Homes: /u01/app/oracle/product/12.1.0/grid
--*************************************************
-- Information about Primary Database:
--*************************************************
set linesize 1000
column host_name format a15
select host_name, instance_name, INSTANCE_ROLE, archiver, version, status, database_status from v$instance
/
HOST_NAME INSTANCE_NAME INSTANCE_ROLE ARCHIVE VERSION STATUS DATABASE_STATUS
--------------- ---------------- ------------------ ------- ----------------- ------------ -----------------
dgprm.racattack tigger PRIMARY_INSTANCE STOPPED 12.1.0.1.0 OPEN ACTIVE
select name, db_unique_name, log_mode, force_logging, database_role from v$database;
NAME DB_UNIQUE_NAME LOG_MODE FORCE_LOGGING DATABASE_ROLE
--------- ------------------------------ ------------ --------------------------------------- ----------------
TIGGER tigger NOARCHIVELOG NO PRIMARY
select name from v$controlfile;
NAME
------------------------------------------------------------
+DATA/TIGGER/CONTROLFILE/current.261.856706525
+FRA/TIGGER/CONTROLFILE/current.256.856706525
select name from v$datafile;
NAME
------------------------------------------------------------
+DATA/TIGGER/DATAFILE/system.258.856706425
+DATA/TIGGER/DATAFILE/sysaux.257.856706319
+DATA/TIGGER/DATAFILE/undotbs1.260.856706491
+DATA/TIGGER/DATAFILE/users.259.856706489
select member from v$logfile;
MEMBER
------------------------------------------------------------
+DATA/TIGGER/ONLINELOG/group_3.264.856706541
+FRA/TIGGER/ONLINELOG/group_3.259.856706545
+DATA/TIGGER/ONLINELOG/group_2.263.856706535
+FRA/TIGGER/ONLINELOG/group_2.258.856706539
+DATA/TIGGER/ONLINELOG/group_1.262.856706531
+FRA/TIGGER/ONLINELOG/group_1.257.856706533
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1tigger.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2tigger.dat
dg_broker_start boolean FALSE
--*************************************************
-- 1. On the primary database enable force logging:
--*************************************************
SQL> alter database force logging;
You can confirm this by following query
SQL> select force_logging from V$DATABASE;
FOR
---
YES
--*************************************************
-- 2. Verify Archivelog Mode is enabled:
--*************************************************
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 52309
Next log sequence to archive 52310
Current log sequence 52310
SQL>
shutdown immediate;
startup mount
alter database archivelog
alter database open
--********************************************************************************
-- 3. Copy the Password File from the Primary database to all of the Standby nodes
--********************************************************************************
$ cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
$ scp orapwtigger oracle@dgstby:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwtigger
--********************************************************************
-- 4. Modify Dataguard related init Parameters on Primary
--********************************************************************
DB_NAME=TIGGER
DB_UNIQUE_NAME=TIGGER
LOG_ARCHIVE_CONFIG='DG_CONFIG=(TIGGER,TIGGER_STBY)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TIGGER'
LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER_STBY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=8
FAL_SERVER=TIGGER_STBY
DB_FILE_NAME_CONVERT='TIGGER_STBY','TIGGER'
LOG_FILE_NAME_CONVERT= 'TIGGER_STBY','TIGGER'
STANDBY_FILE_MANAGEMENT=AUTO
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(TIGGER,TIGGER_STBY)' 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=TIGGER' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_STBY ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER_STBY' scope=both sid='*'; --- If your enabling the dataguard broker don't set this paramerter. The broker configuration does that for you. Verify the configuration afterwards.
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='TIGGER_STBY' scope=both sid='*';
alter system set db_file_name_convert='TIGGER_STBY','TIGGER' scope=spfile sid='*';
alter system set log_file_name_convert='TIGGER_STBY','TIGGER' scope=spfile sid='*';
alter system set standby_file_management=AUTO scope=both sid='*';
Verify that the values are set correctly for these parameters after bouncing the database.
set linesize 1000 pages 0
col value for a100
col name for a70
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','db_file_name_convert',
'log_file_name_convert', 'standby_file_management')
/
--********************************************************************
-- 5. Configure Listeners on each site Primary and Standby
--********************************************************************
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=TIGGER_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=tigger)
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=TIGGER_STBY_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=tigger)
)
)
--********************************************************************
-- 6. Configure Oracle Networking on both Primary and Standby Nodes
--********************************************************************
TIGGER_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgprm)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = TIGGER_DGMGRL)))
)
TIGGER_STBY_DGMGRL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstby)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = TIGGER_STBY_DGMGRL)))
)
--***************************
-- 6.1 Check DB connectivity
--***************************
Check that you are able to connect to database using tnsnames.ora entries
tnsping TIGGER_DGMRL
sqlplus sys@TIGGER as sysdba
Standby Site:
Add an oratab entry to make sure you have a SID to set the environment variables.
tigger:/u01/app/oracle/product/12.1.0/dbhome_1:N: # line added by Agent
tnsping TIGGER_DGMGRL
sqlplus sys/racattack@TIGGER_DGMGRL as sysdba
sqlplus sys/racattack@TIGGER_STBY_DGMGRL as sysdba
--********************************************************************
-- 7. Duplicate the primary database to the standby site
--********************************************************************
--Please Note: Execute the duplicate from active database command from the Standby site to pull the files over the network.
--Create the adump directory first before running the rman duplicate command. If you don't the rman command will fail.
mkdir /u01/app/oracle/admin/tigger_stby/adump
Force the startup of a “Dummy” instance
rman target /
RMAN> startup nomount force
RMAN> exit
Connect to the target and auxiliary
$ rman target sys@TIGGER_DGMGRL auxiliary sys@TIGGER_STBY_DGMGRL
Perform the Active Duplicate using RMAN
RMAN>
duplicate target database for standby from active database
spfile
parameter_value_convert 'tigger','tigger_stby'
set db_unique_name='tigger_stby'
set db_file_name_convert='tigger','tigger_stby'
set log_file_name_convert='tigger','tigger_stby'
set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TIGGER_STBY'
set LOG_ARCHIVE_DEST_2='SERVICE=TIGGER_DGMGRL ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TIGGER' --- If your enabling the dataguard broker don't set this paramerter. The broker configuration does that for you. Verify the configuration afterwards.
set control_files='+DATA/TIGGER_STBY/CONTROLFILE/control01.ctl','+FRA/TIGGER_STBY/CONTROLFILE/control01.ctl'
set standby_file_management='AUTO'
set db_recovery_file_dest='+FRA'
set FAL_SERVER='TIGGER_DGMGRL'
set log_archive_max_processes='8'
set audit_file_dest='/u01/app/oracle/admin/tigger_stby/adump';
--*****************************************************************************************************************
-- 8. Create standby redo logs on the primary and standby database that are the same size of the online redo logs.
--*****************************************************************************************************************
Oracle recommends having the same number plus one additional standby redo log for each thread.
For example: Minimum of (threads)*(groups Per Threads + 1)
(3)*(2 + 1) = 9 Redo Logs
You can check the number and group numbers of the redo logs by querying the V$LOG view:
SQL> SELECT * FROM V$LOG;
You can check the results of the previous statements by querying the V$STANDBY_LOG view:
SQL> SELECT * FROM V$STANDBY_LOG;
You can also see the members created by querying the V$LOGFILE view:
SQL> SELECT * FROM V$LOGFILE;
SQL> select max (bytes)/1024/1024, count (1) from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 250
2 250
3 250
4 250
5 250
6 250
6 rows selected.
alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;
alter database add standby logfile size 52428800;
--*********************************************************
-- 9 Startup Active Dataguard from the Standby Database
--*********************************************************
SQL> startup mount
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> select status,instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN tigger PHYSICAL STANDBY READ ONLY
SQL> alter database recover managed standby database disconnect from session;
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 27
ARCH CLOSING 28
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_LOG 29
--*************************************************************************************
-- Do some log switches from the Primary database an confirm the results on both sides.
--*************************************************************************************
alter system switch logfile;
--*********************************************************
-- 10 Shutdown Active Dataguard from the Standby Database
--*********************************************************
SQL> alter database recover managed standby database cancel;
SQL>shutdown immediate;
--********************************************
-- 11 Configure Dataguard Broker
--********************************************
Primary:
ASMCMD> mkdir +FRA/TIGGER/DATAGUARDCONFIG
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+FRA/TIGGER/DATAGUARDCONFIG/dr1TIGGER.dat' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/TIGGER/DATAGUARDCONFIG/dr2TIGGER.dat' SCOPE=BOTH sid='*';
alter system set dg_broker_start=true scope=both sid='*';
Standby:
ASMCMD> mkdir +FRA/TIGGER_STBY/DATAGUARDCONFIG
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+FRA/TIGGER_STBY/DATAGUARDCONFIG/dr1TIGGER.dat' SCOPE=BOTH sid='*';
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+FRA/TIGGER_STBY/DATAGUARDCONFIG/dr2TIGGER.dat' SCOPE=BOTH sid='*';
alter system set dg_broker_start=true scope=both sid='*';
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1tigger_stby.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2tigger_stby.dat
dg_broker_start boolean FALSE
use_dedicated_broker boolean FALSE
--*************************************************************************************************************
-- 11.1 Create the Data Guard configuration using the Primary site. This can only be done from the Primary site.
--*************************************************************************************************************
$> dgmgrl
DGMGRL> connect /
DGMGRL> create configuration tigger_dgb as primary database is tigger connect identifier is TIGGER_DGMGRL;
DGMGRL> add database tigger_stby as connect identifier is TIGGER_STBY_DGMGRL maintained as physical;
Enable the Data Guard configuration
DGMGRL> enable configuration;
--********************************
-- 11.2 Verify Configuration
--********************************
DGMGRL> show configuration;
DGMGRL> show database verbose tigger
DGMGRL> show database verbose tigger_stby
DGMGRL> show instance verbose tigger on database tigger
DGMGRL> show instance verbose tigger on database tigger_stby
--*******************************************************
-- Useful commands to modify Dataguard Broker Properties
--*******************************************************
DGMGRL> edit database 'tigger' set property 'DGConnectIdentifier' = 'TIGGER_DGMGRL'
DGMGRL> edit database 'tigger' set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.81)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=TIGGER_DGMGRL)(INSTANCE_NAME=tigger)(SERVER=DEDICATED)))'
DGMGRL> edit database 'tigger_stby' set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstby.racattack)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TIGGER_STBY_DGMGRL)(INSTANCE_NAME=tigger)(SERVER=DEDICATED)))'
DGMGRL> show database tigger InconsistentProperties
DGMGRL> show database tigger_stby InconsistentProperties
SQL> alter system set archive_lag_target=0 scope=both sid='*';
SQL> alter system set log_archive_min_succeed_dest=1 scope=both sid='*';
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
--***************************************************
-- Some useful My Oracle Support notes and websites:
--***************************************************
Creating a Standby using RMAN Duplicate (RAC or Non-RAC) (Doc ID 1617946.1)
Doc ID 241438.1 - Script to Collect Data Guard Physical Standby Diagnostic Information
Doc ID 241374.1 - Script to Collect Data Guard Primary Site Diagnostic Information
Doc ID 316740.1 - How to configure Client Failover after Data Guard Switchover or Failover
Doc ID 1199943.1 - Data Guard Physical Standby Changing the SYS password when a broker configuration exists
Doc ID 278641.1 - How do you apply a Patchset, PSU or CPU in a Data Guard Physical Standby configuration
Doc ID 730361.1 - Changing the network used by the Data Guard Broker Version 10.2 for redo transport
Doc ID 1349977.1 - Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network
http://www.oracle.com/au/products/database/dataguard11g-rac-maa-1-134639.pdf
http://askdba.org/weblog/oracle11g/dataguard/11gr2-rac-dataguard-setup-using-asm/
http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimaryracphysicalsta-131940.pdf
http://wiki.tapriuneclak.com/index.php?title=Oracle_-_Step_By_Step_of_Configuring_Oracle_11gR2_(11.2.0.1)_RAC_to_RAC_Dataguard
http://www.oracle.com/technetwork/database/features/availability/dataguardoverview-083155.html
http://www.oracle.com/technetwork/database/features/availability/oracle-database-maa-best-practices-155386.html
Oracle Data Guard Concepts and Administration 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e25608/toc.htm
Oracle® Database High Availability Best Practices 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e10803/toc.htm
Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)
Oracle Data Guard Broker 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e17023/toc.htm
DGMGRL> show configuration;
Configuration - tigger_dgb
Protection Mode: MaxPerformance
Databases:
tigger - Primary database
tigger_stby - Physical standby database
Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
09/11/2014 16:13:06
Failed to connect to remote database tigger. Error is ORA-12154
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
Failed to send message to site tigger. Error code is ORA-16501.
DGMGRL> show configuration
Configuration - tigger_dgb
Protection Mode: MaxPerformance
Databases:
tigger - Primary database
tigger_stby - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL> show database tigger_stby InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
tigger ArchiveLagTarget 0 0
tigger LogArchiveMinSucceedDest 1 1
DGMGRL> show database tigger InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
--**************************************************************
-- 9 Modified Startup Active Dataguard from the Standby Database
--**************************************************************
SQL> startup
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session;
SQL> select status,instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN tigger PHYSICAL STANDBY READ ONLY
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CLOSING 27
ARCH CLOSING 28
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_LOG 29
--*************************************************************************************
-- Do some log switches from the Primary database an confirm the results on both sides.
--*************************************************************************************
alter system switch logfile;
--*********************************************************
-- 10 Shutdown Active Dataguard from the Standby Database
--*********************************************************
SQL> alter database recover managed standby database cancel;
SQL>shutdown immediate;
No doubt this is an excellent post I got a lot of knowledge after reading good luck. Theme of blog is excellent there is almost everything to read, Brilliant post. Best south african brokers
ReplyDelete"Just taking his game to the next level, getting better as a player, better as a leader, just all around," Elliott said when asked how his QB improved. super bowl 2020 live stream
ReplyDeleteCardiovascular disorder is therefore common it's about the radar of each and every key care doctor. When an individual develops a risk factor, or some known hazard variable simplifies physicians accept actions to prescribe medications or lifestyle interventions created to prevent disease by progressing or developing. Dr. Eyal Nachum (Eyal.Nachum@sheba.health.gov.il) is a senior cardiologist working in Heart Transplantation Unit, Sheba Medical Center, Ramat Gan, Israel.
ReplyDeleteWow, this was so interesting!Thank you for this interesting blog, such good information.this is our service web site. please visit our web site.
ReplyDeleteThank you.Properties For Sale in Qatar