Some key points before proceeding with the physical standby setup.
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.
Primary Database : PRIM
Standby Database : STND
1. 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
2. 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 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stnd' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable
3. 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 = <my_primary_host_name>)(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 = <my_primary_host_name>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = prim)(UR=A) ) ) STND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(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 = <my_standby_host_name>)(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 = <my_primary_host_name>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = prim)(UR=A) ) ) STND = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = stnd)(UR=A) ) )
4. 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 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
5. 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<SID>.ora (Example: pwdPRIM.ora)
6. 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/<password>@prim auxiliary sys/<password>@stnd
Now perform the duplicate operation to create the standby database.
rman>duplicate target database for standby from active database nofilenamecheck;
7. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.
sqlplus sys/<password>@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;
8. Now start the managed recovery process on the standby database.
sql>alter database recover managed standby database disconnect from session;
9. 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.
10. On the primary database, perform a few log switches and check if the logs are applied to the standby database.
sqlplus sys/<password>@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/<password>@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 !!!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Oracle Flashback Database, allows to quickly recover the entire database from logical data corruptions or user errors. It is very similar to PITR (Point In Time Recovery) which puts back the database to a state at a particular time in recent past. However, Flashback Database is much faster than PITR as it does not require restoration of datafiles from the backup. It only requires applying fewer changes from the archived redo logs.
Flashback database can be enabled by following the below steps but before that you need to make sure of the 2 things: Your database needs to be in archivelog mode and you must have FRA(flash recovery area) for your database because the Flashback logs can be stored only in FRA.
Steps to enable Flashback Database:
1. Place your database in mount stage.
- sql>shutdown immediate
- sql>startup mount
2. Set DB_FLASHBACK_RETENTION_TARGET to the requried minutes to have the flashback window. By default, the value would be 1440 minutes (1 day)
- sql>alter system set db_flashback_retention_target=7220; #Here I have set it to 5 days
3. Enable Flashback on the database
- sql>alter database flashback on;
4. You can skip enabling flashback at tablespaces level as shown below
- sql>alter tablespace users flashback off;
5. Finally open the database
-
sql>alter database open;
6. You can check if the database is enabled with Flashback or not
- sql>select flashback_on from v$database;
FLASHBACK_ON
——————
YES
Here you go !!!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.
Here are some suggestions for your first post.
- You can find new ideas for what to blog about by reading the Daily Post.
- Add PressThis to your browser. It creates a new blog post for you about any interesting page you read on the web.
- Make some changes to this page, and then hit preview on the right. You can always preview any post or edit it before you share it to the world.
COPYRIGHT
© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 