RAC to single standby – revisited

Hi, today I want to show how I think it’s possible to migrate a RAC to single.

  1. First let’s identify free disks
    [root@vmware oracle]# lsblk 
    NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
    sda                     8:0    0   20G  0 disk 
    ├─sda1                  8:1    0  500M  0 part /boot
    └─sda2                  8:2    0 19.5G  0 part 
      ├─ol_vmware-root    252:0    0 17.5G  0 lvm  /
      └─ol_vmware-swap    252:1    0    2G  0 lvm  [SWAP]
    sdb                     8:16   0  100G  0 disk 
    └─sdb1                  8:17   0  100G  0 part 
      └─vg_oracle-product 252:2    0  100G  0 lvm  /u01
    sdc                     8:32   0   30G  0 disk 
    sdd                     8:48   0   20G  0 disk 
    sr0                    11:0    1 56.3M  0 rom 
    [root@vmware oracle]# fdisk /dev/sdc 
    [root@vmware oracle]# fdisk /dev/sdd
    [root@vmware ~]$ cat /etc/udev/rules.d/99-oracle.rules 
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB2060caf8-3d055279", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="oinstall", MODE="0660"
    KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB0d4b5c6c-5b81abe5", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="oinstall", MODE="0660"
    [root@vmware rules.d]# systemctl restart systemd-udevd

  2. Install GI and DB Home

  3. Prepare your spfile

    [oracle@vmware dbs]$ strings spfileORCL.ora 
    *.audit_file_dest='/u01/app/oracle/admin/ORCL_SITE2/adump'
    *.thread=1
    *.audit_trail='db','extended'
    *.cluster_database=false
    *.compatible='12.1.0.2.0'
    *.control_file_record_keep_time=90
    *.db_block_size=8192
    *.standby_file_management='AUTO'
    *.db_create_file_dest='+DATA'
    *.db_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.db_name='ORCL'
    *.db_recovery_file_dest='+FRA'
    *.db_recovery_file_dest_size=10g
    *.db_securefile='ALWAYS'
    *.db_unique_name='ORCL_SITE2'
    *.diagnostic_dest='/u01/app/oracle'
    *.log_file_name_convert='ORCL_SITE1','ORCL_SITE2'
    *.pga_aggregate_target=200m
    *.remote_login_passwordfile='exclusive'
    *.sga_target=600m
    *.undo_tablespace='UNDOTBS1'
    

  4. Prepare listener

    [oracle@vmware admin]$ cat listener.ora
    # listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2_grid/network/admin/listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = vmware.example.com)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = ORCL_SITE2_DGMGRL.example.com)
          (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2)
          (SID_NAME = ORCL)
        )
      )
    
    ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON		# line added by Agent
    VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET		# line added by Agent
    
    [oracle@vmware admin]$ srvctl stop listener
    [oracle@vmware admin]$ srvctl start listener
    [oracle@vmware admin]$ lsnrctl services
    
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:37:56
    
    Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    Services Summary...
    Service "ORCL_SITE2_DGMGRL.example.com" has 1 instance(s).
      Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    The command completed successfully
    

  5. Create a passwordfile

  6. Define your connect identifiers

    [oracle@vmware admin]$ cat tnsnames.ora 
    ORCL_SITE1 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    ORCL_SITE2 = (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE2_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=vmware.example.com)(PORT=1521)))
    

  7. Once you can login from these aliases without problems you can duplicate the DB

    [oracle@vmware dbs]$ tnsping orcl_site1
    
    TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 14-FEB-2017 11:46:22
    
    Copyright (c) 1997, 2014, Oracle.  All rights reserved.
    
    Used parameter files:
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL_SITE1_DGMGRL))(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan.example.com)(PORT=1521)))
    OK (0 msec)
    [oracle@vmware dbs]$ rman target=sys/oracle@orcl_site1 auxiliary=sys/oracle@orcl_site2
    
    Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 14 11:46:24 2017
    
    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: ORCL (DBID=145047211)
    connected to auxiliary database: ORCL (not mounted)
    
    RMAN> duplicate target database for standby from active database dorecover ;
    Starting Duplicate Db at 14-FEB-17
    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
    current log archived
    
    contents of Memory Script:
    {
       backup as copy reuse
       targetfile  '+DATA/ORCL_SITE1/PASSWORD/pwdorcl_site1.260.935837221' auxiliary format 
     '/u01/app/oracle/product/12.1.0.2/dbs/orapwORCL'   ;
    }
    executing Memory Script
    
    Starting backup at 14-FEB-17
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=57 instance=ORCL2 device type=DISK
    Finished backup at 14-FEB-17
    
    contents of Memory Script:
    {
       sql clone "alter system set  control_files = 
      ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment=
     ''Set by RMAN'' scope=spfile";
       restore clone from service  'orcl_site1' standby controlfile;
    }
    executing Memory Script
    
    sql statement: alter system set  control_files =   ''+DATA/ORCL_SITE2/CONTROLFILE/current.257.935927295'', ''+FRA/ORCL_SITE2/CONTROLFILE/current.256.935927295'' comment= ''Set by RMAN'' scope=spfile
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring control file
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=+DATA/ORCL_SITE2/CONTROLFILE/current.259.935927295
    output file name=+FRA/ORCL_SITE2/CONTROLFILE/current.258.935927295
    Finished restore at 14-FEB-17
    
    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 
     "+DATA";
       set newname for datafile  2 to 
     "+DATA";
       set newname for datafile  3 to 
     "+DATA";
       set newname for datafile  4 to 
     "+DATA";
       set newname for datafile  5 to 
     "+DATA";
       restore
       from service  'orcl_site1'   clone database
       ;
       sql 'alter system archive log current';
    }
    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
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_AUX_DISK_1: starting datafile backup set restore
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    Finished restore at 14-FEB-17
    
    sql statement: alter system archive log current
    current log archived
    
    contents of Memory Script:
    {
       restore clone force from service  'orcl_site1' 
               archivelog from scn  445938;
       switch clone datafile all;
    }
    executing Memory Script
    
    Starting restore at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=35
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=1 sequence=36
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=14
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=15
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_AUX_DISK_1: starting archived log restore to default destination
    channel ORA_AUX_DISK_1: using network backup set from service orcl_site1
    channel ORA_AUX_DISK_1: restoring archived log
    archived log thread=2 sequence=16
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
    Finished restore at 14-FEB-17
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=6 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/system.260.935927305
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/sysaux.261.935927319
    datafile 3 switched to datafile copy
    input datafile copy RECID=8 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs2.262.935927327
    datafile 4 switched to datafile copy
    input datafile copy RECID=9 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/undotbs1.263.935927335
    datafile 5 switched to datafile copy
    input datafile copy RECID=10 STAMP=935927354 file name=+DATA/ORCL_SITE2/DATAFILE/users.264.935927337
    
    contents of Memory Script:
    {
       set until scn  446200;
       recover
       standby
       clone database
        delete archivelog
       ;
    }
    executing Memory Script
    
    executing command: SET until clause
    
    Starting recover at 14-FEB-17
    using channel ORA_AUX_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 35 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347
    archived log for thread 1 with sequence 36 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349
    archived log for thread 2 with sequence 15 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351
    archived log for thread 2 with sequence 16 is already on disk as file +FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_35.259.935927347 thread=1 sequence=35
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_15.262.935927351 thread=2 sequence=15
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_1_seq_36.260.935927349 thread=1 sequence=36
    archived log file name=+FRA/ORCL_SITE2/ARCHIVELOG/2017_02_14/thread_2_seq_16.263.935927353 thread=2 sequence=16
    media recovery complete, elapsed time: 00:00:00
    Finished recover at 14-FEB-17
    Finished Duplicate Db at 14-FEB-17
    
    RMAN> 

  8. Register this database

    srvctl add database -db ORCL_SITE2 -oraclehome /u01/app/oracle/product/12.1.0.2 -domain example.com -spfile /u01/app/oracle/product/12.1.0.2/dbs/spfileORCL.ora -pwfile /u01/app/oracle/product/12.1.0.2/dbs/orapwORCL -role PHYSICAL_STANDBY -startoption mount -dbname ORCL -diskgroup "DATA,FRA"
    srvctl enable database -db ORCL_SITE2

  9. Start dg broker

    SQL> show parameter dg_broker
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    dg_broker_config_file1		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr1ORCL_SITE2.dat
    dg_broker_config_file2		     string	 /u01/app/oracle/product/12.1.0
    						 .2/dbs/dr2ORCL_SITE2.dat
    dg_broker_start 		     boolean	 FALSE
    SQL> alter system set dg_broker_start=true ;
    
    System altered.

  10. Create standby logfiles

    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    alter database add standby logfile thread 1 ;
    
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;
    alter database add standby logfile thread 2 ;

  11. Create DG Broker Configuration

    [oracle@vmware dbs]$ dgmgrl sys/oracle@orcl_site1
    DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
    
    Copyright (c) 2000, 2013, Oracle. All rights reserved.
    
    Welcome to DGMGRL, type "help" for information.
    Connected as SYSDBA.
    DGMGRL> create configuration dg_orcl as PRIMARY DATABASE IS orcl_site1 connect identifier is orcl_site1 ;
    Configuration "dg_orcl" created with primary database "orcl_site1"
    DGMGRL> add database orcl_site2 as connect identifier is orcl_site2 ;
    Database "orcl_site2" added
    DGMGRL> enable configuration ;
    Enabled.

  12. Check Configuration

    DGMGRL> validate database orcl_site1
    
      Database Role:    Primary database
    
      Ready for Switchover:  Yes
    
      Flashback Database Status:
        orcl_site1:  Off
    
    DGMGRL> validate database orcl_site2
    
      Database Role:     Physical standby database
      Primary Database:  orcl_site1
    
      Ready for Switchover:  Yes
      Ready for Failover:    Yes (Primary Running)
    
      Capacity Information:
        Database     Instances        Threads        
        orcl_site1   2                2              
        orcl_site2   1                2              
        Warning: the target standby has fewer instances than the
        primary database, this may impact application performance
    
      Flashback Database Status:
        orcl_site1:  Off
        orcl_site2:  Off
    

And you have now a standby database.

Advertisements

One thought on “RAC to single standby – revisited

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s