RAC Data Guard setup and management with Oracle 11gR2 (11.2.0.2)
RAC Data Guard setup and management with Oracle 11gR2 (11.2.0.2)
In the article we will look at Data Guard creation and management using both Oracle 11gR2 RAC as a primary and standby database. In the article OEL 5.4 and Oracle 11.2 Grid Infrastructure and RDBMS are used and ASM is used as storage. Oracle 11gR2 GI and RDBMS installation or update is a prerequisite for setup a DR primary and standby sites using RAC. The primary database is a RAC database created as described here.. The article will emphasize on creating and managing standby RAC database using sqlplus, RMAN and Data Guard Broker (dgmgrl) without OEM, whereas information related to OEM Grid Control 11g and Data Guard can be obtained here. The general approach for creating a DR standby database includes the following steps:
- Enable force logging on the primary database.
- Make sure that primary uses spfile on ASM registered with OCR.
- Create standby redo logs on the primary database.
- Configure Redo Transport. For RAC configure each instance.
- Configure Oracle NET on both primary and standby sites to have a static entry in the GI listeners and create RDBMS tnsnames aliases.
- Create password files for the standby database. Note that the password file needs to be created with ignorecase=y option.
- Create pfile on the standby site and the respective DIAG directories on the standby site.
- Use RMAN to create a standby clone. Since 11g an active clone can be done from the primary. If RAC is involved for the duration of the cloning set cluster_database=FALSE.
- Make sure that standby uses spfile on ASM registered with OCR.
- Register the standby database in OCR.
- Set up DG Broker.
- Create Data Guard Configuration
RAC specifics are reflected and include
- Temporarily set cluster_database=FALSE during the clone.
- Create pfile that points to shared spfile on ASM.
- Register RAC databases with OCR.
- Create Data Guard Broker configuration files on both sites that are shared between instances and reside on ASM.
- Having both databases in a RAC cluster.
After the Data Guard is set a switchover, failover and reinstate are performed and fixes are provided for the errors encountered during the setup and validation.
The article has the folowing sections
- Data Guard Setup
- Solution verification
- Errors and fixes
- Summary
- Annex with configuration files
Data Guard Setup
The source database tst that will serve as a primary database is configured as specified in Annex 1. In the section a physical standby database will be created and Data Guard broker will be configured.
-
Enable force logging.
SQL> alter database force logging;
-
Create Standby Redo Logs (SRL). In Annex 2 is the information related to the online redo log (ORL) files in the database. As there are three redo log groups with two members each we will create four members for each thread. Note that OMF (db_create_file_dest=’+DGDUP’) are used and the SRL are created with the same size as ORL.
alter database add standby logfile thread 1 group 7 size 104857600;
alter database add standby logfile thread 1 group 8 size 104857600;
alter database add standby logfile thread 1 group 9 size 104857600;
alter database add standby logfile thread 1 group 10 size 104857600;
alter database add standby logfile thread 2 group 11 size 104857600;
alter database add standby logfile thread 2 group 12 size 104857600;
alter database add standby logfile thread 2 group 13 size 104857600;
alter database add standby logfile thread 2 group 14 size 104857600;
-
Configure redo transport on the primary for each instance to the standby database.
SQL> alter system set log_archive_config=’dg_config=(tst,tststby)’ sid=’*’ scope=both;
System altered.
SQL>
SQL> alter system set log_archive_dest_2=’service=tststby SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tststby’ sid=’*’ scope=both;
System altered.
SQL>
-
Configure tnsnames alias and a static registration with the GI listeners for both primary and standby database and compliant with Data Guard Broker. With Oracle 11gR2 since the SCAN Listener was introduced the modification is made to both listeners in GI $OH and to the tnsnames.ora in RDBMS $OH. Annex 3 contains the tnsnames.ora and listener.ora. Reload the listeners.
TSTSTBY
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID_NAME = tststby)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tst)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
(SID_DESC =
(GLOBAL_DBNAME = tst_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
)
-
Create a oracle password file for the standby. To avoid error ORA-16191 specify ignorecase=y. The standby will be created as a single instance database and then will be RAC enabled. That is why three password files are created.
orapwd file=orapwtststby entries=100 password=sys1 ignorecase=y
orapwd file=orapwtststby1 entries=100 password=sys1 ignorecase=y
orapwd file=orapwtststby2 entries=100 password=sys1 ignorecase=y
- Create a pfile for the standby database based on the pfile from the primary database.as in Annex 4 and set *.cluster_database=FALSE and *.db_name=’tststby’.
- Create $ORACLE_BASE/admin/tststby/adump directory on the standby DR site.
-
Verify that connection to primary and standby can be made using the tnsnames aliases using the Oracle password files.
sqlplus sys/sys1@tst as sysdba
sqlplus sys/sys1@tst stbyas sysdba
-
Prepare RMAN scripts to create the standby and set the parameters for the standby database. Start the instance on the standby DR site using the pfile created in step 6. Invoke RMAN connect to target and auxiliary instances. Make sure to connect to auxiliary using the tnsnsmes alias to avoid errors.Run the script. The output from the script is in Annex 5.
run {
allocate channel tst type disk;
allocate channel tst1 type disk;
allocate auxiliary channel tststby type disk;
duplicate target database for standby from active database DORECOVER
spfile
parameter_value_convert “tst”,”tststby”
set db_unique_name=”tststby”
set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set fal_client=”tststby”
set fal_server=”tst”
set standby_file_management=”AUTO”
set log_archive_config=”dg_config=(tst,tststby)”
set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}
-
Register the standby database with OCR. Create a shared ASM based spfile.. Make sure that *.cluster_database=TRUE and each instance pfile pints to the ASM based spfile. See Annex 6 for more information.
[oracle@raclinux2 dbs]$ srvctl add database -d tststby -o /u01/app/oracle/product/11.2.0/db_10 -s mount -r physical_standby -c RAC
[oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby2 -n raclinux2
[oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby1 -n raclinux1
Create a shared spfile on ASM.
SQL> create spfile=’+DGDUP/spfiletststby.ora’ from memory;
File created.
SQL>
Make sure
[oracle@raclinux2 dbs]$ cat inittststby2.ora for instance 1 as well
SPFILE=’+DGDUP/spfiletstsstby.ora’
[oracle@raclinux2 dbs]$
[oracle@raclinux2 trace]$ srvctl add service -d tststby -s tststby_s -r tststby2 -a tststby1
[oracle@raclinux2 dbs]$ srvctl modify database -d tststby -p ‘+DGDUP/spfiletststby.ora’
[oracle@raclinux2 dbs]$ srvctl config database -d tststby
Database unique name: tststby
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/db_10
Oracle user: oracle
Spfile: +DGDUP/spfiletststby.ora
Domain:
Start options: mount
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: tststby
Database instances: tststby1,tststby2
Disk Groups: DGDUP
Mount point paths:
Services: tststby_s
Type: RAC
Database is administrator managed
[oracle@raclinux2 dbs]$
-
Make sure that the standby database is operational. Test redo shipping and redo apply. See Annex 7 for details.
SQL> select PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE
——————– ——————– ——————– —————-
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY
SQL>
-
Set Data Guard Broker. Execute the following SQL on both primary and standby database.
SQL> alter system set dg_broker_start=true scope=both sid=’*’;
- Create Data Guard Configuration. From dgmgrl execute the following commands.
DGMGRL> connect /
Connected.
DGMGRL> create configuration “DataGuard” as primary database is “tst” connect identifier is tst;
Configuration “DataGuard” created with primary database “tst”
DGMGRL>
DGMGRL> add database “tststby” as connect identifier is tststby;
Database “tststby” added
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
Warning: ORA-16532: Data Guard broker configuration does not exist
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
See the resolution of the error in point 1 in Error and Fixes. After locating the files to ASM shared among the RAC instances we have,
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
tststby – Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show configuration verbose;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
tststby – Physical standby database (disabled)
Properties:
FastStartFailoverThreshold = ’30’
OperationTimeout = ’30’
FastStartFailoverLagLimit = ’30’
CommunicationTimeout = ‘180’
FastStartFailoverAutoReinstate = ‘TRUE’
FastStartFailoverPmyShutdown = ‘TRUE’
BystandersFollowRoleChange = ‘ALL’
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show database “tst”;
Database – tst
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
tst1
tst2
Database Status:
SUCCESS
DGMGRL> show database “tststby”;
Database – tststby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
tststby2
Database Status:
DISABLED
DGMGRL>
-
Turn Flashback Database feature on both Primary and Standby database.
Issue on the primary
SQL> alter database flashback on;
Database altered.
SQL>
Issue on the Standby
DGMGRL> edit database “tststby” set state=’apply-off’;
Succeeded.
DGMGRL>
SQL> alter database flashback on;
Database altered.
SQL>
DGMGRL> edit database “tststby” set state=’apply-on’;
Succeeded.
DGMGRL>
-
Set Fast Start failover.
DGMGRL> show fast_start failover;
Fast-Start Failover: DISABLED
Threshold: 30 seconds
Target: (none)
Observer: (none)
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> show fast_start failover;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: tststby
Observer: (none)
Lag Limit: 30 seconds
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
(none)
DGMGRL>
Solution Verification
In order to make sure that DR Data Guard implementation works I will implement a switchover, failover, reinstate a former primary as a standby after failover and some troubleshooting..
-
Switchover
DGMGRL> switchover to “tststby”;
Performing switchover NOW, please wait…
New primary database “tststby” is opening…
Operation requires shutdown of instance “tst1” on database “tst”
Shutting down instance “tst1″…
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
shut down instance “tst1” of database “tst”
start up and mount instance “tst1” of database “tst”
DGMGRL>
From sqlplus perform the requested activities.
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tststby – Primary database
tst – (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL>
-
Changing a protection Mode. Depending on how Redo transport is set the protection mode can be easily changed as shown below.
DGMGRL> edit configuration set PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxAvailability
Databases:
tst – Primary database
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> edit configuration set PROTECTION MODE AS MaxProtection;
Succeeded.
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxProtection
Databases:
tst – Primary database
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
SQL> select NAME, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE,PROTECTION_LEVEL from v$database;
NAME DATABASE_ROLE DATAGUAR PROTECTION_MODE PROTECTION_LEVEL
——— —————- ——– ——————– ——————–
TST PRIMARY ENABLED MAXIMUM PROTECTION MAXIMUM PROTECTION
SQL>
-
Starting with Oracle 11.2.0.2 a SQL can be executed from dgmgrl utility. See an example of executing SQL.
[oracle@raclinux1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect /
Connected.
DGMGRL> sql “alter system archive log current”;
Succeeded.
DGMGRL> sql “alter system archive log current”;
Succeeded.
DGMGRL> sql “alter system archive log current”;
Succeeded.
DGMGRL>
-
Failover. Connect to the standby database to perform the failover.
DGMGRL> connect sys/sys1@tststby
Connected.
DGMGRL> failover to “tststby”;
Performing failover NOW, please wait…
Failover succeeded, new primary is “tststby”
DGMGRL>
-
Reinstate. the former primary database.
DGMGRL> connect sys/sys1@tststby
Connected.
DGMGRL> failover to “tststby”;
Performing failover NOW, please wait…
Failover succeeded, new primary is “tststby”
DGMGRL> reinstate database “tst”;
Reinstating database “tst”, please wait…
Operation requires shutdown of instance “tst1” on database “tst”
Shutting down instance “tst1″…
ORA-03113: end-of-file on communication channel
Please complete the following steps and reissue the REINSTATE command:
shut down instance “tst1” of database “tst”
start up and mount instance “tst1” of database “tst”
DGMGRL>
DGMGRL> connect /
Connected.
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tststby – Primary database
tst – Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> reinstate database “tst”;
Reinstating database “tst”, please wait…
Reinstatement of database “tst” succeeded
DGMGRL>
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
.
Errors and fixes
-
Error ORA-16532 . The Data Guard Broker configuration in RAC instances should be on shared storage accessible from all instances in a RAC. Therefore, change the Parameters for both standby and primary database. After that re-create the configuration. Also If the error is
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16603: Data Guard broker detected a mismatch in configuration ID
ORA-16625: cannot reach database “tst”
DGM-17017: unable to determine configuration status
Determine the current DGB location and recreate the file on a shared locate and make them accessible to all RAC instances.
SQL> show parameter broker
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/db_10/dbs/dr1tststby.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/db_10/dbs/dr2tststby.dat
dg_broker_start boolean TRUE
SQL> alter system set dg_broker_start=false scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_config_file1=’+DGDUP/tst/dr1tststby.dat’ scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_config_file2=’+DGDUP/tst/dr2tststby.dat’ scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_start=true scope=both sid=’*’;
System altered.
SQL>
SQL> show parameter broker
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/db_10/dbs/dr1tst.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/db_10/dbs/dr2tst.dat
dg_broker_start boolean TRUE
SQL> alter system set dg_broker_start=false scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_config_file1=’+DGDUP/tst/dr1tst.dat’ scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_config_file2=’+DGDUP/tst/dr2tst.dat’ scope=both sid=’*’;
System altered.
SQL> alter system set dg_broker_start=true scope=both sid=’*’;
System altered.
SQL>
-
Error ORA-16191
If alert log files show the error or archive log destination is not reachable
Wed Dec 22 13:48:39 2010
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
————————————————————
FAL[client, MRP0]: Error 16191 connecting to tststby for fetching gap sequence
Wed Dec 22 13:48:49 2010
Error 1017 received logging on to the standby
————————————————————
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
————————————————————
FAL[client, MRP0]: Error 16191 connecting to tststby for fetching gap sequence
Make sure that the Primary database can communicate to the standby database. In 11gR2 recreate the password file with option ignorecase=y of the orapwd utility.
-
Error ORA-16715, ORA-16714. In case that there are mismatch of properties make sure that they are corrected either from sqlplus or from dgmgrl. For example In the setup SYNC transport was used so I set up the SYNC in DGMGRL. Similarly a lag or can also be chaged.
DGMGRL> show database verbose “tststby”;
Database – tststby
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
tststby1
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16715: redo transport-related property LogXptMode of standby database “tst” is inconsistent
tststby2
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16715: redo transport-related property LogXptMode of standby database “tst” is inconsistent
Properties:
DGConnectIdentifier = ‘tststby’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘4’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘+DGDUP/tst, +DGDUP/tststby’
LogFileNameConvert = ‘+DGDUP/tst, +DGDUP/tststby’
FastStartFailoverTarget = ‘tst’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) – Please check specific instance for the property value
Database Status:
WARNING
DGMGRL>
DGMGRL> switchover to “tst”;
Performing switchover NOW, please wait…
Error: ORA-16809: multiple warnings detected for the database
Failed.
Unable to switchover, primary database is still “tststby”
DGMGRL>
DGMGRL> edit database “tst” set property LogXptMode=”SYNC”;
Property “logxptmode” updated
DGMGRL> edit database “tststby” set property LogXptMode=”SYNC”;
Property “logxptmode” updated
DGMGRL>
DGMGRL> switchover to “tst”;
Performing switchover NOW, please wait…
New primary database “tst” is opening…
Operation requires shutdown of instance “tststby1” on database “tststby”
Shutting down instance “tststby1″…
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
shut down instance “tststby1” of database “tststby”
start up and mount instance “tststby1” of database “tststby”
DGMGRL>
After performing the request we have.
DGMGRL> connect /
Connected.
DGMGRL> show configuration;
Configuration – DataGuard
Protection Mode: MaxPerformance
Databases:
tst – Primary database
tststby – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
[oracle@raclinux1 adump]$
Summary
In the article we looked at the step by step setup of Data Guard solution where the primary and standby databases are RAC. The configuration was verified by performing switchover, failover and reinstating the former primary after the failover.
Annex
1.Primary database configuration
[oracle@raclinux1 ~]$ srvctl config database -d tst
Database unique name: tst
Database name: tst
Oracle home: /u01/app/oracle/product/11.2.0/db_10
Oracle user: oracle
Spfile: +DGDUP/spfiletst.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: tst
Database instances: tst1,tst2
Disk Groups: DGDUP
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@raclinux1 ~]$
[oracle@raclinux1 ~]$ srvctl status database -d tst
Instance tst1 is running on node raclinux1
Instance tst2 is running on node raclinux2
[oracle@raclinux1 ~]$
[oracle@raclinux1 dbs]$ cat /tmp/itst.ora
tst.__db_cache_size=125829120
tst2.__db_cache_size=117440512
tst1.__db_cache_size=117440512
tst.__java_pool_size=4194304
tst2.__java_pool_size=4194304
tst1.__java_pool_size=4194304
tst.__large_pool_size=4194304
tst2.__large_pool_size=4194304
tst1.__large_pool_size=4194304
tst.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
tst1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
tst2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
tst.__pga_aggregate_target=209715200
tst2.__pga_aggregate_target=209715200
tst1.__pga_aggregate_target=209715200
tst.__sga_target=314572800
tst2.__sga_target=314572800
tst1.__sga_target=314572800
tst.__shared_io_pool_size=0
tst2.__shared_io_pool_size=0
tst1.__shared_io_pool_size=0
tst.__shared_pool_size=167772160
tst2.__shared_pool_size=180355072
tst1.__shared_pool_size=180355072
tst.__streams_pool_size=4194304
tst2.__streams_pool_size=0
tst1.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest=’/u01/app/oracle/admin/tst/adump’
*.audit_trail=’db’
*.cluster_database=TRUE
*.compatible=’11.2.0.0.0′
*.control_files=’+DGDUP/tst/controlfile/current.285.738230295′,’+DGDUP/tst/controlfile/current.288.738230295’#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest=’+DGDUP’
*.db_domain=”
*.db_name=’tst’
*.db_recovery_file_dest=’+DGDUP’
*.db_recovery_file_dest_size=4227858432
*.dg_broker_config_file1=’+DGDUP/tst/dr1tst.dat’
*.dg_broker_config_file2=’+DGDUP/tst/dr2tst.dat’
*.dg_broker_start=TRUE
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tstXDB)’
*.fal_server=’tststby’
tst1.instance_number=1
tst2.instance_number=2
*.log_archive_config=’dg_config=(tst,tststby)’
*.log_archive_dest_1=’location=USE_DB_RECOVERY_FILE_DEST’,’valid_for=(ALL_LOGFILES, ALL_ROLES)’
*.log_archive_dest_2=’service=tststby SYNC valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=tststby’
*.log_archive_dest_state_2=’ENABLE’
tst2.log_archive_format=’%t_%s_%r.dbf’
tst1.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
tst2.log_archive_trace=0
tst1.log_archive_trace=0
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’MANUAL’
tst1.thread=1
tst2.thread=2
tst1.undo_tablespace=’UNDOTBS1′
tst2.undo_tablespace=’UNDOTBS2′
[oracle@raclinux1 dbs]$
2.Database online redo logfile and standby redo log files
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 22 104857600 512 2 NO CURRENT 1166302 21-DEC-10 2.8147E+14 21-DEC-10
2 1 20 104857600 512 2 YES INACTIVE 1085090 20-DEC-10 1114616 20-DEC-10
3 1 21 104857600 512 2 YES INACTIVE 1114616 20-DEC-10 1114635 20-DEC-10
4 2 4 104857600 512 2 YES INACTIVE 1114683 20-DEC-10 1157139 21-DEC-10
5 2 5 104857600 512 2 NO CURRENT 1157139 21-DEC-10 2.8147E+14
6 2 3 104857600 512 2 YES INACTIVE 1085088 20-DEC-10 1114683 20-DEC-10
6 rows selected.
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
4 ONLINE +DGDUP/tst/onlinelog/group_4.290.738230689 NO
1 ONLINE +DGDUP/tst/onlinelog/group_1.297.738230611 NO
1 ONLINE +DGDUP/tst/onlinelog/group_1.298.738230625 YES
2 ONLINE +DGDUP/tst/onlinelog/group_2.299.738230635 NO
2 ONLINE +DGDUP/tst/onlinelog/group_2.300.738230643 YES
3 ONLINE +DGDUP/tst/onlinelog/group_3.301.738230655 NO
3 ONLINE +DGDUP/tst/onlinelog/group_3.302.738230667 YES
4 ONLINE +DGDUP/tst/onlinelog/group_4.289.738230711 YES
5 ONLINE +DGDUP/tst/onlinelog/group_5.292.738230723 NO
5 ONLINE +DGDUP/tst/onlinelog/group_5.291.738230739 YES
6 ONLINE +DGDUP/tst/onlinelog/group_6.293.738230763 NO
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
6 ONLINE +DGDUP/tst/onlinelog/group_6.303.738230779 YES
12 rows selected.
SQL>
We are going to create 4 groups in each thread
alter database add standby logfile thread 1 group 7 size 104857600;
alter database add standby logfile thread 1 group 8 size 104857600;
alter database add standby logfile thread 1 group 9 size 104857600;
alter database add standby logfile thread 1 group 10 size 104857600;
alter database add standby logfile thread 2 group 11 size 104857600;
alter database add standby logfile thread 2 group 12 size 104857600;
alter database add standby logfile thread 2 group 13 size 104857600;
alter database add standby logfile thread 2 group 14 size 104857600;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
4 ONLINE +DGDUP/tst/onlinelog/group_4.290.738230689 NO
1 ONLINE +DGDUP/tst/onlinelog/group_1.297.738230611 NO
1 ONLINE +DGDUP/tst/onlinelog/group_1.298.738230625 YES
2 ONLINE +DGDUP/tst/onlinelog/group_2.299.738230635 NO
2 ONLINE +DGDUP/tst/onlinelog/group_2.300.738230643 YES
3 ONLINE +DGDUP/tst/onlinelog/group_3.301.738230655 NO
3 ONLINE +DGDUP/tst/onlinelog/group_3.302.738230667 YES
4 ONLINE +DGDUP/tst/onlinelog/group_4.289.738230711 YES
5 ONLINE +DGDUP/tst/onlinelog/group_5.292.738230723 NO
5 ONLINE +DGDUP/tst/onlinelog/group_5.291.738230739 YES
6 ONLINE +DGDUP/tst/onlinelog/group_6.293.738230763 NO
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
6 ONLINE +DGDUP/tst/onlinelog/group_6.303.738230779 YES
7 STANDBY +DGDUP/tst/onlinelog/group_7.331.738335237 NO
7 STANDBY +DGDUP/tst/onlinelog/group_7.332.738335247 YES
8 STANDBY +DGDUP/tst/onlinelog/group_8.333.738335257 NO
8 STANDBY +DGDUP/tst/onlinelog/group_8.334.738335267 YES
9 STANDBY +DGDUP/tst/onlinelog/group_9.335.738335277 NO
9 STANDBY +DGDUP/tst/onlinelog/group_9.336.738335285 YES
10 STANDBY +DGDUP/tst/onlinelog/group_10.337.738335295 NO
10 STANDBY +DGDUP/tst/onlinelog/group_10.319.738335309 YES
11 STANDBY +DGDUP/tst/onlinelog/group_11.304.738335317 NO
11 STANDBY +DGDUP/tst/onlinelog/group_11.338.738335327 YES
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
12 STANDBY +DGDUP/tst/onlinelog/group_12.339.738335339 NO
12 STANDBY +DGDUP/tst/onlinelog/group_12.340.738335349 YES
13 STANDBY +DGDUP/tst/onlinelog/group_13.341.738335359 NO
13 STANDBY +DGDUP/tst/onlinelog/group_13.342.738335367 YES
14 STANDBY +DGDUP/tst/onlinelog/group_14.343.738335379 NO
14 STANDBY +DGDUP/tst/onlinelog/group_14.344.738335385 YES
28 rows selected.
SQL>
SQL> select GROUP#, THREAD#, BYTES, STATUS from v$standby_log;
GROUP# THREAD# BYTES STATUS
———- ———- ———- ———-
7 1 104857600 UNASSIGNED
8 1 104857600 UNASSIGNED
9 1 104857600 UNASSIGNED
10 1 104857600 UNASSIGNED
11 2 104857600 UNASSIGNED
12 2 104857600 UNASSIGNED
13 2 104857600 UNASSIGNED
14 2 104857600 UNASSIGNED
8 rows selected.
SQL>
3.Changes to listener.ora and tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_10/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)
TST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tst)
)
)
TSTSTBY
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID_NAME = tststby)
)
)
[oracle@raclinux1 admin]$
5. Create a static entry in the GI scan listener
[oracle@raclinux1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0.2/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tst)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
(SID_DESC =
(GLOBAL_DBNAME = tst_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tst)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
(SID_DESC =
(GLOBAL_DBNAME = tst_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/oracle
[oracle@raclinux1 admin]$ cat listener.ora.old
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
[oracle@raclinux1 admin]$
4. pfile for the standby database creation
[oracle@raclinux1 dbs]$ cat inittststby1.ora
tststby.__db_cache_size=125829120
tststby2.__db_cache_size=117440512
tststby1.__db_cache_size=130023424
tststby.__java_pool_size=4194304
tststby2.__java_pool_size=4194304
tststby1.__java_pool_size=4194304
tststby.__large_pool_size=4194304
tststby2.__large_pool_size=4194304
tststby1.__large_pool_size=4194304
tststby.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
tststby1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
tststby.__pga_aggregate_target=209715200
tststby2.__pga_aggregate_target=209715200
tststby1.__pga_aggregate_target=209715200
tststby.__sga_target=314572800
tststby2.__sga_target=314572800
tststby1.__sga_target=314572800
tststby.__shared_io_pool_size=0
tststby2.__shared_io_pool_size=0
tststby1.__shared_io_pool_size=0
tststby.__shared_pool_size=167772160
tststby2.__shared_pool_size=180355072
tststby1.__shared_pool_size=167772160
tststby.__streams_pool_size=4194304
tststby2.__streams_pool_size=0
tststby1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/tststby/adump’
*.audit_trail=’db’
*.cluster_database=FALSE
*.compatible=’11.2.0.0.0′
*.db_block_size=8192
*.db_create_file_dest=’+DGDUP’
*.db_domain=”
*.db_name=’tststby’
*.db_recovery_file_dest=’+DGDUP’
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest=’/u01/app/oracle’
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
tststby1.instance_number=1
tststby2.instance_number=2
5.RMAN output from the standby creation
[oracle@raclinux1 dbs]$ sqlplus sys/sys1@tststby as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 21 17:09:57 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittststby1.ora
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 390070424 bytes
Database Buffers 125829120 bytes
Redo Buffers 3809280 bytes
SQL>
[oracle@raclinux1 dbs]$ rman target / auxiliary sys/sys1@tststby
Recovery Manager: Release 11.2.0.2.0 – Production on Tue Dec 21 17:42:12 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST (DBID=1879374527)
connected to auxiliary database: TSTSTBY (not mounted)
RMAN> run {
allocate channel tst type disk;
allocate channel tst1 type disk;
allocate auxiliary channel tststby type disk;
duplicate target database for standby from active database DORECOVER
spfile
parameter_value_convert “tst”,”tststby”
set db_unique_name=”tststby”
set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set fal_client=”tststby”
set fal_server=”tst”
set standby_file_management=”AUTO”
set log_archive_config=”dg_config=(tst,tststby)”
set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>
using target database control file instead of recovery catalog
allocated channel: tst
channel tst: SID=58 device type=DISK
allocated channel: tst1
channel tst1: SID=61 device type=DISK
allocated channel: tststby
channel tststby: SID=25 device type=DISK
Starting Duplicate Db at 21-DEC-10
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db_10/dbs/orapwtst1’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db_10/dbs/orapwtststby’ targetfile
‘+DGDUP/spfiletst.ora’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora””;
}
executing Memory Script
Starting backup at 21-DEC-10
Finished backup at 21-DEC-10
sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0/db_10/dbs/spfiletststby.ora”
contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/tststby/adump” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”+DGDUP/tststby/controlfile/current.285.738230295”, ”+DGDUP/tststby/controlfile/current.288.738230295” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=tststbyXDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”tststby” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”+DGDUP/tst”, ”+DGDUP/tststby” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”+DGDUP/tst”, ”+DGDUP/tststby” comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”tststby” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”tst” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(tst,tststby)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_2 =
”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst” comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/tststby/adump” comment= ”” scope=spfile
sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.285.738230295”, ”+DGDUP/tststby/controlfile/current.288.738230295” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=tststbyXDB)” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”tststby” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”+DGDUP/tst”, ”+DGDUP/tststby” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”+DGDUP/tst”, ”+DGDUP/tststby” comment= ”” scope=spfile
sql statement: alter system set fal_client = ”tststby” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”tst” comment= ”” scope=spfile
sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile
sql statement: alter system set log_archive_config = ”dg_config=(tst,tststby)” comment= ”” scope=spfile
sql statement: alter system set log_archive_dest_2 = ”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst” comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 327155864 bytes
Database Buffers 188743680 bytes
Redo Buffers 3809280 bytes
allocated channel: tststby
channel tststby: SID=30 device type=DISK
contents of Memory Script:
{
sql clone “alter system set control_files =
”+DGDUP/tststby/controlfile/current.366.738351783”, ”+DGDUP/tststby/controlfile/current.367.738351783” comment=
”Set by RMAN” scope=spfile”;
backup as copy current controlfile for standby auxiliary format ‘+DGDUP/tststby/controlfile/current.368.738351783’;
restore clone controlfile to ‘+DGDUP/tststby/controlfile/current.369.738351783’ from
‘+DGDUP/tststby/controlfile/current.368.738351783’;
sql clone “alter system set control_files =
”+DGDUP/tststby/controlfile/current.368.738351783”, ”+DGDUP/tststby/controlfile/current.369.738351783” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.366.738351783”, ”+DGDUP/tststby/controlfile/current.367.738351783” comment= ”Set by RMAN” scope=spfile
Starting backup at 21-DEC-10
channel tst: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_10/dbs/snapcf_tst1.f tag=TAG20101221T174303 RECID=34 STAMP=738351787
channel tst: datafile copy complete, elapsed time: 00:00:07
Finished backup at 21-DEC-10
Starting restore at 21-DEC-10
channel tststby: copied control file copy
Finished restore at 21-DEC-10
sql statement: alter system set control_files = ”+DGDUP/tststby/controlfile/current.368.738351783”, ”+DGDUP/tststby/controlfile/current.369.738351783” comment= ”Set by RMAN” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 327155864 bytes
Database Buffers 188743680 bytes
Redo Buffers 3809280 bytes
allocated channel: tststby
channel tststby: SID=29 device type=DISK
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
“+dgdup”;
switch clone tempfile all;
set newname for datafile 1 to
“+dgdup”;
set newname for datafile 2 to
“+dgdup”;
set newname for datafile 3 to
“+dgdup”;
set newname for datafile 4 to
“+dgdup”;
set newname for datafile 5 to
“+dgdup”;
backup as copy reuse
datafile 1 auxiliary format
“+dgdup” datafile
2 auxiliary format
“+dgdup” datafile
3 auxiliary format
“+dgdup” datafile
4 auxiliary format
“+dgdup” datafile
5 auxiliary format
“+dgdup” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +dgdup in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 21-DEC-10
channel tst: starting datafile copy
input datafile file number=00001 name=+DGDUP/tst/datafile/system.287.738229555
channel tst1: starting datafile copy
input datafile file number=00002 name=+DGDUP/tst/datafile/sysaux.282.738229779
output file name=+DGDUP/tststby/datafile/sysaux.371.738351819 tag=TAG20101221T174338
channel tst1: datafile copy complete, elapsed time: 00:02:25
channel tst1: starting datafile copy
input datafile file number=00003 name=+DGDUP/tst/datafile/undotbs1.281.738229967
output file name=+DGDUP/tststby/datafile/system.370.738351819 tag=TAG20101221T174338
channel tst: datafile copy complete, elapsed time: 00:02:55
channel tst: starting datafile copy
input datafile file number=00005 name=+DGDUP/tst/datafile/undotbs2.306.738231147
output file name=+DGDUP/tststby/datafile/undotbs1.372.738351989 tag=TAG20101221T174338
channel tst1: datafile copy complete, elapsed time: 00:00:24
channel tst1: starting datafile copy
input datafile file number=00004 name=+DGDUP/tst/datafile/users.280.738229991
output file name=+DGDUP/tststby/datafile/undotbs2.373.738352009 tag=TAG20101221T174338
channel tst: datafile copy complete, elapsed time: 00:00:23
output file name=+DGDUP/tststby/datafile/users.374.738352019 tag=TAG20101221T174338
channel tst1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 21-DEC-10
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like “+DGDUP/tst/archivelog/2010_12_21/thread_1_seq_32.375.738352033” auxiliary format
“+DGDUP” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 21-DEC-10
channel tst: starting archived log copy
input archived log thread=1 sequence=32 RECID=58 STAMP=738352034
output file name=+DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043 RECID=0 STAMP=0
channel tst: archived log copy complete, elapsed time: 00:00:01
Finished backup at 21-DEC-10
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: +dgdup/TSTSTBY/ARCHIVELOG/2010_12_21/thread_1_seq_32.376.738352043
File Name: +dgdup/TSTSTBY/DATAFILE/SYSTEM.370.738351819
File Name: +dgdup/TSTSTBY/DATAFILE/SYSAUX.371.738351819
File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS1.372.738351989
File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS2.373.738352009
File Name: +dgdup/TSTSTBY/DATAFILE/USERS.374.738352019
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.359.738350563
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.360.738350567
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.361.738350567
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.362.738350567
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.366.738351783
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.367.738351783
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: +dgdup/TSTSTBY/ARCHIVELOG/2010_12_21/thread_1_seq_32.376.738352043
File Name: +dgdup/TSTSTBY/DATAFILE/SYSTEM.370.738351819
File Name: +dgdup/TSTSTBY/DATAFILE/SYSAUX.371.738351819
File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS1.372.738351989
File Name: +dgdup/TSTSTBY/DATAFILE/UNDOTBS2.373.738352009
File Name: +dgdup/TSTSTBY/DATAFILE/USERS.374.738352019
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.361.738350567
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.362.738350567
List of Files Which Where Not Cataloged
=======================================
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.359.738350563
RMAN-07517: Reason: The file header is corrupted
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.360.738350567
RMAN-07517: Reason: The file header is corrupted
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.366.738351783
RMAN-07517: Reason: The file header is corrupted
File Name: +dgdup/TSTSTBY/CONTROLFILE/Current.367.738351783
RMAN-07517: Reason: The file header is corrupted
List of files in Recovery Area not managed by the database
==========================================================
File Name: +DGDUP/tststby/controlfile/current.368.738351783
RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
number of files not managed by recovery area is 1, totaling 9.56MB
datafile 1 switched to datafile copy
input datafile copy RECID=41 STAMP=738352049 file name=+DGDUP/tststby/datafile/system.370.738351819
datafile 2 switched to datafile copy
input datafile copy RECID=42 STAMP=738352049 file name=+DGDUP/tststby/datafile/sysaux.371.738351819
datafile 3 switched to datafile copy
input datafile copy RECID=43 STAMP=738352051 file name=+DGDUP/tststby/datafile/undotbs1.372.738351989
datafile 4 switched to datafile copy
input datafile copy RECID=44 STAMP=738352051 file name=+DGDUP/tststby/datafile/users.374.738352019
datafile 5 switched to datafile copy
input datafile copy RECID=45 STAMP=738352052 file name=+DGDUP/tststby/datafile/undotbs2.373.738352009
contents of Memory Script:
{
set until scn 1303335;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-DEC-10
starting media recovery
archived log for thread 1 with sequence 32 is already on disk as file +DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043
archived log file name=+DGDUP/tststby/archivelog/2010_12_21/thread_1_seq_32.376.738352043 thread=1 sequence=32
media recovery complete, elapsed time: 00:00:02
Finished recover at 21-DEC-10
Finished recover at 21-DEC-10
Finished Duplicate Db at 21-DEC-10
released channel: tst
released channel: tst1
released channel: tststby
RMAN>
6.. Create a pfile /tmp/inittststby2.ora from the spfiletststby.ora. Mount from the pfile; CReate a spfie for DG Broker to function properly; Make sure that both datavases are RACED after the stadby creation.
SQL> create spfile from memory;
File created.
SQL> !env | grep ORA
ORACLE_UNQNAME=RACDB
ORACLE_SID=tststby2
ORACLE_HOSTNAME=raclinux2.gj.com
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_10
SQL>
7. Test Standby database
SQL> select PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE
——————– ——————– ——————– —————-
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY
SQL>
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
SQL> select * from v$archive_gap;
no rows selected
SQL>
SQL> select PROCESS, CLIENT_PROCESS , GROUP#, THREAD#, SEQUENCE# from v$managed_standby;
PROCESS CLIENT_P GROUP# THREAD# SEQUENCE#
——— ——– —————————————- ———- ———-
ARCH ARCH N/A 0 0
ARCH ARCH N/A 0 0
ARCH ARCH N/A 0 0
ARCH ARCH N/A 0 0
MRP0 N/A N/A 1 35
SQL>
select sequence#, first_time, next_time, applied from v$archive_log order by sequence#;
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
———- ———
12 YES
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 IN-MEMORY
21 NO
22 NO
SEQUENCE# APPLIED
———- ———
23 NO
24 NO
25 NO
26 NO
27 NO
28 NO
29 NO
30 NO
31 NO
32 NO
32 YES
SEQUENCE# APPLIED
———- ———
33 YES
33 NO
34 NO
34 YES
35 YES
35 NO
36 YES
36 NO
37 NO
37 YES
38 YES
SEQUENCE# APPLIED
———- ———
38 NO
39 YES
40 YES
41 NO
42 NO
43 NO
44 NO
45 NO
46 NO
47 NO
48 NO
SEQUENCE# APPLIED
———- ———
49 NO
50 NO
51 NO
52 NO
53 NO
54 NO
55 NO
56 NO
57 NO
58 NO
59 NO
SEQUENCE# APPLIED
———- ———
60 NO
61 NO
57 rows selected.
SQL> select process, client_process, group#, thread#, sequence# from v$managed_standby;
PROCESS CLIENT_P GROUP# THREAD#
——— ——– —————————————- ———-
SEQUENCE#
———-
ARCH ARCH 11 2
38
ARCH ARCH 7 1
60
ARCH ARCH N/A 0
0
PROCESS CLIENT_P GROUP# THREAD#
——— ——– —————————————- ———-
SEQUENCE#
———-
ARCH ARCH 8 1
61
RFS ARCH N/A 0
0
RFS UNKNOWN N/A 0
0
PROCESS CLIENT_P GROUP# THREAD#
——— ——– —————————————- ———-
SEQUENCE#
———-
RFS UNKNOWN N/A 0
0
RFS ARCH N/A 0
0
RFS UNKNOWN N/A 0
0
PROCESS CLIENT_P GROUP# THREAD#
——— ——– —————————————- ———-
SEQUENCE#
———-
RFS UNKNOWN N/A 0
0
RFS LGWR 6 2
39
RFS LGWR 2 1
62
PROCESS CLIENT_P GROUP# THREAD#
——— ——– —————————————- ———-
SEQUENCE#
———-
MRP0 N/A N/A 1
62
13 rows selected.
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
———- ———
12 YES
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
SEQUENCE# APPLIED
———- ———
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES
31 YES
32 YES
32 YES
SEQUENCE# APPLIED
———- ———
33 YES
33 YES
34 YES
34 YES
35 YES
35 YES
36 YES
36 YES
37 YES
37 YES
38 YES
SEQUENCE# APPLIED
———- ———
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
SEQUENCE# APPLIED
———- ———
49 YES
50 YES
51 YES
52 YES
53 YES
54 YES
55 YES
56 YES
57 YES
58 YES
59 YES
SEQUENCE# APPLIED
———- ———
60 YES
61 IN-MEMORY
57 rows selected.
8.
Download RAC Data Guard setup and management with Oracle 11gR2
46 Comments »
Leave a Reply
-
OraNA
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Thank you for your good documentation.
oracle password file name is not correct.
this file name must be from “orapwtststby” to “orapwststby”
Thank you.
Hello,
orapwtststby is the correct name. Both instances are tst and tststby. There is no instance ststby. Take a note that this prior to RAC enabling the standby database.
Regards,
i mean that “t” word is not correct.
it must be orapwt -> orapw
Hello,
You said that ‘this file name must be from “orapwtststby” to “orapwststby”. The instance before RACing is tststby not ststby. Threrefore it is orapw+SID.
Regards,
Ok.
Now i clearly understand :).
In step 4, the static registrations to the scan listener and local listener, should the SID_NAME be tst1/tst2 rather than tst as you have above?
Hello,
You are dealing with a database not an instance.
Regards,
Hi, this document is very helpful. I do have a question. (I am not savy in RAC, so this question may be silly)
At step 10:. Did you set cluster_database=TRUE in memory before you do anything in step 10?
like “ALTER SYSTEM set cluster_database=TRUE scope=MEMORY”
Thank you very much.
Sean
Hi,
Ste 10 is
Register the standby database with OCR. Create a shared ASM based spfile.. Make sure that *.cluster_database=TRUE and each instance pfile points to the ASM based spfile. See Annex 6 for more information.
So you need to set cluster_database=TRUE
Regards,
Hi,
Where are the annexes?
Regards
Hi,
Look at the article bottom.
Regards,
There’s nothing 😦
Thought it could be firefox, but I have tried IE as well, no annexes on the bottom.
Regards
Hi,
Look here https://gjilevski.wordpress.com/2010/12/24/rac-data-guard-setup-and-management-with-oracle-11gr2-11-2-0-2/
Start from the bottom. The formating is nessed up while uploading. I can try to put a pdf.
Annex
1.Primary database configuration
2.Database online redo logfile and standby redo log files
3.Changes to listener.ora and tnsnames.ora
5. Create a static entry in the GI scan listener
……..
Regards,
Hi,
Can you download the pdf that attached for you?
Regards,
PERFECT!!! thank you very much
Hi Guenadi,
I am kind of stuck at instance startup on standby database, as when I startup the instance using the pfile created from the primary database, the instance startups up with status BLOCKED.
Here are the contents from the related files :
vi /u01/app/11.2.0/grid/network/admin/listener.ora
[CODE]LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Sols.localdomain)
(SID_NAME = Sols)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = Sol.localdomain)
(SID_NAME = Sol)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = Sol_DGMGRL.localdomain)
(SID_NAME = Sol)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = Sols_DGMGRL.localdomain)
(SID_NAME = Sols)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)[/CODE]
vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
[CODE]# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SOLS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sols-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Sols.localdomain) (UR=A)
)
)
SOL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sol-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Sol.localdomain) (UR=A)
)
)[/CODE]
vi /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSols1.ora (PFILE)
[CODE]Sols2.__db_cache_size=222298112
Sols1.__db_cache_size=226492416
Sols2.__java_pool_size=4194304
Sols1.__java_pool_size=4194304
Sols2.__large_pool_size=12582912
Sols1.__large_pool_size=12582912
Sols2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
Sols2.__pga_aggregate_target=327155712
Sols1.__pga_aggregate_target=327155712
Sols2.__sga_target=616562688
Sols1.__sga_target=616562688
Sols2.__shared_io_pool_size=0
Sols1.__shared_io_pool_size=0
Sols2.__shared_pool_size=360710144
Sols1.__shared_pool_size=360710144
Sols2.__streams_pool_size=4194304
Sols1.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/Sols/adump’
*.audit_trail=’db’
*.cluster_database=false
*.compatible=’11.2.0.0.0′
*.control_files=’+DGDATA/sols/controlfile/current.256.807733739′,’+DGFRA/sol/controlfile/current.256.807733745′
*.db_block_size=8192
*.db_create_file_dest=’+DGDATA’
*.db_domain=’localdomain’
*.db_name=’Sols’
*.db_recovery_file_dest=’+DGFRA’
*.db_recovery_file_dest_size=15728640000
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SolsXDB)’
Sols1.instance_number=1
Sols2.instance_number=2
*.log_archive_config=’dg_config=(Sol,Sols)’
*.log_archive_dest_2=’service=Sols.localdomain SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Sols’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=943718400
*.open_cursors=300
*.processes=150
*.remote_listener=’sols-scan:1521′
*.remote_login_passwordfile=’exclusive’
Sols2.thread=2
Sols1.thread=1
Sols2.undo_tablespace=’UNDOTBS2′
Sols1.undo_tablespace=’UNDOTBS1′[/CODE]
lsnrctl status
[CODE]LSNRCTL for Solaris: Version 11.2.0.3.0 – Production on 26-FEB-2013 01:57:39
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Solaris: Version 11.2.0.3.0 – Production
Start Date 26-FEB-2013 01:37:37
Uptime 0 days 0 hr. 20 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/Sol3/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.22)(PORT=1521)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM1”, status READY, has 1 handler(s) for this service…
Service “Sol.localdomain” has 1 instance(s).
Instance “Sol”, status UNKNOWN, has 1 handler(s) for this service…
Service “Sol_DGMGRL.localdomain” has 1 instance(s).
Instance “Sol”, status UNKNOWN, has 1 handler(s) for this service…
Service “Sols.localdomain” has 2 instance(s).
Instance “Sols”, status UNKNOWN, has 1 handler(s) for this service…
Instance “Sols1”, status BLOCKED, has 1 handler(s) for this service… <<——–
Service "Sols_DGMGRL.localdomain" has 1 instance(s).
Instance "Sols", status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully[/CODE]
Since the instance is blocked I cannot get RMAN to connect to the AUXILIARY.
Can you please help me out as to what may be causing this problem.
Thanks for your help and all your articles are excellent.
Regards
Terry
Hi,
What is the error that you are getting? Did you have different db_name and db_unique_name for the standby? Where exactly do you face the error at standby creation or at what step? What do you do to get the error? Was it working before? What did you do?
Best Regards,
Hi Guenadi,
Thanks for getting back to me, I am at the step of creating a standby database, I have managed to connect to the Auxiliary Instance by modifying the static listener, but now I am facing the new problem, when I run your script in RMAN where after altering the parameters RMAN shuts down the Auxiliary Instance and then tries to start it up again but the Auxiliary Instance remains shut.
[CODE]contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/Sols/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=SolsXDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”Sols” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”+DATA/Sol”, ”+DGDATA/Sols” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”+DATA/Sol”, ”+DGDATA/Sols”, ”+FRA/Sol”, ”+DGFRA/Sols” comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”Sols” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”Sol” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(Sol,Sols)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_2 =
”service=Sol SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Sol” comment=
”” scope=spfile”;
shutdown clone immediate; <<<———
startup clone nomount; <<<———
}[/CODE]
[b] Here is the RMAN error: [/b]
[CODE]RMAN-06402: Oracle instance shut down
RMAN-08031: released channel: Sol
RMAN-08031: released channel: Sol2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/27/2013 16:30:37
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor[/CODE]
db_name and db_unique_name are the same for standby, followings are the modified contents of the RMAN script according to my setup:
[CODE]run {
allocate channel Sol type disk;
allocate channel Sol2 type disk;
allocate auxiliary channel Sols type disk;
duplicate target database for standby from active database DORECOVER
spfile
parameter_value_convert "Sol","Sols"
set db_unique_name="Sols"
set db_file_name_convert="+DATA/Sol","+DGDATA/Sols"
set log_file_name_convert="+DATA/Sol","+DGDATA/Sols","+FRA/Sol","+DGFRA/Sols"
set fal_client="Sols"
set fal_server="Sol"
set standby_file_management="AUTO"
set log_archive_config="dg_config=(Sol,Sols)"
set log_archive_dest_2="service=Sol SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=Sol";}[/CODE]
Followings are the static listener' contents on standby i.e. Sols :
[b]vi /u01/app/11.2.0/grid/network/admin/listener.ora [/b]
[CODE]LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Sol3.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Sols.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Sols1)
)
(SID_DESC =
(GLOBAL_DBNAME = Sol3_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Sols1)
)
)[/CODE]
Kindly study the above information and please let me know where things are going wrong.
Thank you very much for your help.
Regards
Terry
Hi Terry,
I would look at the ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Try to work it around. Do you have the problem if you try connection from sqlplus? Look at a way to resolve the error.
Best Regards,
Hi Terry,
Make sure that SID is the same in both the listener.ora and tnsnames.ora. Test the connectivity with sqlplus.
What is the output of ‘lsnrctl services’ at present?
For example:
TSTSTBY
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID_NAME = tststby)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tst)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
(SID_DESC =
(GLOBAL_DBNAME = tst_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tst)
)
(SID_DESC =
(GLOBAL_DBNAME = tststby_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)
(SID_NAME = tststby)
)
)
Best Regards,
Thanks Guenadi, I did reconfigure the listener again and the standby creation went through fine, will now move ahead with the remaining steps.
Its so nice of you to get back to me so fast !!!
Appreciate your help.
Best regards.
Terry
Reblogged this on Mike Desouza's Blog and commented:
Dataguard setup for 11gr2
Hi,
Thanks for uploading potential information, however i would like to understand about listener configuration on tst_DGMGR , why are we configuring static entries with this values in listener.ora
Appreciate if you can revert back on this.
Hi,
You need static registrattion due to a bug ,you can look into MOS site or in the 11gR2 Dataguard documentation or here http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-rac-standby-133152.pdf, so that to be able to use dgmgrl to start and stop and instance.
Best Regards,
Guenadi Jilevski
Hi Guenadi, I have a 3 Node Primary RAC database (11.2.0.3) and only 1 node RAC for standby database (it is not a 1 node RAC, it is regular RAC with only 1 node). We are not looking to implement dataguard broker. Would the above process change in our case? Do we still need to set cluster_database=FALSE even though only 1 node is available on standby. Would setting of any of the primary database parameters require downtime? Thanks a lot.
Best Regards,
Hi,
This is a generic procedure.
You need to temporarily set cluster_database=FALSE during the clone only. After that decide if you will be using the standby as RAC or non-RAC. If you want to have a RAC enabled database set it to TRUE in order to be able to add instance later if this is what you are planing. If you want to keep it non-RAC tan you can skip it.
Best Regards,
Hi Guenadi, we have a 3 node RAC cluster as primary and 1 node RAC for standby (it is not a 1 node RAC, but it is regular RAC with only 1 node since we have hardware limitations). We are not planning on implementing Dataguard broker. Would the above process change in our case? Do we still need to set the cluster_database parameter on the standby side? Would we need a downtime for setting up any of the parameters on the primary database, we have not set any standby related parameters yet.
Thanks a lot!
Hi,
IT depends on which parameters are you changing on the primary site.
If you use the cloning that copies the spfile you will need to change the cluster_database parameter on the standby site. Only prior to the clone set cluster_database =false to the standby, see below the modification.
run {
allocate channel tst type disk;
allocate channel tst1 type disk;
allocate auxiliary channel tststby type disk;
duplicate target database for standby from active database DORECOVER
spfile
parameter_value_convert “tst”,”tststby”
set db_unique_name=”tststby”
set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”
set fal_client=”tststby”
set fal_server=”tst”
set cluster_database =false
set standby_file_management=”AUTO”
set log_archive_config=”dg_config=(tst,tststby)”
set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}
Thanks a lot Guenadi! Appreciate your prompt response!
Hi Guenadi, I am having trouble connecting to the standby database from the primary. The remote connection to primary db works fine from the standby:
I am getting the below error message while connecting to the standby from primary and standby from standby:
>sqlplus sys/oss0dba@PROD101STBY as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 5 14:45:08 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
>tnsping PROD101STBY
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production on 05-JUN-2013 15:42:25
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/oracle/g01/software/rdbms/11.2.0.3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = scan2.domain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD101STBY)))
OK (30 msec)
Below are my listener and tnsnames.ora files from primary and standby:
GI Home Listener.ora on Primary:
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
)
SID_LIST_LISTENER_SCAN2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
)
SID_LIST_LISTENER_SCAN3 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
)
DB Home tnsnames.ora on Primary:
PROD101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan1.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD101)
)
)
PROD101STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan2.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD101STBY)
)
)
GI Home Listener.ora on Standby:
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
)
SID_LIST_LISTENER_SCAN2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
)
SID_LIST_LISTENER_SCAN3 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101STBY)
)
(SID_DESC =
(GLOBAL_DBNAME = PROD101)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
(SID_NAME = PROD101)
)
)
DB Home tnsnames.ora on Standby:
PROD101STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan2.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD101STBY)
)
)
PROD101 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan1.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD101)
)
)
Appreciate your help. Thanks!
Hi,
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Star by looking into
lsnrctl services output
and try to find out why it does not match.
Best Regards,
Hi Guenadi, I did already check the services output, but it doesn’t even show up in it, below is my output:
On Standby:
>lsnrctl services
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production on 06-JUN-2013 13:18:44
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
On Primary:
>lsnrctl services
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production on 06-JUN-2013 13:18:55
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary…
Service “+ASM” has 1 instance(s).
Instance “+ASM1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “PROD101” has 1 instance(s).
Instance “PROD1011”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:1585 refused:0 state:ready
LOCAL SERVER
Service “PROD101XDB” has 1 instance(s).
Instance “PROD1011”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=host1)(PORT=33067))
The command completed successfully
Appreciate your help. Thanks.
Hi,
Check what is wrong with the listener file on the standby site and why the service is not there. Start from here and trouble shoot further. I bet it is a configuration issue somewhere.
Best Regards,
Thanks a lot Guenadi, I will check the listener file on the standby site.
I did a reload after changing the listener.ora file. Do I need to bounce the standby cluster so that the listener and scan listener gets restarted and registers?
Thanks!
Hi,
It is not necessary to bounce it, but you can do it if it is a testing sandbox env.
Bet Regards,
Hi Guenadi, I changed by lisetner entry to the following on standby:
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = ENSP101STBY)
(ORACLE_HOME = /oracle/g01/software/rdbms/11.2.0.3)
)
)
Didn’t change the scan one’s yet, since I wanted to try with one first.
Then, I tried to start the listener and it did start as below:
>lsnrctl start LISTENER
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production on 06-JUN-2013 16:42:09
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /oracle/g01/software/grid/11.2.0.3/bin/tnslsnr: please wait…
TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production
System parameter file is /oracle/g01/software/grid/11.2.0.3/network/admin/listener.ora
Log messages written to /oracle/g01/admin/diag/tnslsnr/hoststby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 – Production
Start Date 06-JUN-2013 16:42:11
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/g01/software/grid/11.2.0.3/network/admin/listener.ora
Listener Log File /oracle/g01/admin/diag/tnslsnr/hoststby/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary…
Service “PROD101STBY” has 1 instance(s).
Instance “PROD101STBY”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
When I try to connect, it still gives me this error:
>sqlplus sys/password@PROD101STBY as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 6 16:45:26 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
Thanks! Appreciate your help and quick responses.
Hi,
What is the PROD101STBY definition in the tnsnames.ora on both sites? Try to match it to the services in the listener.
Regards,
ORA-12154: TNS:could not resolve the connect identifier specified
Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Action: – If you are using local naming (TNSNAMES.ORA file):
– Make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
– Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
– Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
– Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
– If you are using directory naming:
– Verify that “LDAP” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
– Verify that the LDAP directory server is up and that it is accessible.
– Verify that the net service name or database name used as the connect identifier is configured in the directory.
– Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier
– If you are using easy connect naming:
– Verify that “EZCONNECT” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
– Make sure the host, port and service name specified are correct.
– Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
We absolutely love your blog and find nearly all of your post’s to be exactly I’m looking for.
Would you offer guest writers to write content in your case?
I wouldn’t mind publishing a post or elaborating on a lot of the subjects you write about here.
Again, awesome weblog!
Hello,
I am myself am writing. I do not publish somebody else’s content. Thank you.
Best Regards,
Guenadi Jilevski
Truly when someone doesn’t be aware of then its up too other viewrs that they will
help, so here it occurs.
To keep you in the most comfortable and peaceful state of mind possible, Dr.
Nathan Newman is world renowned for his advanced cosmetic surgery procedures.
Unlike other muscles in your body, your facial exercise are connected
as skin and bone, forming a cushion for the skin to relax.
gjilevski.com has potential, you can make your page go viral easily using one tricky method. Just type in google:
Sulingi’s Method To Go Viral
Hi Guenadi,
i)Usually we copy password file from primary to standby but in this post you created password files for standby is it OK?
ii)Let us assume stand by is running on RAC then each stand by instance should point to separate primary instance or all stand by instances should point to same primary instance to get archive/redo logs
Hi Guenadi,
i)Usually we copy password file from primary to standby but in this post you created password files for standby is it OK?
ii)Let us assume stand by is running on RAC then each stand by instance should point to separate primary instance or all stand by