Oracle 11g Cross platform Active Standby – Windows Primary database and Linux Active Standby

This note describes the procedure of configuring a cross platform using the 11g RMAN Active Duplicate as well as an Active Standby Database setup over a Windows and Linux platform.

 

The Metalink note Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1] describes the supported cross platform combinations between a primary and standby database.

 

 

The environment used is as follows:

 

Primary

 

Windows 7 64 bit

11g Release 2

 

DB_UNIQUE_NAME=orcl

 

 

Active Standby

 

Oracle Enterprise Linux 5.7 64 bit

11g Release 2

 

DB_UNIUE_NAME=orcl_dr

 

 

  • Add static entry in listener.ora

 

(SID_DESC =

(GLOBAL_DBNAME =orcl_dr)

(ORACLE_HOME =/u02/app/oracle/product/11.2.0/dbhome_1 )

(SID_NAME =orcl_dr)

)

 

  • Reload listener or stop and start listener

 

 

  • Add entries in tnsnames.ora on both source and target

 

ORCL_DR=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = linux01.gavinsoorma.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl_dr)

)

)

 

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = gavin-pc)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

 

  • Create password file on target – ensure same password is used the primary database password file

 

  • Create directory for audit _file_dest

 

  • Create directory for database files

 

    On the Windows server the datafile location is C:\ORADATA|ORCL. On the Linux machine the corresponding location is ‘/u01/oradata/orcl_dr’

 

  • Create directory for log_archive_dest_1 – ‘/u01/oradata/orcl_dr/arch/’

 

  • Create init.ora in $ORACLE_HOME /dbs location with one entry

 

        *.DB_NAME=orcl_dr

 

  • STARTUP NOMOUNT the standby database

 

  • This is the RMAN command used to create a Duplicate from Active Database.

 

Note the db_file_name_convert and log_file_name_convert parameters.

 

 

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

NOFILENAMECHECK

DORECOVER

SPFILE

SET DB_UNIQUE_NAME=”orcl_dr”

SET AUDIT_FILE_DEST=”/u02/app/oracle/admin/orcl_dr/adump”

SET DIAGNOSTIC_DEST=”/u02/app/oracle”

SET LOG_ARCHIVE_DEST_2=”service=orcl_dr LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”

SET FAL_SERVER=”orcl_dr”

SET FAL_CLIENT=”orcl”

SET CONTROL_FILES=’/u01/oradata/orcl_dr/control01.ctl’,’/u01/oradata/orcl_dr/control02.ctl’,’/u01/oradata/orcl_dr/control03.ctl’

SET DB_FILE_NAME_CONVERT=’C:\ORADATA\ORCL\’,’/u01/oradata/orcl_dr/’

SET LOG_FILE_NAME_CONVERT=’C:\ORADATA\ORCL\’,’/u01/oradata/orcl_dr/’;

 

 

Note – the mistake I made here was not setting the parameters LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_FORMAT in the above RMAN Duplicate script

 

That is why we will see archive log files being created on the target like :

 

/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:oradataorclarchARC0000000005_0765492451.0001

 

 

  • From the Primary database run the following command

 

c:\app\gavin\product\11.2.0\dbhome_2\BIN>rman target sys/oracle11g auxiliary sys/oracle11g@orcl_dr

 

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Oct 26 18:22:36 2011

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

 

connected to target database: ORCL (DBID=1293273184)

connected to auxiliary database: ORCL_DR (not mounted)

 

RMAN> DUPLICATE TARGET DATABASE

2> FOR STANDBY

3> FROM ACTIVE DATABASE

4> NOFILENAMECHECK

5> DORECOVER

6> SPFILE

7> SET DB_UNIQUE_NAME=”orcl_dr”

8> SET AUDIT_FILE_DEST=”/u02/app/oracle/admin/orcl_dr/adump”

9> SET DIAGNOSTIC_DEST=”/u02/app/oracle/diag”

10> SET LOG_ARCHIVE_DEST_2=”service=orcl_dr LGWR SYNC REGISTER VALID_FOR=(online

_logfile,primary_role)”

11> SET FAL_SERVER=”orcl_dr”

12> SET FAL_CLIENT=”orcl”

13> SET CONTROL_FILES=’/u01/oradata/orcl_dr/control01.ctl’,’/u01/oradata/orcl_dr

/control02.ctl’,’/u01/oradata/orcl_dr/control03.ctl’

14> SET DB_FILE_NAME_CONVERT=’C:\ORADATA\ORCL\’,’/u01/oradata/orcl_dr/’

15> SET LOG_FILE_NAME_CONVERT=’C:\ORADATA\ORCL\’,’/u01/oradata/orcl_dr/’;

 

Starting Duplicate Db at 26-OCT-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=10 device type=DISK

 

contents of Memory Script:

{

backup as copy reuse

targetfile ‘C:\app\gavin\product\11.2.0\dbhome_2\DATABASE\PWDorcl.ORA’ auxil

iary format

‘/u02/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl_dr’ targetfile

‘C:\APP\GAVIN\PRODUCT\11.2.0\DBHOME_2\DATABASE\SPFILEORCL.ORA’ auxiliary format

 

‘/u02/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl_dr.ora’ ;

sql clone “alter system set spfile= ”/u02/app/oracle/product/11.2.0/dbhome_1

/dbs/spfileorcl_dr.ora””;

}

executing Memory Script

 

Starting backup at 26-OCT-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=222 device type=DISK

Finished backup at 26-OCT-11

 

sql statement: alter system set spfile= ”/u02/app/oracle/product/11.2.0/dbhome_

1/dbs/spfileorcl_dr.ora”

 

contents of Memory Script:

{

sql clone “alter system set db_unique_name =

”orcl_dr” comment=

”” scope=spfile”;

sql clone “alter system set AUDIT_FILE_DEST =

”/u02/app/oracle/admin/orcl_dr/adump” comment=

”” scope=spfile”;

sql clone “alter system set DIAGNOSTIC_DEST =

”/u02/app/oracle/diag” comment=

”” scope=spfile”;

sql clone “alter system set LOG_ARCHIVE_DEST_2 =

”service=orcl_dr LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”

comment=

”” scope=spfile”;

sql clone “alter system set FAL_SERVER =

”orcl_dr” comment=

”” scope=spfile”;

sql clone “alter system set FAL_CLIENT =

”orcl” comment=

”” scope=spfile”;

sql clone “alter system set CONTROL_FILES =

”/u01/oradata/orcl_dr/control01.ctl”, ”/u01/oradata/orcl_dr/control02.ctl”,

”/u01/oradata/orcl_dr/control03.ctl” comment=

”” scope=spfile”;

sql clone “alter system set db_file_name_convert =

”C:\ORADATA\ORCL\”, ”/u01/oradata/orcl_dr/” comment=

”” scope=spfile”;

sql clone “alter system set LOG_FILE_NAME_CONVERT =

”C:\ORADATA\ORCL\”, ”/u01/oradata/orcl_dr/” comment=

”” scope=spfile”;

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

 

sql statement: alter system set db_unique_name = ”orcl_dr” comment= ”” sco

pe=spfile

 

sql statement: alter system set AUDIT_FILE_DEST = ”/u02/app/oracle/admin/orcl

_dr/adump” comment= ”” scope=spfile

 

sql statement: alter system set DIAGNOSTIC_DEST = ”/u02/app/oracle/diag” com

ment= ”” scope=spfile

 

sql statement: alter system set LOG_ARCHIVE_DEST_2 = ”service=orcl_dr LGWR SY

NC REGISTER VALID_FOR=(online_logfile,primary_role)” comment= ”” scope=spfile

 

 

sql statement: alter system set FAL_SERVER = ”orcl_dr” comment= ”” scope=s

pfile

 

sql statement: alter system set FAL_CLIENT = ”orcl” comment= ”” scope=spfi

le

 

sql statement: alter system set CONTROL_FILES = ”/u01/oradata/orcl_dr/control

01.ctl”, ”/u01/oradata/orcl_dr/control02.ctl”, ”/u01/oradata/orcl_dr/control

03.ctl” comment= ”” scope=spfile

 

sql statement: alter system set db_file_name_convert = ”C:\ORADATA\ORCL\”, ‘

‘/u01/oradata/orcl_dr/” comment= ”” scope=spfile

 

sql statement: alter system set LOG_FILE_NAME_CONVERT = ”C:\ORADATA\ORCL\”,

”/u01/oradata/orcl_dr/” comment= ”” scope=spfile

 

Oracle instance shut down

 

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area 835104768 bytes

 

Fixed Size 2217952 bytes

Variable Size 230688800 bytes

Database Buffers 595591168 bytes

Redo Buffers 6606848 bytes

 

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format ‘/u01/oradat

a/orcl_dr/control01.ctl’;

restore clone controlfile to ‘/u01/oradata/orcl_dr/control02.ctl’ from

‘/u01/oradata/orcl_dr/control01.ctl’;

restore clone controlfile to ‘/u01/oradata/orcl_dr/control03.ctl’ from

‘/u01/oradata/orcl_dr/control01.ctl’;

}

executing Memory Script

 

Starting backup at 26-OCT-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=C:\APP\GAVIN\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFORCL.ORA tag=

TAG20111026T182824 RECID=2 STAMP=765570526

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Finished backup at 26-OCT-11

 

Starting restore at 26-OCT-11

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=134 device type=DISK

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 26-OCT-11

 

Starting restore at 26-OCT-11

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 26-OCT-11

 

contents of Memory Script:

{

sql clone ‘alter database mount standby database’;

}

executing Memory Script

 

sql statement: alter database mount standby database

 

contents of Memory Script:

{

set newname for tempfile 1 to

“/u01/oradata/orcl_dr/TEMP01.DBF”;

switch clone tempfile all;

set newname for datafile 1 to

“/u01/oradata/orcl_dr/SYSTEM01.DBF”;

set newname for datafile 2 to

“/u01/oradata/orcl_dr/SYSAUX01.DBF”;

set newname for datafile 3 to

“/u01/oradata/orcl_dr/UNDOTBS01.DBF”;

set newname for datafile 4 to

“/u01/oradata/orcl_dr/USERS01.DBF”;

set newname for datafile 5 to

“/u01/oradata/orcl_dr/EXAMPLE01.DBF”;

backup as copy reuse

datafile 1 auxiliary format

“/u01/oradata/orcl_dr/SYSTEM01.DBF” datafile

2 auxiliary format

“/u01/oradata/orcl_dr/SYSAUX01.DBF” datafile

3 auxiliary format

“/u01/oradata/orcl_dr/UNDOTBS01.DBF” datafile

4 auxiliary format

“/u01/oradata/orcl_dr/USERS01.DBF” datafile

5 auxiliary format

“/u01/oradata/orcl_dr/EXAMPLE01.DBF” ;

sql ‘alter system archive log current’;

}

executing Memory Script

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/oradata/orcl_dr/TEMP01.DBF in control file

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

 

Starting backup at 26-OCT-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=C:\ORADATA\ORCL\SYSTEM01.DBF

output file name=/u01/oradata/orcl_dr/SYSTEM01.DBF tag=TAG20111026T182947

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=C:\ORADATA\ORCL\SYSAUX01.DBF

output file name=/u01/oradata/orcl_dr/SYSAUX01.DBF tag=TAG20111026T182947

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=C:\ORADATA\ORCL\EXAMPLE01.DBF

output file name=/u01/oradata/orcl_dr/EXAMPLE01.DBF tag=TAG20111026T182947

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:53

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=C:\ORADATA\ORCL\UNDOTBS01.DBF

output file name=/u01/oradata/orcl_dr/UNDOTBS01.DBF tag=TAG20111026T182947

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=C:\ORADATA\ORCL\USERS01.DBF

output file name=/u01/oradata/orcl_dr/USERS01.DBF tag=TAG20111026T182947

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

Finished backup at 26-OCT-11

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

backup as copy reuse

archivelog like “C:\ORADATA\ORCL\ARCH\ARC0000000005_0765492451.0001” auxilia

ry format

“/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:oradataorclarchARC0000000005_076

5492451.0001″ ;

catalog clone archivelog “/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:orad

ataorclarchARC0000000005_0765492451.0001″;

switch clone datafile all;

}

executing Memory Script

 

Starting backup at 26-OCT-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=5 RECID=3 STAMP=765570963

output file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:oradataorclarchAR

C0000000005_0765492451.0001 RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:25

Finished backup at 26-OCT-11

 

cataloged archived log

archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:oradataorcl

archARC0000000005_0765492451.0001 RECID=1 STAMP=765570981

 

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=765570981 file name=/u01/oradata/orcl_dr/SYSTE

M01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=765570981 file name=/u01/oradata/orcl_dr/SYSAU

X01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=765570982 file name=/u01/oradata/orcl_dr/UNDOT

BS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=765570982 file name=/u01/oradata/orcl_dr/USERS

01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=765570982 file name=/u01/oradata/orcl_dr/EXAMP

LE01.DBF

 

contents of Memory Script:

{

set until scn 1034319;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

 

executing command: SET until clause

 

Starting recover at 26-OCT-11

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 5 is already on disk as file /u02/app/or

acle/product/11.2.0/dbhome_1/dbs/c:oradataorclarchARC0000000005_0765492451.0001

archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/c:oradataorcl

archARC0000000005_0765492451.0001 thread=1 sequence=5

media recovery complete, elapsed time: 00:00:02

Finished recover at 26-OCT-11

Finished Duplicate Db at 26-OCT-11

 

RMAN>

 

 

ON PRIMARY

 

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

 

SQL> alter system set fal_server=orcl scope=both;

 

System altered.

 

SQL> alter system set fal_client=orcl_dr scope=both;

 

System altered.

 

 

SQL>alter system set LOG_ARCHIVE_DEST_2=’SERVICE=orcl_dr LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=60 DB_UNIQUE_NAME=orcl_dr’ scope=both;

 

 

ON STANDBY

 

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=/u01/oradata/orcl_dr/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_dr’;SQL>

 

System altered.

 

 

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=orcl LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’ scope=both;

 

System altered.

 

 

  • Shutdown and open the Standby database and configure the Real Time Apply (Active Data Guard)

 

SQL> shutdown immediate;

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 230688800 bytes

Database Buffers 595591168 bytes

Redo Buffers 6606848 bytes

Database mounted.

Database opened.

 

 

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete

 

 

SQL> select platform_name,open_mode from v$database;

 

PLATFORM_NAME OPEN_MODE

———————————————————— ——————–

Linux x86 64-bit READ ONLY.


 

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em ORACLE 11gR2. Bookmark o link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s