source and target database should be same configuration
primary
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME
--------- -------------------- ---------------- ----------------
Ram READ WRITE PRIMARY Ram
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ rm hc_apac.dat
[oracle@rac1 dbs]$ ls -ltr
total 20
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Jan 6 22:46 lkW5005PR
-rw-r----- 1 oracle oinstall 1536 Jan 6 22:47 orapww5005pr
-rw-r----- 1 oracle oinstall 41 Jan 6 22:48 initw5005pr.ora
-rw-rw---- 1 oracle oinstall 1544 Jan 6 22:48 hc_w5005pr.dat
[oracle@rac1 dbs]$
configure standby redolog on primary
SQL> set lines 180
col MEMBER for a60
select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;SQL> SQL>
THREAD# GROUP# MEMBER BYTES
---------- ---------- ------------------------------------------------------------ ----------
1 3 +DATA01/Ram/ONLINELOG/group_3.259.1018027407 209715200
1 3 +FLASH01/Ram/ONLINELOG/group_3.1590.1018027409 209715200
1 2 +DATA01/Ram/ONLINELOG/group_2.267.1018027407 209715200
1 2 +FLASH01/Ram/ONLINELOG/group_2.1589.1018027409 209715200
1 1 +DATA01/Ram/ONLINELOG/group_1.260.1018027407 209715200
1 1 +FLASH01/Ram/ONLINELOG/group_1.1591.1018027409 209715200
6 rows selected.
ALTER DATABASE ADD standby logfile thread 1 group 4 ( '+DATA01','+FLASH01') SIZE 200m;
SQL> ALTER DATABASE ADD standby logfile thread 1 group 5 ('+DATA01','+FLASH01') size 200m;
Database altered.
SQL> ALTER DATABASE ADD standby logfile thread 1 group 6 ('+DATA01','+FLASH01') size 200m;
Database altered.
SQL> ALTER DATABASE ADD standby logfile thread 1 group 7 ('+DATA01','+FLASH01') size 200m;
Database altered.
SQL> show parameter pfile;
NAME TYPE VALUE
---------- ----------- --------------------------------
spfile string +DATA/w5005pr/spfilew5005pr.ora
SQL> create pfile='/home/oracle/initw5005pr.ora.bkp' from spfile;
File created.
SQL> alter system set db_unique_name='Ram' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(Ram,RamSBY)' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RamSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RamSBY' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET fal_client=Ram scope=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=RamSBY scope=both;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> create pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRam' from spfile;
File created.
SQL>
Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The best practice for this is to copy the passwordfile as suggested.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME
copy the initora file to standylocation and change it accordingly.
prod pfile
Ram.__data_transfer_cache_size=0
Ram.__db_cache_size=22817013760
Ram.__inmemory_ext_roarea=0
Ram.__inmemory_ext_rwarea=0
Ram.__java_pool_size=268435456
Ram.__large_pool_size=335544320
Ram.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment
Ram.__pga_aggregate_target=3422552064
Ram.__sga_target=26910654464
Ram.__shared_io_pool_size=536870912
Ram.__shared_pool_size=2885681152
Ram.__streams_pool_size=0
*.audit_file_dest='/oraclebase/app/oracle/admin/Ram/adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_files='+DATA01/Ram/CONTROLFILE/current.261.1018026219','+FLASH01/Ram/CONTROLFILE/current.256.1018026219'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_create_online_log_dest_1='+DATA01'
*.db_create_online_log_dest_2='+FLASH01'
*.db_name='Ram'
*.db_recovery_file_dest_size=100006m
*.db_recovery_file_dest=''
*.db_unique_name='Ram'
*.diagnostic_dest='/oraclebase/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RamXDB)'
*.fal_client='Ram'
*.fal_server='RamSBY'
*.local_listener='LISTENER_Ram'
*.log_archive_config='DG_CONFIG=(Ram,RamSBY)'
*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram'
*.log_archive_dest_2='SERVICE=RamSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RamSBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=3201m
*.processes=1280
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=25602m
*.standby_file_management='AUTO'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'
standby pfile
RamSBY.__data_transfer_cache_size=0
RamSBY.__db_cache_size=22817013760
RamSBY.__inmemory_ext_roarea=0
RamSBY.__inmemory_ext_rwarea=0
RamSBY.__java_pool_size=268435456
RamSBY.__large_pool_size=335544320
RamSBY.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment
RamSBY.__pga_aggregate_target=3422552064
RamSBY.__sga_target=26910654464
RamSBY.__shared_io_pool_size=536870912
RamSBY.__shared_pool_size=2885681152
RamSBY.__streams_pool_size=0
*.audit_file_dest='/oraclebase/app/oracle/admin/RamSBY/adump'
*.audit_trail='db'
*.compatible='18.0.0'
*.control_files='+DATA01','+FLASH01'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_create_online_log_dest_1='+DATA01'
*.db_create_online_log_dest_2='+FLASH01'
*.db_name='Ram'
*.db_recovery_file_dest_size=100006m
*.db_recovery_file_dest=''
*.db_unique_name='RamSBY'
*.diagnostic_dest='/oraclebase/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RamSBYXDB)'
*.fal_client='RamSBY'
*.fal_server='Ram'
*.local_listener='LISTENER_RamSBY'
*.log_archive_config='DG_CONFIG=(RamSBY,Ram)'
*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RamSBY'
*.log_archive_dest_2='SERVICE=Ram LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Ram'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=3201m
*.processes=1280
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=25602m
*.standby_file_management='AUTO'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'
***********************************************
mkdir -p /u01/app/oracle/admin/Ramsby/adump
startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';
create spfile='+DATA01/RamSBY/spfileRamSBY.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';
startup nomount force;
show parameter pfile
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
--Example for two members
--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.log') SIZE 200M;
--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;
configure listener and tnsnames
srvctl modify scan_listener -p TCP:1523
srvctl modify listener -p TCP:1523
Verify connection ‘AS SYSDBA’ from Primary
Verify connection ‘AS SYSDBA’ from Standby
Run the duplicate from active database command from primary
rman target sys/ChangeMe2019@Ram auxiliary sys/ChangeMe2019@RamSBY
duplicate target database for standby from active database nofilenamecheck;
once completed
"alter database mount standby database;
database altered;"
alter database recover managed standby database disconnect from session;
alter system set log_archive_dest_state_2=defer;
on primary
select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log where archived='YES';
MAX(SEQUENCE#)
--------------
10
standyb
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;
NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME
--------- -------------------- ---------------- ----------------
ddd READ ONLY WITH APPLY PHYSICAL STANDBY standy
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
6 07-JAN-16 07-JAN-16
7 07-JAN-16 07-JAN-16
8 07-JAN-16 07-JAN-16
9 07-JAN-16 07-JAN-16
10 07-JAN-16 07-JAN-16
SQL> select max(sequence#) from v$archived_log where applied='YES';
select process,status,sequence#,thread# from v$managed_standby;
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;
System altered.
SQL> ALTER SYSTEM SET log_archive_dest_state_2 =enable';
ALTER SYSTEM SET log_archive_dest_state_2 =enable'
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;
System altered.
[oracle@rac2 ~]$ which srvctl
/u01/app/oracle/product/11.2.0/db_1/bin/srvctl
[oracle@rac2 ~]$ srvctl add database -d w5005prg -o /u01/app/oracle/product/11.2.0/db_1/ -r physical_standby -s 'READ ONLY'
[oracle@rac2 ~]$ srvctl start database -d w5005prg
[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
applying retention policy for rman on both primary and standby
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
or
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
On Primary Site :
Connect with sys and :
SQL>Alter system set "_log_deletion_policy" = 'ALL' scope=both;
The Archivelogs will be deleted on stanby site when a "Backup Database occurs in Primary Site" !!! To be confirmed
SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
No comments:
Post a Comment