Monday, 3 February 2020

Creation of 2 Node RAC to non RAC DR (12c)


Steps to create 2 node RAC to non RAC physical standby


  • Kindly follow steps to create standby database using url
  • Make changes on RAC using spfile , So it will reflect on both instances.
  • Make changes on  initstdrac file.
  • Add tns entry on both primary and standby on tnsnames.ora file
  • Restart listener and tnsping to check tns entry
  • start standby database in nomount state using pfile
  • Connect rman using Auxiliary db.
  • Duplicate target database for standby nofilenamecheck
  • Start MRP0 process on standby using recover command at session.

  • Copy password file from ASM to local disk 

ASMCMD> pwd
+DATA/rac/PASSWORD
ASMCMD> ls
pwdrac.256.1088276167
ASMCMD> 
ASMCMD> ls
pwdrac.256.1088276167
ASMCMD> pwcopy pwdrac.256.1088276167 /tmp
copying +DATA/rac/PASSWORD/pwdrac.256.1088276167 -> /tmp/pwdrac.256.1088276167
ASMCMD> 
  • Add standby logfile on primary rac instance 

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 5 ('+DATA/RAC/ONLINELOG/redo05.log') size 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 6 ('+DATA/RAC/ONLINELOG/redo06.log') size 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7 ('+DATA/RAC/ONLINELOG/redo07.log') size 200M;

Database altered.
  • Some parameters used for standby creations  we can change same in pfile or dynamically using spfile.
db_unique_name='rac'
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,racdr)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdr' scope=both sid='*';
alter system set fal_server=racdr scope=both sid='*';
alter system set fal_client=rac scope=both sid='*';
alter system set standby_file_management=auto scope=both sid='*';
alter system set log_file_name_convert='/u01/app/oracle/oradata/racdr/','+DATA/RAC' scope=spfile sid='*';
alter system set db_file_name_convert='/u01/app/oracle/oradata/racdr/','+DATA/RAC' scope=spfile sid='*';

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,racdr)' scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdr' scope=both;

System altered.

SQL> alter system set fal_server=racdr scope=both;

System altered.

SQL> alter system set fal_client=rac scope=both;

System altered.

SQL> alter system set standby_file_management=auto scope=both;

System altered.

SQL> alter system set log_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/racdr' scope=spfile; sid='*';;

System altered.

SQL> alter system set db_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/racdr' scope=spfile; sid='*';

System altered.

SQL> 
  • Stop database instance using srvctl command for changes. 
[root@srv1 dbs]# srvctl status database -d rac
Instance rac1 is running on node srv1
Instance rac2 is running on node srv2
[root@srv1 dbs]# srvctl stop  database -d rac
[root@srv1 dbs]# srvctl start  database -d rac
[root@srv1 dbs]# srvctl status  database -d rac
Instance rac1 is running on node srv1
Instance rac2 is running on node srv2
[root@srv1 dbs]# 


  • Pfile parameter entry in instance rac1 (This is information purpose only  you can make both  entry dynamically and static also) 

 [oracle@srv1 dbs]$ cat initrac1.ora
rac2.__data_transfer_cache_size=0
rac1.__data_transfer_cache_size=0
rac2.__db_cache_size=1207959552
rac1.__db_cache_size=1191182336
rac2.__java_pool_size=16777216
rac1.__java_pool_size=16777216
rac2.__large_pool_size=33554432
rac1.__large_pool_size=33554432
rac2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rac2.__pga_aggregate_target=603979776
rac1.__pga_aggregate_target=603979776
rac2.__sga_target=1795162112
rac1.__sga_target=1795162112
rac2.__shared_io_pool_size=83886080
rac1.__shared_io_pool_size=83886080
rac2.__shared_pool_size=436207616
rac1.__shared_pool_size=452984832
rac2.__streams_pool_size=0
rac1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/rac/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.1.0.2.0'
*.control_files='+DATA/RAC/CONTROLFILE/current.273.1020351747','+FRA/RAC/CONTROLFILE/current.261.1020351747'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='rac'
*.db_unique_name='rac'
*.LOG_ARCHIVE_DEST_2='SERVICE=stdrac LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdrac'
*.fal_server=stdrac
*.fal_client=rac
*.standby_file_management=auto
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stdrac)'
*.log_file_name_convert='/u01/app/oracle/fast_recovery_area/RAC/onlinelog/','+DATA/RAC/ONLINELOG/'
*.db_file_name_convert='/u01/app/oracle/oradata/RAC/','+DATA/RAC/DATAFILE/'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4785m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
rac2.instance_number=2
rac1.instance_number=1
RAC1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.81)(PORT=1521))'
RAC2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.82)(PORT=1521))'
*.log_archive_dest_1='LOCATION=+FRA/RAC/ARCHIVELOG'
*.open_cursors=300
*.pga_aggregate_target=570m
*.processes=300
*.remote_listener='srv-scan:1521'
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan=''
*.sga_target=1710m
rac2.thread=2
rac1.thread=1
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'
[oracle@srv1 dbs]$

#################################################################################


  • Tnsnames.ora

[oracle@srv1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRETAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv-scan.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pretaf)
    )
  )


STDRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv3.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STDRAC)
    )
  )
[oracle@srv1 admin]$

  • Standby Pfile 

[root@srv3 dbs]# cat initSTDRAC.ora
STDRAC.__data_transfer_cache_size=0
STDRAC.__db_cache_size=260046848
STDRAC.__java_pool_size=4194304
STDRAC.__large_pool_size=8388608
STDRAC.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
STDRAC.__pga_aggregate_target=301989888
STDRAC.__sga_target=452984832
STDRAC.__shared_io_pool_size=12582912
STDRAC.__shared_pool_size=159383552
STDRAC.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/STDRAC/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/STDRAC/control01.ctl','/u01/app/oracle/fast_recovery_area/STDRAC/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='RAC'
*.db_unique_name='STDRAC'
*.LOG_ARCHIVE_DEST_2='SERVICE=RAC LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RAC'
*.fal_server=rac
*.fal_client=stdrac
*.standby_file_management=auto
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(stdrac,rac)'
*.log_file_name_convert='+DATA/RAC/ONLINELOG/,'/u01/app/oracle/fast_recovery_area/RAC/onlinelog/'
*.db_file_name_convert='+DATA/RAC/DATAFILE/','/u01/app/oracle/oradata/RAC/'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STDRACXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=720m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
[root@srv3 dbs]#

  • Login standby database and start db using pfile in no mount state 
[oracle@srv3 dbs]$ . oraenv
ORACLE_SID = [STDRAC] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv3 dbs]$[oracle@srv3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 6 19:12:03 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTDRAC.ora';
ORACLE instance started.

Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             566234808 bytes
Database Buffers          180355072 bytes
Redo Buffers                5455872 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  • login primary db with auxiliary instance 

[oracle@srv3 dbs]$  rman target sys/system123@pretaf auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Dec 6 19:12:32 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RAC (DBID=2608333762)
connected to auxiliary database: RAC (not mounted)


  • Run duplicate command for creating standby database 

RMAN> duplicate target database for standby nofilenamecheck;

Starting Duplicate Db at 06-DEC-2019 19:12:55
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:
{
   restore clone standby controlfile;
}
executing Memory Script

Starting restore at 06-DEC-2019 19:12:56
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/rmanbkp/44uimg4j_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/rmanbkp/44uimg4j_1_1 tag=TAG20191205T195915
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/STDRAC/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/STDRAC/control02.ctl
Finished restore at 06-DEC-2019 19:12:58

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
 "+DATA";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/RAC/system.279.1020351597";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/RAC/idata.284.1020709157";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/RAC/sysaux.271.1020351511";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/RAC/undotbs1.270.1020351693";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/RAC/undotbs2.278.1020352203";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/RAC/users.272.1020351691";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/RAC/ilog.285.1020709245";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/RAC/indx.286.1020709561";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-DEC-2019 19:13:03
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/RAC/system.279.1020351597
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/RAC/idata.284.1020709157
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/RAC/sysaux.271.1020351511
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/RAC/undotbs1.270.1020351693
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/RAC/undotbs2.278.1020352203
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/RAC/users.272.1020351691
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/RAC/ilog.285.1020709245
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/RAC/indx.286.1020709561
channel ORA_AUX_DISK_1: reading from backup piece /u01/rmanbkp/ RAC_41uimfth_1_1.bckp
channel ORA_AUX_DISK_1: piece handle=/u01/rmanbkp/ RAC_41uimfth_1_1.bckp tag=TAG20191205T195528
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:05
Finished restore at 06-DEC-2019 19:16:09

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/system.279.1020351597
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/idata.284.1020709157
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/sysaux.271.1020351511
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/undotbs1.270.1020351693
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/undotbs2.278.1020352203
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/users.272.1020351691
datafile 7 switched to datafile copy
input datafile copy RECID=11 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/ilog.285.1020709245
datafile 8 switched to datafile copy
input datafile copy RECID=12 STAMP=1026328569 file name=/u01/app/oracle/oradata/RAC/indx.286.1020709561

Finished Duplicate Db at 06-DEC-2019 19:18:32

RMAN>
RMAN>


###############################################################################

  • Login standby database and perform mrp process and check the same

[oracle@srv3 ~]$ . oraenv
ORACLE_SID = [stdrac] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 11 12:31:54 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on  current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;
select PROCESS,STATUS, SEQUENCE#,THREAD# from v$managed_standby;SQL>
NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
RAC       stdrac           STDRAC                         MOUNTED              PHYSICAL STANDBY NO                 0

SQL> alter database recover managed standby database disconnect from session;

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
MRP0      WAIT_FOR_LOG          2        610
RFS       IDLE                  0          0
RFS       IDLE                  1        583
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  2        610
RFS       IDLE                  0          0

PROCESS   STATUS          THREAD#  SEQUENCE#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0

12 rows selected.

SQL>



Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)