Oracle 11G Cross Platform Active Standby - Windows Primary Database and Linux Active Standby

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.