Create Logical Standby Database on Oracle Database 9i


Primary:
=======

SQL> Grant LOGSTDBY_ADMINISTRATOR to SYS with admin option;
SQL> Grant SELECT_CATALOG_ROLE to SYS with admin option;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/oracle/RP1/oraarch
2> MANDATORY’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=SPFILE;

SQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED
2> ORDER BY OWNER, TABLE_NAME;

SQL> SELECT COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
2> WHERE OWNER=’[owner]’ AND TABLE_NAME = ’[table_name]’;

SQL> SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
2> WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
YES YES

SQL> CREATE TABLESPACE logmnrts DATAFILE ’/oracle/oradata/RP1/logmnrts.dbf’
2> SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(’logmnrts’);

SQL> SELECT NAME FROM V$DATAFILE;

SQL> SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;

SQL> SHUTDOWN IMMEDIATE;

[backup copy datafile, logfile, passwordfile]

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO
2> '/mnt/hgfs/shared/RP1/control_RP3.ctl';

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER DATABASE OPEN;
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

SQL> SELECT NAME FROM V$ARCHIVED_LOG
2> WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
3> WHERE DICTIONARY_BEGIN = ’YES’ AND STANDBY_DEST= ’NO’));
/oracle/RP1/oraarch/1_158.dbf

SQL> CREATE PFILE=’/full/path/to/pfile.ora’ FROM SPFILE;

[Copy backup datafile, logfile, controlfile, passwordfile from primary to standby]

Standby:
=======

[Vi pfile primary untuk standby and edit:
–    Control_files=
–    Standby_archive_dest= (tidak boleh sama dengan archive_dest_1
–    Log_archive_dest_1
–    [Other parameter sesuai dengan env standby]
–    *.parallel_max_servers=9
–    *.fal_client=’stby’
–    *.fal_server=’prim’
–    *.standby_archive_dest=’location=/oracle/RP1/stbyarch/’
–    *.standby_file_management=’AUTO’

Parameter primary:
–    *.parallel_max_servers=9
–    *.fal_client=’RP1′
–    *.fal_server=’RH2′
–    *.standby_archive_dest=’location=/oracle/stbyarch/’
–    *.standby_file_management=’AUTO’
–    *.db_file_name_convert=’/oracle/oradata/primary/’,’/oracle/oradata/RP1/’
–    *.log_file_name_convert=’/oracle/oraarch/’,’/oracle/RP1/oraarch/’
–    *.log_archive_start=TRUE

[configure listener to connect primary and standby]

Vi sqlnet.ora
SQLNET.EXPIRE_TIME=2

SQL> STARTUP MOUNT PFILE=initRP3.ora;

SQL> ALTER DATABASE RENAME FILE ’/full/path/to/primary/datafile.dbf’
2> TO ’/full/path/to/standby/datafile.dbf’;

[semua datafile]

SQL> select name from v$datafile;

SQL> ALTER DATABASE RENAME FILE ’/full/path/to/primary/logfile.log’
2> TO ’/full/path/to/standby/logfile.log’;

SQL> select * from v$logfile;

SQL> ALTER DATABASE GUARD ALL;
SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT PFILE=initRP3.ora;

nid TARGET=SYS/oracle@RP3 DBNAME=RP3

vi initRP3.ora
–    db_name=RP3

SQL> SHUTDOWN IMMEDIATE;

SQL> CREATE SPFILE FROM PFILE=initRP3.ora;

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> SELECT * FROM V$TEMPFILE;
no rows selected

Jika ada tempfile, drop semuanya dengan command:
SQL> ALTER DATABASE TEMPFILE ’tempfilename’ DROP;

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE
2> CONTENTS =’TEMPORARY’;
TABLESPACE_NAME
--------------------------------
TEMP

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE
2> ’/disk1/oracle/oradata/payroll/standby/temp01.dbf’
3> SIZE 40M REUSE;

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE
2> '/full/path/to/archivelog/1_158.dbf’;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

PRIMARY
=======
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RP1’ SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

STANDBY
=======
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
2> FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';

SQL> COLUMN STATUS FORMAT A50
SQL> COLUMN TYPE FORMAT A12
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

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

%d bloggers like this: