Some key points before proceeding with the physical standby setup.
Database software installed on both servers with enterprise edition version and directory structure of fra also.
Both servers are pinging.
Primary database should be in archivelog mode.
Forced Logging is on in Primary database (sql>alter database force logging;)
Initialization parameter “db_name” should be same on both primary and standby database.
Initialization parameter “db_unique_name” should be different on primary and standby databases.
Keep everywhere database name in CAPITAL.
Primary Database : PRIM
Standby Database : STND
- Check if the primary database is using the password file or not. If not, then create one as below.
orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y
- Add the following parameters in the initialization parameter file of the primary database.
initprim.ora
prim.__db_cache_size=46137344
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base=’/u01/home/oracle’#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest=’/u01/home/oracle/admin/prim/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.diagnostic_dest=’/u01/home/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.db_unique_name=’prim’
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=prim’
*.log_archive_dest_2=’service=stnd NOAFFIRM ASYNC
valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
- Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.
Primary:
listener.ora file
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
tnsnames.ora file
tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
Standby:
listener.ora file
listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = stnd)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
tnsnames.ora file
tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)(UR=A)
)
)
STND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stnd)(UR=A)
)
)
- Add the following parameters in the initialization parameter file of the standby database
initstnd.ora
stnd.__db_cache_size=230686720
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stnd/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/app/oracle/oradata/stnd/control01.ctl’,’/u02/app/oracle/oradata/stnd/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/fra’
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
*.log_archive_dest_1=’location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=stnd’
*.log_archive_dest_2=’service=prim ASYNC
valid_for=(online_logfiles,primary_role)
db_unique_name=prim’
*.db_unique_name=’stnd’
*.db_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.log_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u02/app/oracle/oradata/stnd/’
*.standby_file_management=AUTO
*.FAL_SERVER=’prim’
*.FAL_CLIENT=’stnd’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer
- Now copy the password file of the primary database “orapwprim” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwstnd”.
Note that the format of the password file in Windows is as pwd.ora (Example: pwdPRIM.ora)
and also copy the control file to two locations mentioned in the pfile and create those directories on standby with permissions.
- Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).
rman target sys/@prim auxiliary sys/@stnd
Now perform the duplicate operation to create the standby database.
rman>duplicate target database for standby from active database nofilenamecheck;
Note: (If getting error) RMAN-04006: ERROR FROM AUXILIARY DATABASE: ORA-01031: INSUFFICIENT PRIVILEGES
Changes
You may have tried to add the connecting user to the password file but that has failed:
SQL> select * from v$pwfile_users;
No rows
SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01109: database not open
Because the auxiliary is nomounted, the addition of a user to the password file for the auxiliary is not allowed, nor does it resolve this problem.
You have verified the same problem happens in SQL*Plus as does RMAN. For example, if in RMAN you are issuing:
RMAN> connect auxiliary @
then for testing SQL*Plus you’d have to issue this from the operating system prompt:
% sqlplus “@ as sysdba”
also shows the ORA-1031 error.
(connecting in two steps to the auxiliary is not a valid test).
Cause
There may be a mismatch in the “case” of characters between these items on the host where the auxiliary database resides:
1) The entry in the listener.ora file for service_name for the auxiliary instance.
2) The entry for db_name in the init.ora file for the auxiliary instance.
3) The naming of the password file.
4) The $ORACLE_SID for the auxiliary instance.
If all four items have matching case characters, you may find the errors connecting remotely via the password file are resolved.
Solution
Set the reference to service_name in the listener.ora, db_name in the init.ora, the password file name itself, and the $ORACLE_SID to all uppercase.
Listener example:
(DESCRIPTION=
(ADDRESS=…)
(ADDRESS=…)
(CONNECT_DATA=
(SERVICE_NAME=SALES.US.ACME.COM)))
The init.ora file:
db_name=SALES
The password file:
orapwSALES.ora or orapwSALES
The $ORACLE_SID:
$ ORACLE_SID=SALES; export ORACLE_SID
========================================================================================================================
- Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.
sqlplus sys/@stnd as sysdba
Check the status of the standby database by making sure it is in mount stage.
sql>select status,instance_name,database_role from v$instance,v$database;
- Now start the managed recovery process on the standby database.
sql>alter database recover managed standby database disconnect from session;
- Now check if the managed recovery process (MRP) has been started on the standby database or not.
sql>select process,status,sequence# from v$managed_standby;
Example:
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 39
Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the MRP0 under the process column.
- On the primary database, perform a few log switches and check if the logs are applied to the standby database.
sqlplus sys/@prim as sysdba
sql>alter system switch logfile;
sql>select max(sequence#) from v$archived_log;
Example:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
38
sqlplus sys/@stnd as sysdba
sql>select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
Example:
SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 38
Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.
Here you go !!!
Ref http://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ follow the below steps to setup the active dataguard.:
===============================================================================================================================================
How to setup active dataguard in oracle 11g
The main advantage of setting up dataguard in 11g is that the database can be opened in Read-Only mode allowing the Users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode. In other words, the physical standby database would be in recovery mode and hand in hand the standby database can be used for reporting purposes.
The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed
Any Data Manipulation Language (DML) except for select statements
Any Data Definition Language (DDL)
Access of local sequences
DMLs on local temporary tablesSteps on how to setup the active dataguard:
Once you setup the physical standby database as described in http://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ follow the below steps to setup the active dataguard.
Step 1:
Check the status of the Primary database and the latest sequence generated in the primary database.
SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS INSTANCE_NAME DATABASE_ROLE
OPEN prim PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
40
Step 2:
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
MOUNTED stnd PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
40
Step 3:
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9 rows selected.
Check to see if the archive logs are being shipped and applied to standby. An easy way to tell is to query the V$ARCHIVE_DEST_STATUS.
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
OPEN stnd PHYSICAL STANDBY READ ONLY
Step 6:
Now start the MRP on the physical standby database.
SQL> alter database recover managed standby database nodelay disconnect from session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 41
RFS IDLE 0
RFS IDLE 0
RFS IDLE 0
MRP0 WAIT_FOR_LOG 41
9 rows selected.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
You can also verify total number of log sequence generated and applied on DR site, by below SQL query:
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SQL> select message from v$dataguard_status;
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Here you go !!
Ref:http://shivanandarao-oracle.com/2012/03/19/how-to-setup-active-dataguard-in-oracle-11g/
***In case of you are facing any kind of error than following SQL query will help you to diagnose it.
SQL> select dest_name,status,error from v$archive_dest where dest_name=’LOG_ARCHIVE_DEST_2′;
or
SQL> select message from v$dataguard_status;
Note: LOG_ARCHIVE_DEST_2 should be VALID in order to continue Data Guard sync
Note: This command will give you appropriate message about the dataguard current status.
By default, for a newly created standby database, the primary database is in maximum performance mode.
