primary database: RAC db_name=sdb db_unique_name=sdb
standby database: noRAC db_name=sdb db_unique_name=stb
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
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
allocate CHANNEL ch00 type DISK ;
backup database format '/u01/app/oracle/backup/full_%U';
release channel ch00;
}
[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
####################################################
*.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;
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)
)
)
#########################################
SQL>SELECT SQEUENCE#,STATUS,PROCESS FROM V$MANAGED_STANDBY;
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#
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
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