11gR2 RAC to Single DataGuard

primary database: RAC db_name=sdb db_unique_name=sdb
standby database: noRAC db_name=sdb db_unique_name=stb

1. primary database (rac)

Enable Forced Logging

SQL> ALTER DATABASE FORCE LOGGING;

Copy password file to standby node

[oracle@rac1 dbs]$ scp  orapwsdb1 oracle@dataguard:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwsdb 

Change parameter in primary node:

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(sdb,stb)' sid='*'; 
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=stb lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stb' sid='*';
SQL> alter system set FAL_SERVER=stb sid='*';
SQL> alter system set db_file_name_convert='+DATA/stb','+DATA/sdb' scope=spfile sid='*';
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO
[grid@rac1 ~]$ srvctl stop database -d sdb   
[grid@rac1 ~]$ srvctl stop database -d sdb -o mount
SQL> alter database archivelog;
[grid@rac1 ~]$ srvctl stop database -d sdb
[grid@rac1 ~]$ srvctl start database -d sdb

Create pfile and copy to standby node

SQL> create pfile from spfile;
[oracle@rac1 dbs]$scp initsdb1.ora oracle@dataguard:/u01/app/oracle/product/11.2.0.4/db_1/dbs/initsdb.ora

Backup database and copy backup set to standby node

run{
allocate CHANNEL ch00  type DISK  ; 
backup database format '/u01/app/oracle/backup/full_%U';
release channel ch00;
}

create standby controlfile and copy to standby node;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/stb.ctl';

Standby database: (single instance)

create dump directory:

[oracle@dataguard dbs]$ mkdir -p /u01/app/oracle/admin/sdb/adump
[oracle@dataguard dbs]$ mkdir -p /u01/app/oracle/admin/sdb/bdump
[oracle@dataguard dbs]$ mkdir -p /u01/app/oracle/admin/sdb/udump
[oracle@dataguard dbs]$ mkdir -p /u01/app/oracle/admin/sdb/hdump

Create spfile 

####################################################
*.audit_file_dest='/u01/app/oracle/admin/sdb/adump'
*.audit_trail='DB'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/product/11.2.0.4/db_1/dbs/stb.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/sdb','+DATA/stb'
*.db_name='sdb'
*.db_unique_name='stb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4194304000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sdbXDB)'
*.fal_server='SDB'
*.log_archive_config='DG_CONFIG=(stb,sdb)'
*.log_archive_dest_2='SERVICE=sdb lgwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sdb'
*.log_file_name_convert='+DATA/sdb','+DATA/stb'
*.memory_target=3G
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#######################################################
SQL> startup nomount

SQL> create spfile from pfile;

SQL> shutdown immediate;

SQL> startup mount;

Restore database

RMAN> restore database; 

configure tnsnames.ora in primary and standby nodes: (tnsname.ora file should under oracle account)

#################################
sdb1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdb)
    )
  )


sdb2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdb)
    )
  )


stb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dataguard)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdb)
    )
  )


sdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sdb)
    )
  )
#########################################

Create standby logfile in standby node

alter database add standby logfile thread 1
    group 5 size 50M,
    group 6 size 50M,
    group 7 size 50M;
alter database add standby logfile thread 2
   -- group 8 size 50M,
    group 9 size 50M,
    group 10 size 50M;

Start Redo Apply.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


SQL>SELECT SQEUENCE#,STATUS,PROCESS FROM V$MANAGED_STANDBY;
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

ACTIVE DATABASE TO OPEN READ ONLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SQL>  select open_mode from v$database;


OPEN_MODE
--------------------
READ ONLY WITH APPLY