Estoy enviando un procedimiento que realice para configurar un standby lógico en Oracle 9iR2, veo que algunos lo necesitan, espero les sirva.

Saludos,

#######################################

# CONFIGURACION DE UN STANDBY LOGICO

#

# BASE DE DATOS PRIMARIA: oracle9i

# BASE DE DATOS STANDBY : stdbylog

# SERVIDOR PRIMARIO : DB003

# SERVIDOR SECUNDARIO : CONT003

# NOTE: 180031.1

# NOTE: 68537.1

#######################################

EN EL PRIMARIO

==============

0.SQL>@d:oracleora92rdbmsadmincatpatch.sql

SQL>@d:oracleora92rdbmsadminutlrp.sql

1.SQL> ALTER DATABASE FORCE LOGGING;

2.Modificar el archivo de parametros:

INIT.ORA

========

COMPATIBLE=9.2.0.1.0

DB_NAME=oracle9i

INSTANCE_NAME=oracle9i

LOG_ARCHIVE_DEST_1=’LOCATION=d:oracleoradataoracle9iArchive MANDATORY’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

log_archive_dest_2=’SERVICE=stdbyphy LGWR SYNC=NOPARALLEL AFFIRM’

log_archive_dest_state_2=ENABLE

#LOG_ARCHIVE_DEST_3=’SERVICE=STDBYLOG LGWR ASYNC’

#LOG_ARCHIVE_DEST_STATE_3=ENABLE

LOG_ARCHIVE_FORMAT="%t_%s.dbf"

LOG_ARCHIVE_START=TRUE

LOG_PARALLELISM=1

REMOTE_ARCHIVE_ENABLE=TRUE

STANDBY_FILE_MANAGEMENT=AUTO

4.SQL> SHUTDOWN IMMEDIATE;

SQL> create spfile from pfile=’d:oracleadminoracle9ipfileinit.ora’;

SQL> STARTUP;

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

6.SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED

WHERE OWNER=’OE’ AND TABLE_NAME = ‘CUSTOMERS’;

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

8.SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

9.SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

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

11.SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

12.SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

13.SQL> CREATE TABLESPACE logmnrts DATAFILE ‘d:oracleoradataoracle9ilogmnrts.dbf’ SIZE 25M AUTOEXTEND ON MAXSIZE UNLIMITED;

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘logmnrts’);

14.SQL> SELECT NAME FROM V$DATAFILE;

15.SQL> SHUTDOWN IMMEDIATE;

16.Bajar el servicio de windows: OracleServiceOracle9i

Con el explorador de Windows copiar desde:

d:oracleoradataoracle9isystem01.dbf hacia d:oracleoradatastdbylogsystem01.dbf

…..

Levantar el servicio de windows: OracleServiceOracle9i

17.SQL> STARTUP MOUNT;

18.SQL> ALTER DATABASE BACKUP CONTROLFILE TO ‘d:tempcontrol01.ctl’;

19.SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

20.SQL> ALTER DATABASE OPEN;

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

21.SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

22.SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

23.SQL> SELECT NAME FROM V$ARCHIVED_LOG

WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE DICTIONARY_BEGIN = ‘YES’ AND STANDBY_DEST= ‘NO’));

D:ORACLEORADATAORACLE9IARCHIVE1_25.DBF

Tomar nota, para luego usarlo en standby.

24.SQL> CREATE PFILE=’d:tempinit.ora’ FROM SPFILE;

25.Backup datafiles and control.

Latest archived redo log

Database initialization parameter

EN EL STANDBY

=============

!!! ADVERTENCIA: SI EL STANDBY LOGICO Y FISICO SE ENCUENTRAN EN EL MISMO HOST

ES NECESARIO BAJAR LA BASE DE DATOS DEL STANDBY FISICO, Y COMENTAR

EN EL LISTENER LA ENTRADA DEL STANDBY FISICO Y VOLVER A REINICIAR

EL SERVICIO DE LISTENER (OracleOraHome92TNSListener) PARA EVITAR

TENER CONFLICTO CON LOS SERVICIOS DE NOMBRES DEL STANDBY LOGICO.

UNA VEZ QUE SE HA TERMINADO DE CAMBIAR EL NOMBRE DEL STANDBY LOGICO

CON LA UTILIDAD "NID", RECIEN SE VUELVE A DESCOMENTAR EN EL ARCHIVO

LISTENER.ORA LA ENTRADA REFERIDA AL STANDBY FISICO Y SE REINICIA

EL SERVICIO DEL LISTENER.

26. INIT.ORA

---

db_name=oracle9i

compatible=9.2.0.1.0

instance_name=stdbylog

log_archive_start=TRUE

log_archive_format="%t_%s.dbf"

log_archive_dest_1=’LOCATION=d:oracleoradatastdbylogarchive’

log_archive_dest_state_1=ENABLE

log_archive_dest_2=’SERVICE=oracle9i LGWR SYNC=NOPARALLEL AFFIRM’

log_archive_dest_state_2=ENABLE

log_parallelism=1

parallel_max_servers=9

standby_archive_dest=’d:oracleoradatastdbylogarchive’

# The following parameter is required only if the primary and standby databases

# are located on the same system.

# lock_name_space=stdbylog

27.d:> oradim -NEW -SID stdbylog -STARTMODE auto

d:> del d:oracleora92databasePWDstdbylog.ora

d:> orapwd file=d:oracleora92databasePWDstdbylog.ora password=oracle entries=5

28.d:> lsnrctl stop

d:> lsnrctl start

29.SQLNET.ORA

---

SQLNET.EXPIRE_TIME=2

30.Crear entradas en el TNSNAMES.ORA

31.SQL> STARTUP MOUNT PFILE=’d:oracleadminstdbylogpfileinit.ora’;

32.SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iCWMLITE01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGCWMLITE01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iDRSYS01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGDRSYS01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iEXAMPLE01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGEXAMPLE01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iODM01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGODM01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iSYSTEM01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGSYSTEM01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iINDX01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGINDX01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iTOOLS01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGTOOLS01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iUSERS01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGUSERS01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iUNDOTBS01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGUNDOTBS01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iXDB01.DBF’ TO ‘D:ORACLEORADATASTDBYLOGXDB01.DBF’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iREDO01.LOG’ TO ‘D:ORACLEORADATASTDBYLOGREDO01.LOG’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iREDO02.LOG’ TO ‘D:ORACLEORADATASTDBYLOGREDO02.LOG’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iREDO03.LOG’ TO ‘D:ORACLEORADATASTDBYLOGREDO03.LOG’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iSTBY_REDO01.LOG’ TO ‘D:ORACLEORADATASTDBYLOGSTBY_REDO01.LOG’;

SQL>ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAoracle9iSTBY_REDO02.LOG’ TO ‘D:ORACLEORADATASTDBYLOGSTBY_REDO02.LOG’;

33.SQL> ALTER DATABASE GUARD ALL;

SQL> ALTER DATABASE OPEN RESETLOGS;

34.SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT PFILE=’d:oracleadminstdbylogpfileinit.ora’;

35.d:>set ORACLE_SID=stdbylog

d:>nid TARGET=SYS/oracle DBNAME=stdbylog

d:>del d:oracleora92databasePWDstdbylog.ora

d:>orapwd file=d:oracleora92databasePWDstdbylog.ora password=oracle entries=5

36.Modificar Init.ora (standby)

…

db_name=stdbylog

…

37.SQL> SHUTDOWN IMMEDIATE;

SQL> CREATE SPFILE FROM PFILE=’d:oracleadminstdbylogpfileinit.ora’;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE OPEN RESETLOGS;

38.SQL>SELECT * FROM V$TEMPFILE;

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

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘d:oracleoradatastdbylogtemp01.dbf’ SIZE 40M REUSE;

39.SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE

‘D:ORACLEORADATASTDBYLOGARCHIVE1_12.DBF’;

40.SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

41.SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

EN EL PRIMARIO

==============

42.SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=’SERVICE=STDBYLOG LGWR SYNC’ SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;

43.SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;