ACTIVE STANDBY DATABASE 11g R2

Oracle 11g R2 Active Standby Database.

Part:1


SET LINE 32000 PAGESIZE 32000

ALTER DATABASE FORCE LOGGING;

 select * from v$pwfile_users;

col size format a15 heading "LOG SIZE"
SELECT ((BYTES)/1024/1024)||' MB' AS  "SIZE" FROM V$LOG;

col member format a100 heading "REDO DESTINATION"
select group#, member from v$logfile ORDER BY 1;


ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCL_REDO07.LOG' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCL_REDO08.LOG' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ORCL_REDO09.LOG'SIZE 50M;

select * from v$standby_log;

shu immediate

create spfile from pfile='<ORCL.ORA>';

startup

Pfile:- ORCL database Primary

orcl.__db_cache_size=159383552
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=222298112
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=243269632
orcl.__streams_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\Administrator\oradata\orcl\control01.ctl','D:\app\Administrator\oradata\orcl\control02.ctl','D:\app\Administrator\oradata\orcl\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='D:\app\Administrator\flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=639631360
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

#PARAMETER FOR PUNEDBY CONFIGURATION

db_name='ORCL'
db_unique_name='ORCL'
instance_name='ORCL'
service_names='ORCL'
#dg_broker_start='TRUE'
log_archive_config='DG_CONFIG=(ORCL,PUNE)'
log_archive_dest_1='LOCATION=D:\Archive\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'
log_archive_dest_2='SERVICE=PUNE LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=PUNE'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
FAL_CLIENT='ORCL'
FAL_SERVER='PUNE'
DB_FILE_NAME_CONVERT='C:\PUNE\','D:\app\Administrator\oradata\orcl'
LOG_FILE_NAME_CONVERT='C:\PUNE\','D:\app\Administrator\oradata\orcl'
standby_file_management='AUTO'
log_archive_format='ARC_%t_%s_%r.dbf'
log_archive_max_processes=30
log_archive_trace=100
fast_start_mttr_target=300

Part: 2

oradim -new -sid pune -startmode manual -intpwd oracle

set oracle_sid=pune

sqlplus sys@pune as sysdba

create spfile from pfile='<PUNE.ORA>';

startup mount

Pfile:- PUNE database Primary

pune.__db_cache_size=159383552

pune.__java_pool_size=4194304

pune.__large_pool_size=4194304

pune.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment

pune.__pga_aggregate_target=222298112

pune.__sga_target=419430400

pune.__shared_io_pool_size=0

pune.__shared_pool_size=243269632

pune.__streams_pool_size=0

*.audit_file_dest='D:\app\Administrator\admin\pune\adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='C:\PUNE\control01.ctl','C:\PUNE\control02.ctl'

*.db_recovery_file_dest='C:\PUNE\flash_recovery_area\' 

*.db_block_size=8192

*.db_domain=''

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='C:\pune\'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=puneXDB)'

*.memory_target=639631360

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

#PARAMETER FOR STANDBY CONFIGURATION

db_name='ORCL'

db_unique_name='PUNE'

instance_name='PUNE'

service_names='PUNE'

#dg_broker_start='TRUE'

log_archive_config='DG_CONFIG=(ORCL,PUNE)'

log_archive_dest_1='LOCATION=C:\PUNE\archivelog\ MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PUNE'

log_archive_dest_2='SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=ORCL'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

FAL_CLIENT='PUNE'

FAL_SERVER='orcl'

DB_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl','C:\PUNE\'

LOG_FILE_NAME_CONVERT='D:\app\Administrator\oradata\orcl','C:\PUNE\'

standby_file_management='AUTO'

log_archive_format='ARC_%t_%s_%r.dbf'

log_archive_max_processes=30

log_archive_trace=100

fast_start_mttr_target=300

RMAN>

RMAN target sys@orcl

connect auxiliary sys@pune

duplicate target database for standby from active database ;

========================================================================

ELSE PART

If You should try to fully switchover database (read /write standby database) concept then drop the standby log on standby site when the database on mount stage because recommend to swithover database.

alter database drop logfile group 4;

alter database drop logfile group 5;

alter database drop logfile group 6;

alter database drop logfile group 7;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\app\Administrator\oradata\orcl\ORCL_REDO04.LOG',

'D:\app\Administrator\oradata\orcl\ORCL_REDO04_A.LOG') SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5  ('D:\app\Administrator\oradata\orcl\ORCL_REDO05.LOG',

'D:\app\Administrator\oradata\orcl\ORCL_REDO05_A.LOG') SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6  ('D:\app\Administrator\oradata\orcl\ORCL_REDO06.LOG',

'D:\app\Administrator\oradata\orcl\ORCL_REDO06_A.LOG') SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7  ('D:\app\Administrator\oradata\orcl\ORCL_REDO07.LOG',

'D:\app\Administrator\oradata\orcl\ORCL_REDO07_A.LOG') SIZE 100M;

========================================================================

select open_mode from v$database;

alter database recover managed standby database using current logfile disconnect from session;

select sequence#,applied from v$archived_log order by 1;          both database 

alter database recover managed standby database cancel;

alter database open read only ;

real time apply

alter database recover managed standby database using current logfile disconnect from session;  

Part: 3

Snapshot database

http://vinaycustomizepc.blogspot.in/2012/07/snapshot-standby-database.html



Reference

https://mehmeteser.wordpress.com/2013/08/25/11g-create-dataguard-using-oracle-managed-files-and-rman/