Setting up Dataguard in 12c (inc active data guard)

See Document http://docs.oracle.com/database/121/SBYDB/create_ps.htm#SBYDB5017

NOTE: My Primary is jhcdb and my new standby will be jhstby

I think the hardest part of this is setting everything back if it goes wrong.

1. enable force logging on primary (jhcdb).

SQL> ALTER DATABASE FORCE LOGGING;

2. Set the primary to receive redo (this will alow the existing database to switch roles).
Make sure the file size matches existing redo logs (select members, bytes/1024/1024 from v$log;)

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog01.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('C:\app\oracle\oradata\jhcdb\slog02.rdo') SIZE 50M;

3. Define additional init params for redo transport services on the existing primary
-- already set

DB_NAME=jhcdb
DB_UNIQUE_NAME=jhcdb
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHCDB\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHCDB\CONTROL02.CTL'

-- Require setting

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhcdb' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=jhstby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhstby' scope=spfile;
alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile;

-- Standby role parameters

alter system set FAL_SERVER=jhstby scope=spfile;
alter system set DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

4. Enable archiving, though this should already be done. But its worth at least shutting down
because you've likely changed the LOG_ARCHIVE_DEST_1

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

5. For the creation of the database the following steps are required and the book mark listed is from the oracle doc (see top).

reference below task database

Section 3.2.1 - Create a Backup Copy of the Primary Database Data Files Primary
Section 3.2.2 6 Create a Control File for the Standby Database Primary
Section 3.2.3 7,8 Create a Parameter File for the Standby Database Primary
Section 3.2.4 9 Copy Files from the Primary System to the Standby System Primary
Section 3.2.5 Set Up the Environment to Support the Standby Database Standby
Section 3.2.6 Start the Physical Standby Database Standby
Section 3.2.7 Verify the Physical Standby Database Is Performing Properly Standby

6. Create a backup copy of the control file. (jhcdb - Primary)

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\app\tmp\jhcdb.ctl';

7. Create a control file for the standby database

SQL> CREATE PFILE='c:\app\tmp\initjhstby.ora' FROM SPFILE;

8. Amend the necessary parameters to make the init file suitable for the new standby database

DB_NAME='jhcdb'
DB_UNIQUE_NAME='jhstby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(jhcdb,jhstby)'
CONTROL_FILES='C:\APP\ORACLE\ORADATA\JHSTBY\CONTROL01.CTL', 'C:\APP\ORACLE\FAST_RECOVERY_AREA\JHSTBY\CONTROL02.CTL'
DB_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_FILE_NAME_CONVERT='\jhcdb\','\jhstby\'
LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jhstby'
LOG_ARCHIVE_DEST_2='SERVICE=jhcdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jhcdb'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=jhcdb

Not sure what to do about the other parameters, I did change the audit dest.

9. Shutdown jhcdb and copy the files

SQL> shutdown immediate

create C:\app\oracle\audit\jhstby

copy C:\app\oracle\oradata\jhcdb to C:\app\oracle\oradata\jhstby

**** copy the orapwd file from jhcdb to jhstby (when I created one, the redo log transfer kept failing with ORA-01017 in the arc1 trace file).

set oracle_sid=jhstby
copy the control file create earlier to both locations.
run orapwd file=PWDjhstby.ora password=oracle entries=10

SQL-JHSTBY> STARTUP MOUNT
SQL-JHSTBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

10. Startup the primary

SQL> startup
SQL> select max(sequence#) from v$loghist;

11. Check

SQL-JHSTBY> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
SQL-JHCDB> SELECT MAX(SEQUENCE#) FROM V$LOGHIST;


12. Making it active data guard.
SQL-JHSTBY> recover managed standby database cancel;
SQL-JHSTBY> alter database open read only;
SQL-JHSTBY> recover managed standby database disconnect using current log file;

And hey presto, it's open in read only mode. Try this

On jhcdb, create table jh1 (adate date);
Then on JHSTBY, desc jh1 ..... And you should get n immediate result.

But remember active data guard is separately licenceable .



Happyjohn