OraCsdP: 12CR1 Physical Standby Creation « from Active Primary DB »

The goal is still to create a Physical Standby DB, quickly and without errors.

Now we have Oracle 12C, let’s use the DUPLICATE FROM ACTIVE STANDBY feature.

We won’t use any RMAN dumps previously created.

Primary DB : PRIMA12C / Standby DB : STDBY12C

a/ the most important step remains the prerequisite.

b/ that time, we’ll have a Dedicated Network for Log shipping to offload the User Network.

Component	Version
OS	AIX 7.2
Oracle RDBMS	12C R1 12.1.0.2
MultiTenant 12C	Non CDB

1] Architecture

Decisions

Item	Value	Description
LOGGING	FORCE	FORCE_LOGGING = ‘Y’
Synchronisation	MAXIMUM AVAILABILITY	No Data Loss
Réplication	REAL TIME	Standby Redologs necessary
Flashback Database	NO	Flashback Logs area necessary Useful after Failover ( reinstate )
Fast Start Failover	NO	Failover : automatic
Broker	YES	Centralized Administration

If the real-time apply feature is enabled, then apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have already been applied to the standby database by the time the failover or switchover begins.

Real-time apply requires a standby database that is configured with a standby redo log and that is in ARCHIVELOG mode.

Reminder : Real Time Apply

Dataguard Physical Instance State

Oracle Instance State	Physical Standby
Type	Primary	Secondary
OPEN_MODE	READ WRITE	MOUNTED
DATABASE_ROLE	PRIMARY	PHYSICAL STANDBY
RECOVERY_MODE	NA	MANAGED REAL TIME APPLY

Boot Sequence

Oracle	Physical Standby
Action	Primary	Secondary
Startup	startup mount	startup mount
Recover	If DATABASE_ROLE = ‘PRIMARY’ alter database open	Implicitly by the Broker : Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY

2] Creation

Creating a Physical Standby from Primary on Version 12c (Doc ID 1570958.1)

1. Create necessary Directory on standby server.

2. Create pfile and password.

3. Make static listener entry on standby server.

4. Use RMAN Duplicate ..from Active Database to clone Primary.

5. Configure primary for remote log shipping and verify MRP progress on standby.

RMAN can create a standby database by copying the files currently in use by the primary database. No backups are required.

Parameter	Primary	Standby
db_name	PRI12C	PRI12C
db_unique_name	PRI12C	STD12C
CNAME Host Appli. (1)	PRI12C host	STD12C host-stb
CNAME Host Technical (1)	PRI12CTEC host-tec	STD12CTEC host-stb-tec
LDAP Entry Appli. dedicated	PRI12C	STD12C
LDAP Entry Technical dedicated	PRI12CTEC	STD12CTEC
Listener Static Oracle Net Service	LSN_PRI12C 1522 PRI12C.<domain> PRI12C_DGMGRL.<domain>	LSN_STD12C 1522 STD12C.<domain> STD12C_DGMGRL.<domain>
Listener Technical Static Oracle Net Service	LSN_PRI12C_TEC 1552 PRI12C.<domain> PRI12C_DGMGRL.<domain> PRI12C_DGB.<domain>	LSN_STD12C_TEC 1552 STD12C.<domain> STD12C_DGMGRL.<domain> STD12C_DGB.<domain>

(1) We have CNAME DNS Alias to map the DB servers hosname as the DB Name :

PRI12C CNAME Alias of host

PRI12CTEC CNAME Alias of host-tec

STD12C CNAME Alias of host-stb

STD12CTEC CNAME Alias of host-stb-tec

for i in PRI12C STD12C PRI12CTEC STD12CTEC ; do

nslookup $i

done

2.1 Prerequisite on Primary

2 Listeners per Instance / 2 ports TCP && 2 Host Address :

PRI12C host: /oracle/12CDB/network/admin $ cat li*ora

LSN_PRI12C=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=PRI12C)(PORT=1522))

(ADDRESS=(PROTOCOL=ipc)(KEY=extprocpri12c))))

SID_LIST_LSN_PRI12C =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PRI12C.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = PRI12C)

)

(SID_DESC =

(GLOBAL_DBNAME = PRI12C_DGMGRL.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = PRI12C)

)

ADR_BASE_LSN_PRI12C=/work/oracle/pri12c/tnslsnr

LSN_PRI12C_TEC =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRI12CTEC)(PORT = 1552))

)

SID_LIST_LSN_PRI12C_TEC =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PRI12C.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = PRI12C)

)

(SID_DESC =

(GLOBAL_DBNAME = PRI12C_DGMGRL.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = PRI12C)

)

(SID_DESC =

(GLOBAL_DBNAME = PRI12C_DGB.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = PRI12C)

)

ADR_BASE_LSN_PRI12C_TEC =/work/oracle/pri12c/tnslsnr

Start/Reload both Listeners

PRI12C sr-orapro-2: /oracle/12CDB/network/admin $ lsnrctl reload LSN_PRI12C

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 27-SEP-2018 15:59:48

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sr-orapro-2)(PORT=1531)))

The command completed successfully

PRI12C sr-orapro-2: /oracle/12CDB/network/admin $ lsnrctl start LSN_PRI12C_TEC

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 27-SEP-2018 15:59:57

Starting /oracle/12CDB/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

System parameter file is /oracle/12CDB/network/admin/listener.ora

Log messages written to /work/oracle/pri12c/tnslsnr/diag/tnslsnr/sr-orapro-2/lsn_pri12c_tec/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.206.2)(PORT=1561)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRI12CTEC)(PORT=1561)))

STATUS of the LISTENER

------------------------

Alias LSN_PRI12C_TEC

Version TNSLSNR for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

Start Date 27-SEP-2018 15:59:58

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/12CDB/network/admin/listener.ora

Listener Log File /work/oracle/pri12c/tnslsnr/diag/tnslsnr/sr-orapro-2/lsn_pri12c_tec/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.206.2)(PORT=1561)))

Services Summary...

Service "PRI12C.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

Service "PRI12C_DGB.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

Service "PRI12C_DGMGRL.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

PRI12C sr-orapro-2: /oracle/12CDB/network/admin $

PRI12C sr-orapro-2: /oracle/12CDB/network/admin $ lsnrctl start LSN_PRI12C_TEC

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 25-OCT-2018 11:08:00

Starting /oracle/12CDB/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

System parameter file is /oracle/12CDB/network/admin/listener.ora

Log messages written to /work/oracle/pri12c/tnslsnr/diag/tnslsnr/sr-orapro-2/lsn_pri12c_tec/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.206.2)(PORT=1561)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PRI12CTEC)(PORT=1561)))

STATUS of the LISTENER

------------------------

Alias LSN_PRI12C_TEC

Version TNSLSNR for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production

Start Date 25-OCT-2018 11:08:00

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/12CDB/network/admin/listener.ora

Listener Log File /work/oracle/pri12c/tnslsnr/diag/tnslsnr/sr-orapro-2/lsn_pri12c_tec/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.206.2)(PORT=1561)))

Services Summary...

Service "PRI12C.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

Service "PRI12C_DGB.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

Service "PRI12C_DGMGRL.cg13.fr" has 1 instance(s).

Instance "PRI12C", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Create LDAP Entry : PRI12C & PRI12CTEC as SERVER=DEDICATED

Check SYS access from Standby Server

for i in PRI12C ; do

sqlplus SYS/${psw}@$i AS SYSDBA

sqlplus SYS/${psw}@${i}TEC AS SYSDBA

done

Init parameter with instance bounce

sqlplus -s ${con_str} AS SYSDBA <<!

alter system set DB_UNIQUE_NAME=PRI12C scope = spfile ;

alter system set DB_FILE_NAME_CONVERT='/data/std12c/','/data/pri12c/' scope = spfile ;

alter system set LOG_FILE_NAME_CONVERT='/rdo1/std12c/','/rdo1/pri12c/','/rdo2/std12c/','/rdo2/pri12c/' scope = spfile ;

shut immediate

sqlplus -s ${con_str} AS SYSDBA <<!

startup

2.2 Prerequisite on Secondary

2 Listeners per Instance / 2 ports TCP && 2 Host Address :

STD12C host-stb: /oracle/12CDB/network/admin $ cat li*ora

LSN_STD12C=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=STD12C)(PORT=1522))

(ADDRESS=(PROTOCOL=ipc)(KEY=extprocstd12c))))

SID_LIST_LSN_STD12C =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = STD12C.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = STD12C)

)

(SID_DESC =

(GLOBAL_DBNAME = STD12C_DGMGRL.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = STD12C)

)

ADR_BASE_LSN_STD12C=/work/oracle/std12c/tnslsnr

LSN_STD12C_TEC =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = STD12CTEC)(PORT = 1552))

)

SID_LIST_LSN_STD12C_TEC =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = STD12C.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = STD12C)

)

(SID_DESC =

(GLOBAL_DBNAME = STD12C_DGMGRL.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = STD12C)

)

(SID_DESC =

(GLOBAL_DBNAME = STD12C_DGB.<domain>)

(ORACLE_HOME = /oracle/12CDB)

(SID_NAME = STD12C)

)

ADR_BASE_LSN_STD12C_TEC =/work/oracle/std12c/tnslsnr

Start both Listeners

$ lsnrctl start LSN_STD12C

$ lsnrctl start LSN_STD12C_TEC

Create LDAP Entry : STD12C & STD12CTEC as SERVER=DEDICATED

Check SYS access from Primary Server

for i in STD12C ; do

sqlplus SYS/${psw}@$i AS SYSDBA

sqlplus SYS/${psw}@${i}TEC AS SYSDBA

done

since standby instance is not started : ORA-01017: invalid username/password;

Create directories

mkdir -p /work/oracle/std12c/diag

mkdir -p /work/oracle/std12c/adump

mkdir -p /work/oracle/std12c/pfile

mkdir -p /work/oracle/std12c/exp

mkdir -p /work/oracle/std12c/dpdump

mkdir -p /work/oracle/std12c/tnsadmin

mkdir -p /work/oracle/std12c/rman

mkdir -p /work/oracle/std12c/tnslsnr

mkdir -p /work/oracle/std12c/noarc

mkdir -p /rdo1/std12c/ctl

mkdir -p /rdo2/std12c/ctl

2.3 Active Standby Creation with RMAN

[PRIMARY] ALTER DATABASE FORCE LOGGING;
[PRIMARY] Create Standby Redologs

Check Redologs BLOCKSIZE, should be the same on both sites.

To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement.

Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database.

[PRIMARY] Modify Init Parameters
[SECONDARY] Create Password File
[SECONDARY] Create Minimum Init Parameter File to startup Nomount the Instance

sys_psw_pri=$( get_psw_adb SYS ${PRIM} ) ; con_str=SYS/${sys_psw_pri}@${PRIM}

sqlplus -s ${con_str} AS SYSDBA <<!

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/rdo1/pri12c/std_log_11_1.rdo','/rdo2/pri12c/std_log_11_2.rdo') size 512M BLOCKSIZE 4096 ;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/rdo1/pri12c/std_log_12_1.rdo','/rdo2/pri12c/std_log_12_2.rdo') size 512M BLOCKSIZE 4096 ;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/rdo1/pri12c/std_log_13_1.rdo','/rdo2/pri12c/std_log_13_2.rdo') size 512M BLOCKSIZE 4096 ;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/rdo1/pri12c/std_log_14_1.rdo','/rdo2/pri12c/std_log_14_2.rdo') size 512M BLOCKSIZE 4096 ;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRI12C,STD12C)' ;

alter system set LOG_ARCHIVE_DEST_1='LOCATION="/arch/pri12c/" VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRI12C' ;

alter system set LOG_ARCHIVE_DEST_2='SERVICE=STD12CTEC ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STD12C' ;

alter system set FAL_SERVER=STD12CTEC ;

alter system set dg_broker_config_file1='/rdo1/pri12c/ctl/dr1PRI12C.dat' ;

alter system set dg_broker_config_file2='/rdo2/pri12c/ctl/dr2PRI12C.dat' ;

alter system set STANDBY_FILE_MANAGEMENT=AUTO ;

rm ${ORACLE_HOME}/dbs/orapw${ORACLE_SID}

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=sys_psw

cat > /work/oracle/std12c/pfile/initSTD12C_stdby_mini.ora <<!

db_name='PRI12C'

sqlplus -s '/ as sysdba' <<!

shut abort

startup nomount pfile = '/work/oracle/std12c/pfile/initSTD12C_stdby_mini.ora'

rm /rdo?/std12c/log_*.rdo

rm /data/std12c/temp.dbf

[SECONDARY] Run RMAN Script

RMAN SCRIPT

$ cat duplicate_active_standby_S.scr

connect target sys/sys_psw@PRI12CD

connect AUXILIARY sys/sys_psw@STD12CTEC

# Media : DISK

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

allocate channel prim type disk;

allocate auxiliary channel aux type disk;

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

SPFILE

parameter_value_convert 'pri12c','std12c'

set instance_name = 'STD12C'

set db_file_name_convert='/data/pri12c/','/data/std12c/'

set log_file_name_convert='/rdo1/pri12c/','/rdo1/std12c/','/rdo2/pri12c/','/rdo2/std12c/'

set log_archive_max_processes='4'

set db_unique_name='STD12C'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(PRI12C,STD12C)'

set log_archive_dest_1='location="/arch/std12c" valid_for=(all_logfiles,all_roles) db_unique_name=STD12C'

set log_Archive_dest_2='service=PRI12CTEC async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRI12C'

set log_archive_config='dg_config=(PRI12C,STD12C)';

sql channel prim "alter system archive log current";

sql channel aux "alter database recover managed standby database USING CURRENT LOGFILE disconnect";

}

Run it :

rman @${REP_SCR}/duplicate_active_standby_S.scr |tee ${REP_SCR}/log/duplicate_active_standby_S_$( date '+%y%m%d_%H%M%S' ).log

Log

$ ./1_duplicate_active_standby.ksh STD12C 12 STANDBY PHYSICAL PRI12C |tee log/1_duplicate_active_standby.PRI12C.log

Database altered.

System altered.

rm: /oracle/12CDB/dbs/orapwSTD12C: A file or directory in the path name does not exist.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 255852544 bytes

Fixed Size 2996712 bytes

Variable Size 197133848 bytes

Database Buffers 50331648 bytes

Redo Buffers 5390336 bytes

rm: /rdo?/std12c/log_*.rdo: A file or directory in the path name does not exist.

rm: /data/std12c/temp.dbf: A file or directory in the path name does not exist.

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 25 12:06:16 2018

RMAN> connect target *

2> connect AUXILIARY *

4> # Media : DISK

5> CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET ;

7> run

8> {

9> allocate channel c1 type disk;

10> allocate channel c2 type disk;

11> allocate channel c3 type disk;

12> allocate channel c4 type disk;

13> allocate channel prim type disk;

14> allocate auxiliary channel aux type disk;

15>

16> DUPLICATE TARGET DATABASE

17> FOR STANDBY

18> FROM ACTIVE DATABASE

19> SPFILE

20> parameter_value_convert 'pri12c','std12c'

21> set instance_name = 'STD12C'

22> set db_file_name_convert='/data/pri12c/','/data/std12c/'

23> set log_file_name_convert='/rdo1/pri12c/','/rdo1/std12c/','/rdo2/pri12c/','/rdo2/std12c/'

24> set log_archive_max_processes='4'

25> set db_unique_name='STD12C'

26> set standby_file_management='AUTO'

27> set log_archive_config='dg_config=(PRI12C,STD12C)'

28> set log_archive_dest_1='location="/arch/std12c" valid_for=(all_logfiles,all_roles) db_unique_name=STD12C'

29> set log_Archive_dest_2='service=PRI12CTEC async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRI12C'

30> set log_archive_config='dg_config=(PRI12C,STD12C)';

31>

32> sql channel prim "alter system archive log current";

33> sql channel aux "alter database recover managed standby database USING CURRENT LOGFILE disconnect";

34> }

35>

36>

connected to target database: PRI12C (DBID=1455241593)

connected to auxiliary database: PRI12C (not mounted)

using target database control file instead of recovery catalog

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored

allocated channel: c1

channel c1: SID=728 device type=DISK

allocated channel: c2

channel c2: SID=826 device type=DISK

allocated channel: c3

channel c3: SID=13 device type=DISK

allocated channel: c4

channel c4: SID=129 device type=DISK

allocated channel: prim

channel prim: SID=235 device type=DISK

allocated channel: aux

channel aux: SID=3 device type=DISK

Starting Duplicate Db at 25/10/2018

contents of Memory Script:

{

backup as copy reuse

targetfile '/oracle/12CDB/dbs/orapwPRI12C' auxiliary format

'/oracle/12CDB/dbs/orapwSTD12C' targetfile

'/work/oracle/pri12c/pfile/spfilePRI12C.ora' auxiliary format

'/oracle/12CDB/dbs/spfileSTD12C.ora' ;

sql clone "alter system set spfile= ''/oracle/12CDB/dbs/spfileSTD12C.ora''";

}

executing Memory Script

Starting backup at 25/10/2018

Finished backup at 25/10/2018

sql statement: alter system set spfile= ''/oracle/12CDB/dbs/spfileSTD12C.ora''

contents of Memory Script:

{

sql clone "alter system set audit_file_dest =

''/work/oracle/std12c/adump'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''/rdo1/std12c/ctl/control01.ctl'', ''/rdo2/std12c/ctl/control02.ctl'' comment=

'''' scope=spfile";

sql clone "alter system set dg_broker_config_file1 =

''/rdo1/std12c/ctl/dr1PRI12C.dat'' comment=

'''' scope=spfile";

sql clone "alter system set dg_broker_config_file2 =

''/rdo2/std12c/ctl/dr2PRI12C.dat'' comment=

'''' scope=spfile";

sql clone "alter system set diagnostic_dest =

''/work/oracle/std12c'' comment=

'''' scope=spfile";

sql clone "alter system set instance_name =

''STD12C'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''/data/pri12c/'', ''/data/std12c/'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/rdo1/pri12c/'', ''/rdo1/std12c/'', ''/rdo2/pri12c/'', ''/rdo2/std12c/'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_max_processes =

4 comment=

'''' scope=spfile";

sql clone "alter system set db_unique_name =

''STD12C'' comment=

'''' scope=spfile";

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_config =

''dg_config=(PRI12C,STD12C)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_1 =

''location=^"/arch/std12c^" valid_for=(all_logfiles,all_roles) db_unique_name=STD12C'' comment=

'''' scope=spfile";

sql clone "alter system set log_Archive_dest_2 =

''service=PRI12CTEC async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRI12C'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_config =

''dg_config=(PRI12C,STD12C)'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set audit_file_dest = ''/work/oracle/std12c/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/rdo1/std12c/ctl/control01.ctl'', ''/rdo2/std12c/ctl/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set dg_broker_config_file1 = ''/rdo1/std12c/ctl/dr1PRI12C.dat'' comment= '''' scope=spfile

sql statement: alter system set dg_broker_config_file2 = ''/rdo2/std12c/ctl/dr2PRI12C.dat'' comment= '''' scope=spfile

sql statement: alter system set diagnostic_dest = ''/work/oracle/std12c'' comment= '''' scope=spfile

sql statement: alter system set instance_name = ''STD12C'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/data/pri12c/'', ''/data/std12c/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/rdo1/pri12c/'', ''/rdo1/std12c/'', ''/rdo2/pri12c/'', ''/rdo2/std12c/'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 4 comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''STD12C'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(PRI12C,STD12C)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''location="/arch/std12c" valid_for=(all_logfiles,all_roles) db_unique_name=STD12C'' comment= '''' scope=spfile

sql statement: alter system set log_Archive_dest_2 = ''service=PRI12CTEC async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=PRI12C'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(PRI12C,STD12C)'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 8589934592 bytes

Fixed Size 3804952 bytes

Variable Size 1610612968 bytes

Database Buffers 6962544640 bytes

Redo Buffers 12972032 bytes

allocated channel: aux

channel aux: SID=236 device type=DISK

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/rdo1/std12c/ctl/control01.ctl';

restore clone primary controlfile to '/rdo2/std12c/ctl/control02.ctl' from

'/rdo1/std12c/ctl/control01.ctl';

}

executing Memory Script

Starting backup at 25/10/2018

channel c1: starting datafile copy

copying standby control file

output file name=/rman/pri12c/ctl/snapcf_pri12cf tag=TAG20181025T120648

channel c1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 25/10/2018

Starting restore at 25/10/2018

channel aux: copied control file copy

Finished restore at 25/10/2018

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

"/data/std12c/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/data/std12c/system01.dbf";

set newname for datafile 2 to

"/data/std12c/mgmt_depot.dbf";

set newname for datafile 3 to

"/data/std12c/sysaux01.dbf";

set newname for datafile 4 to

"/data/std12c/undotbs01.dbf";

set newname for datafile 5 to

"/data/std12c/mgmt_02.dbf";

set newname for datafile 6 to

"/data/std12c/users01.dbf";

set newname for datafile 7 to

"/data/std12c/mgmt.dbf";

set newname for datafile 8 to

"/data/std12c/mgmt_ad4j.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/data/std12c/system01.dbf" datafile

2 auxiliary format

"/data/std12c/mgmt_depot.dbf" datafile

3 auxiliary format

"/data/std12c/sysaux01.dbf" datafile

4 auxiliary format

"/data/std12c/undotbs01.dbf" datafile

5 auxiliary format

"/data/std12c/mgmt_02.dbf" datafile

6 auxiliary format

"/data/std12c/users01.dbf" datafile

7 auxiliary format

"/data/std12c/mgmt.dbf" datafile

8 auxiliary format

"/data/std12c/mgmt_ad4j.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /data/std12c/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 25/10/2018

channel c1: starting datafile copy

input datafile file number=00007 name=/data/pri12c/mgmt.dbf

channel c2: starting datafile copy

input datafile file number=00005 name=/data/pri12c/mgmt_02.dbf

channel c3: starting datafile copy

input datafile file number=00004 name=/data/pri12c/undotbs01.dbf

channel c4: starting datafile copy

input datafile file number=00008 name=/data/pri12c/mgmt_ad4j.dbf

channel prim: starting datafile copy

input datafile file number=00003 name=/data/pri12c/sysaux01.dbf

output file name=/data/std12c/sysaux01.dbf tag=TAG20181025T120655

channel prim: datafile copy complete, elapsed time: 00:00:15

channel prim: starting datafile copy

input datafile file number=00002 name=/data/pri12c/mgmt_depot.dbf

output file name=/data/std12c/mgmt_ad4j.dbf tag=TAG20181025T120655

channel c4: datafile copy complete, elapsed time: 00:00:15

channel c4: starting datafile copy

input datafile file number=00001 name=/data/pri12c/system01.dbf

output file name=/data/std12c/system01.dbf tag=TAG20181025T120655

channel c4: datafile copy complete, elapsed time: 00:00:07

channel c4: starting datafile copy

input datafile file number=00006 name=/data/pri12c/users01.dbf

output file name=/data/std12c/mgmt_depot.dbf tag=TAG20181025T120655

channel prim: datafile copy complete, elapsed time: 00:00:08

output file name=/data/std12c/users01.dbf tag=TAG20181025T120655

channel c4: datafile copy complete, elapsed time: 00:00:01

output file name=/data/std12c/undotbs01.dbf tag=TAG20181025T120655

channel c3: datafile copy complete, elapsed time: 00:00:26

output file name=/data/std12c/mgmt_02.dbf tag=TAG20181025T120655

channel c2: datafile copy complete, elapsed time: 00:01:18

output file name=/data/std12c/mgmt.dbf tag=TAG20181025T120655

channel c1: datafile copy complete, elapsed time: 00:01:58

Finished backup at 25/10/2018

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=990446934 file name=/data/std12c/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=2 STAMP=990446934 file name=/data/std12c/mgmt_depot.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=3 STAMP=990446934 file name=/data/std12c/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=4 STAMP=990446934 file name=/data/std12c/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=5 STAMP=990446934 file name=/data/std12c/mgmt_02.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=6 STAMP=990446934 file name=/data/std12c/users01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=7 STAMP=990446934 file name=/data/std12c/mgmt.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=8 STAMP=990446934 file name=/data/std12c/mgmt_ad4j.dbf

Finished Duplicate Db at 25/10/2018

sql statement: alter system archive log current

sql statement: alter database recover managed standby database USING CURRENT LOGFILE disconnect

released channel: c1

released channel: c2

released channel: c3

released channel: c4

released channel: prim

released channel: aux

Recovery Manager complete.

Standby Physique OK : STD12C

3] Controls

3.1 Primary Instance Check

Controls script

set pages 5000 lines 500

col error for a30

col current_scn for 999999999999999999

col applied_scn for 999999999999999999

col MEMBER for a35

col status for a20

col type for a20

col protection_mode for a20

col recovery_mode for a30

prompt

prompt ** Standby redolog ASSIGNED ? **

SELECT a.GROUP#,THREAD#,SEQUENCE#,ARCHIVED,a.STATUS,MEMBER FROM V$STANDBY_LOG a , V$LOGFILE b where a.GROUP# = b.GROUP# ;

prompt

prompt ** Protection de la synchronisation et Current SCN **

SELECT protection_mode , current_scn FROM v$database;

prompt

prompt ** From Primary: Standby status

--SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';

select DEST_ID,type,recovery_mode from v$archive_dest_status where dest_id=2;

prompt

prompt ** From Primary: Current sequence no

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT distinct sequence#, first_time, next_time FROM v$archived_log where sequence# = ( SELECT max(sequence#) FROM v$archived_log ) ;

prompt

prompt ** From Primary: last redo applied by Standby : Current SCN **

select dest_id,error,status,log_sequence,applied_scn from v$archive_dest where TARGET='STANDBY';

Instances Oracle : informations

PRI12C PRIMARY

** From Primary: Standby status

DEST_ID TYPE RECOVERY_MODE

---------- ---------------- -----------------------

2 PHYSICAL MANAGED REAL TIME APPLY

** From Primary: Current sequence no

Session altered.

SEQUENCE# FIRST_TIME NEXT_TIME

---------- -------------------- --------------------

10286 13-FEB-2018 15:24:37 13-FEB-2018 15:39:38

** From Primary: last redo applied by Standby

DEST_ID ERROR STATUS LOG_SEQUENCE APPLIED_SCN

---------- ------------------------------ --------- ------------ -------------------

2 VALID 10287 41839376373

** PRI12C PRIMARY: delay with Standby **

PRIMARY STANDBY DIFF_MIN

--------------------------------------------------

13-FEB-2018 15:44:5413-FEB-2018 15:43:57 1

3.2 Standby Instance Check

set pages 5000 lines 500

col error for a30

col applied_scn for 999999999999999999

col MEMBER for a35

prompt

prompt ** Standby redolog ASSIGNED ? **

SELECT a.GROUP#,THREAD#,SEQUENCE#,ARCHIVED,a.STATUS,MEMBER FROM V$STANDBY_LOG a , V$LOGFILE b where a.GROUP# = b.GROUP# ;

prompt

prompt ** From Standby: Monitor Redo Apply

select process,status,thread#,sequence#,blocks from v$managed_standby where process like '%MR%';

prompt

prompt ** From Standby: Applied Archivelogs

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence# = ( SELECT max(sequence#) FROM v$archived_log where ( APPLIED = 'YES' OR APPLIED = 'IN-MEMORY' ) ) ;

Instances Oracle : informations

PPRUS PHYSICAL STANDBY

** From Standby: Standby redolog ASSIGNED ? **

GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------

51 1 10287 YES ACTIVE

52 1 0 NO UNASSIGNED

61 1 0 YES UNASSIGNED

62 0 0 YES UNASSIGNED

71 0 0 YES UNASSIGNED

72 0 0 YES UNASSIGNED

81 0 0 YES UNASSIGNED

82 0 0 YES UNASSIGNED

8 rows selected.

** From Standby: Monitor Redo Apply

PROCESS STATUS THREAD# SEQUENCE# BLOCKS

--------- ------------ ---------- ---------- ----------

MRP0 APPLYING_LOG 1 10287 65536

** From Standby: Applied Archivelogs

Session altered.

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---------- -------------------- -------------------- ---------

10286 13-FEB-2018 15:24:37 13-FEB-2018 15:39:38 IN-MEMORY

REDOLOG Log status:

UNASSIGNED - If ARCHIVED is NO, then the standby redo log has been archived and is again available. IfARCHIVED is YES, then the standby redo log has never been used and is available.

ACTIVE - If ARCHIVED is NO, then the standby redo log is complete and waiting to be archived. If ARCHIVED is YES, then the standby redo log is currently being written to and is therefore not ready to be archived. For a given thread, there should be only one such log.

Now, it’s time to create Broker Configuration – following post.

OraCsdP

Categories

lundi 19 novembre 2018

12CR1 Physical Standby Creation « from Active Primary DB »