Let us learn Oracle Database Administration skills: Oracle Heterogenous Dataguard Setup 19c

Saturday, November 2, 2024

Oracle Heterogenous Dataguard Setup 19c - Detailed setup walk through

Heterogenous Dataguard Setup:

Objective: Setup a standalone standby database for a rac primary

Heterogenous here: based on type of deployment

RAC to Standalone

More details:

RAC (2 node) - standalone standby

ASM primary - File system standby

Primary:

DB Name: ORACL19C

DB UNIQ Name: ORACL19C

Standby:

DB Name: ORACL19C

DB Uniq name: ORACL19CSB1

YouTube:

Steps we will follow below:

#	Step Description	config primary/standby
1	Enable force logging @ primary Site - this is a mandatory step, skipping this will result in corruption @ standby site	Primary
2	Ensure password file is available, If not create one using orapwd utility @primary	Primary
3	Add standby redologfiles to primary using the formulat (g+1)*t , where g is numer of groups and t is threads equivalent to primary redolog file	Primary
4	Set the init params@ primary	Primary
5	Set the additional init params @ primary	Primary
5	Set the additional init params @ primary	Primary
6	Enable archiving @ primary if it isnt enabled already	Primary
7	Make an entry in /etc/oratab in standby server for standby instance	Standby
8	Copy the password file from primary site to standby site,ensure password file matches the instance name	Standby
9	Copy TDE file and sqlnet.ora setting properly to standby node	Standby
10	Start the standby instance using only 2 params db_name and db_unique_name	Standby
10	create audit file directory in standby site & create controlfile directory in asm in standby	Standby
11	Add tns entry for both primary and standby in both server's the tnsnames.ora (in DB home)	Primary/Standby
12	Make a static SID_LIST_LISTENER entry in the both primary and standby listeners for faciliating the active db duplication (ensure the global_dbname is adjusted to db_uniquename)	Primary/Standby
13	Perform connection tests to both the primary and standby site through listener	Primary/Standby
14	Frame the standby restore command from active database
15	Use the command file to restore the standby database and recover as well	standby
16	Put the Standby DB in recovery mode	Standby

Execution of the steps:

Enable force logging @ primary Site - this is a mandatory step, skipping this will result in corruption @ standby site

Primary

SQL> select name,open_mode,force_logging from v$database;

NAME OPEN_MODE FORCE_LOGGING

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

ORACL19C READ WRITE NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select name,open_mode,force_logging from v$database;

NAME OPEN_MODE FORCE_LOGGING

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

ORACL19C READ WRITE YES

Ensure password file is available, If not create one using orapwd utility @primary

Primary

[oracle@vcentos79-oracle-rac2 ~]$ srvctl config database -db ORACL19C

Database unique name: ORACL19C

Database name: ORACL19C

Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/ORACL19C/PARAMETERFILE/spfile.270.1138114305

Password file: +DATA/ORACL19C/PASSWORD/pwdoracl19c.258.1138111527 <<< password file exists

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATA

Mount point paths:

Services:

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: dba

Database instances: ORACL19C1,ORACL19C2

Configured nodes: vcentos79-oracle-rac1,vcentos79-oracle-rac2

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

[oracle@vcentos79-oracle-rac2 ~]$

Add standby redologfiles to primary using the formula (g+1)*t , where g is number of groups and t is threads equivalent to primary redolog file

Primary

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 11 ('+DATA') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 12 ('+DATA') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 13 ('+DATA') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 21 ('+DATA') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 22 ('+DATA') SIZE 200M;

ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 23 ('+DATA') SIZE 200M;

SQL> sho parameter db_create

NAME TYPE VALUE

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

db_create_file_dest string +DATA

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

1 1 11 209715200 512 1 NO

CURRENT 1139933 31-OCT-24 1.8447E+19 0

2 1 10 209715200 512 1 NO

INACTIVE 987493 30-OCT-24 1139933 31-OCT-24 0

3 2 3 209715200 512 1 NO

CURRENT 1240045 31-OCT-24 1.8447E+19 0

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

4 2 2 209715200 512 1 NO

INACTIVE 675412 29-MAY-23 1240045 31-OCT-24 0

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 11 ('+DATA') SIZE 200M;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

1 1 11 209715200 512 1 NO

CURRENT 1139933 31-OCT-24 1.8447E+19 0

2 1 10 209715200 512 1 NO

INACTIVE 987493 30-OCT-24 1139933 31-OCT-24 0

3 2 3 209715200 512 1 NO

CURRENT 1240045 31-OCT-24 1.8447E+19 0

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

4 2 2 209715200 512 1 NO

INACTIVE 675412 29-MAY-23 1240045 31-OCT-24 0

SQL> select * from v$standby_log;

GROUP# DBID THREAD# SEQUENCE#

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

BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM

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

NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME CON_ID

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

11 UNASSIGNED 1 0

209715200 512 0 YES UNASSIGNED

SQL> select member from v$logfile;

MEMBER

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

+DATA/ORACL19C/ONLINELOG/group_1.260.1138111547

+DATA/ORACL19C/ONLINELOG/group_2.261.1138111549

+DATA/ORACL19C/ONLINELOG/group_3.268.1138114303

+DATA/ORACL19C/ONLINELOG/group_4.269.1138114303

+DATA/ORACL19C/ONLINELOG/group_11.277.1183824727

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 12 ('+DATA') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 13 ('+DATA') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 21 ('+DATA') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 22 ('+DATA') SIZE 200M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 23 ('+DATA') SIZE 200M;

Database altered.

SQL>

4	Set the init params@ primary	Primary
5	Set the additional init params @ primary	Primary

Params proper:

DB_NAME=ORACL19C

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

Set otherwise:

alter system set DB_UNIQUE_NAME='ORACL19C' scope=spfile sid='*';

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORACL19C,ORACL19CSB1)' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACL19C' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=ORACL19CSB1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORACL19CSB1' scope=both sid='*';

alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile sid='*';

alter system set FAL_SERVER='ORACL19CSB1' scope=both sid='*';

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';

alter system set DB_RECOVERY_FILE_DEST_SIZE=1G;

alter system set db_recovery_file_dest='+DATA';

Errors to look for:

ORA-01078, ORA-16032, ORA-19801, ORA-02097, ORA-19802

Enable archiving @ primary if it isnt enabled already

Primary

Enable archivelog mode:

SQL> SQL> shu immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1577054672 bytes

Fixed Size 8896976 bytes

Variable Size 486539264 bytes

Database Buffers 1073741824 bytes

Redo Buffers 7876608 bytes

Database mounted.

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11

Current log sequence 12

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

ORACL19C MOUNTED

SQL> alter database archivelog;

Database altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11

Next log sequence to archive 12

Current log sequence 12

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=500M;

System altered.

SQL>

[oracle@vcentos79-oracle-rac2 ~]$ srvctl start database -db ORACL19C

[oracle@vcentos79-oracle-rac2 ~]$ srvctl status database -db ORACL19C

Instance ORACL19C1 is running on node vcentos79-oracle-rac1

Instance ORACL19C2 is running on node vcentos79-oracle-rac2

[oracle@vcentos79-oracle-rac2 ~]$

SQL> set lines 1200 pages 3000 colsep , time on timing on trim on trims on long 20000

16:34:57 SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME ,OPEN_MODE ,LOG_MODE ,FORCE_LOGGING

---------,--------------------,------------,---------------------------------------

ORACL19C ,READ WRITE ,ARCHIVELOG ,YES

Elapsed: 00:00:00.21

16:35:09 SQL> select instance_name,status,host_name from v$instance;

INSTANCE_NAME ,STATUS ,HOST_NAME

----------------,------------,----------------------------------------------------------------

ORACL19C1 ,OPEN ,vcentos79-oracle-rac1

Elapsed: 00:00:00.02

16:35:25 SQL> select instance_name,status,host_name from gv$instance;

INSTANCE_NAME ,STATUS ,HOST_NAME

----------------,------------,----------------------------------------------------------------

ORACL19C1 ,OPEN ,vcentos79-oracle-rac1

ORACL19C2 ,OPEN ,vcentos79-oracle-rac2

Elapsed: 00:00:00.07

Make an entry in /etc/oratab in standby server for standby instance

Standby

ORACL19CSB1:/u01/app/oracle/product/19.0.0/db_1:N

>> oratab entry added in sby

Copy the password file from primary site to standby site,ensure password file matches the instance name

Standby

[oracle@vcentos79-oracle-rac2 ~]$ scp pwdoracl19c.258.1138111527 oracle@192.168.194.11:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwORACL19CSB1

…

Warning: Permanently added '192.168.194.11' (ECDSA) to the list of known hosts.

oracle@192.168.194.11's password:

pwdoracl19c.258.1138111527 100% 2048 264.4KB/s 00:00

[oracle@vcentos79-oracle-rac2 ~]$

>> password file copied as per the requirement.

Copy TDE file and sqlnet.ora setting properly to standby node

Standby

>> no sqlnet.ora or no TDE

Start the standby instance using only 2 params db_name and db_unique_name

Standby

>> initparam setup

*.db_name=ORACL19C

*.db_unique_name=ORACL19CSB1

>> no mount the instance

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 243268216 bytes

Fixed Size 8895096 bytes

Variable Size 180355072 bytes

Database Buffers 50331648 bytes

Redo Buffers 3686400 bytes

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

ORACL19CSB1 STARTED

SQL> exit

create audit file directory in standby site & create controlfile directory in asm in standby

Standby

Create needed directories:

primary:

Audit file dest:/u01/app/oracle/admin/ORACL19C/adump

sby:

audit file dest:/u01/app/oracle/admin/ORACL19CSB1/adump

[oracle@vcentos79-oracle-ggtgt dbs]$ mkdir -p /u01/app/oracle/admin/ORACL19CSB1/adump

[oracle@vcentos79-oracle-ggtgt dbs]$ ls -ld /u01/app/oracle/admin/ORACL19CSB1/adump

drwxr-xr-x. 2 oracle oinstall 6 Oct 31 16:48 /u01/app/oracle/admin/ORACL19CSB1/adump

[oracle@vcentos79-oracle-ggtgt dbs]$

/oradata/ORACL19CSB1/controlfile

>> paths created in standby

Add tns entry for both primary and standby in both server's the tnsnames.ora (in DB home)

Primary/Standby

tnsnames.ora:

ORACL19C =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = centosoraclecl1)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORACL19C)

)

ORACL19CSB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.11)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORACL19CSB1)

)

[oracle@vcentos79-oracle-ggtgt admin]$ tnsping ORACL19C

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 31-OCT-2024 16:55:54

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = centosoraclecl1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORACL19C)))

OK (10 msec)

[oracle@vcentos79-oracle-ggtgt admin]$ tnsping ORACL19CSB1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 31-OCT-2024 16:55:58

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.194.11)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORACL19CSB1)))

OK (10 msec)

[oracle@vcentos79-oracle-ggtgt admin]$

>> tns setup is complete

Make a static SID_LIST_LISTENER entry in the both primary and standby listeners for faciliating the active db duplication (ensure the global_dbname is adjusted to db_uniquename)

Primary/Standby

primary:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORACL19C)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = ORACL19C1)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORACL19C)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = ORACL19C2)

)

standby:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORACL19CSB1)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)

(SID_NAME = ORACL19CSB1)

)

lsnrctl reload

lsnrctl status

>> finished listener entry

Perform connection tests to both the primary and standby site through listener

Primary/Standby

>> connection test succeeded

sqlplus sys@ORACL19C as sysdba

>>passwd<<

sqlplus sys@ORACL19CSB1 as sysdba

>>passwd<<

Perform backup of the primary to disk & find out the PITR

Primary

Perform backup of the db to local disk:

run

{

allocate channel c1 device type disk format '/home/oracle/dba/SbySetup/dbbkp/%U';

allocate channel c2 device type disk format '/home/oracle/dba/SbySetup/dbbkp/%U';

allocate channel c3 device type disk format '/home/oracle/dba/SbySetup/dbbkp/%U';

backup spfile;

backup current controlfile;

backup as compressed backupset database include current controlfile plus archivelog;

release channel c1;

release channel c2;

release channel c3;

}

shell:

export ORACLE_SID=ORACL19C1;export ORAENV_ASK=NO;. oraenv >/dev/null 2>&1;

export NLS_DATE_FORMAT='DD/MON/YYYY HH24:MI:SS';

date

rman target / cmdfile=/home/oracle/dba/SbySetup/bkpORACL19C.cmd log=/home/oracle/dba/SbySetup/bkpORACL19C.log

date

[oracle@vcentos79-oracle-rac1 SbySetup]$ jobs -l

[1]+ 5529 Running nohup sh backupdb.sh > nh_backupdb_01nov24.out &

[oracle@vcentos79-oracle-rac1 SbySetup]$

[oracle@vcentos79-oracle-rac1 SbySetup]$ view bkpORACL19C.log

[1]+ Done nohup sh backupdb.sh > nh_backupdb_01nov24.out

[oracle@vcentos79-oracle-rac1 SbySetup]$

[oracle@vcentos79-oracle-rac1 SbySetup]$ grep -Ei "ORA-|RMAN-" bkpORACL19C.log

[oracle@vcentos79-oracle-rac1 SbySetup]$

Find out the PITR to be used in the duplicate command: 01/NOV/2024 12:01:43 [this is when the dbf backup finished]

snip:

including current control file in backup set

piece handle=/home/oracle/dba/SbySetup/dbbkp/0t391n89_1_1 tag=TAG20241101T124808 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:27

Finished backup at 01/NOV/2024 12:48:36

Transfer the backup to sby server

standby

Tx the backup pieces:

[oracle@vcentos79-oracle-rac1 SbySetup]$ scp -pr dbbkp oracle@192.168.194.11:/home/oracle/dba/SBYSetup/

Warning: Permanently added '192.168.194.11' (ECDSA) to the list of known hosts.

oracle@192.168.194.11's password:

0b391kfu_1_1 100% 30MB 8.1MB/s 00:03

0c391kfu_1_1 100% 6182KB 4.8MB/s 00:01

0d391kg2_1_1 100% 3133KB 2.8MB/s 00:01

0e391kga_1_1 100% 62MB 7.0MB/s 00:08

0f391kga_1_1 100% 23MB 8.0MB/s 00:02

0g391kga_1_1 100% 1712KB 8.9MB/s 00:00

0h391kgd_1_1 100% 1088KB 6.7MB/s 00:00

0i391kha_1_1 100% 59KB 3.8MB/s 00:00

0j391kha_1_1 100% 46KB 1.4MB/s 00:00

[oracle@vcentos79-oracle-rac1 SbySetup]$

Restore the standby db from backup

Standby

Restore command:

run

{

allocate auxiliary channel stby1 type disk;

allocate auxiliary channel stby2 type disk;

allocate auxiliary channel stby3 type disk;

duplicate target database for standby

until time "TO_DATE('01-NOV-2024 12:48:36','DD-MON-YYYY HH24:MI:SS')"

spfile

parameter_value_convert 'ORACL19C','ORACL19CSB1'

set db_name='ORACL19C'

set db_unique_name='ORACL19CSB1'

set control_files='/oradata/ORACL19CSB1/controlfile/control_ORACL19CSB1_01.ctl'

set fal_server='ORACL19C'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORACL19CSB1'

set log_archive_dest_2='service=ORACL19C ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ORACL19C'

set db_create_file_dest='/oradata'

set db_create_online_log_dest_1='/oradata'

set cluster_database='FALSE'

set db_recovery_file_dest='/oraarch'

reset local_listener

backup location '/home/oracle/dba/SBYSetup/dbbkp' NOFILENAMECHECK

;

}

reset instance_mode family='dw_helper'

reset instance_number

reset thread

Shell script:

export ORACLE_SID=ORACL19CSB1;export ORAENV_ASK=NO;. oraenv >/dev/null 2>&1;

export NLS_DATE_FORMAT='DD/MON/YYYY HH24:MI:SS';

date

rman auxiliary sys/<>@ORACL19CSB1 cmdfile=/home/oracle/dba/SBYSetup/standbyORACL19CSB1.cmd log=/home/oracle/dba/SBYSetup/standbyORACL19CSB1.log

date

Learning:

1. the timestamp, oracle wants a spfile from the time before the PITR

2. the params to be reset like instance_number, thread, instance_mode arent accepted in duplicate, since they are instance specific param and duplicate db doesnt recognize them

3. missing of spfile autobackup

Restore finished:

[1]+ Done nohup sh standbyORACL19CSB1.sh > nh_standbyORACL19CSB1_01Nov2024_at3.out

[oracle@vcentos79-oracle-ggtgt SBYSetup]$

Validate the restore

Standby

Validate the db:

set lines 1200 pages 3000 colsep , time on timing on trim on trims on

alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS';

select name,open_mode,db_unique_name,database_role from v$database;

select distinct(status) from v$datafile;

select distinct(checkpoint_time) from v$datafile_header;

13:03:24 SQL> select name,open_mode,db_unique_name,database_role from v$database;

NAME ,OPEN_MODE ,DB_UNIQUE_NAME ,DATABASE_ROLE

---------,--------------------,------------------------------,----------------

ORACL19C ,MOUNTED ,ORACL19CSB1 ,PHYSICAL STANDBY

Elapsed: 00:00:00.00

13:03:28 SQL> select distinct(status) from v$datafile;

STATUS

-------

ONLINE

SYSTEM

Elapsed: 00:00:00.19

13:03:36 SQL> select distinct(checkpoint_time) from v$datafile_header;

CHECKPOINT_TIME

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

01/NOV/2024 12:48:09

Elapsed: 00:00:00.15

13:03:40 SQL> select * from v$log;

GROUP#, THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, MEMBERS,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIME ,NEXT_CHANGE#,NEXT_TIME , CON_ID

----------,----------,----------,----------,----------,----------,---,----------------,-------------,--------------------,------------,--------------------,----------

1, 1, 0, 209715200, 512, 1,NO ,CURRENT , 1422257,01/NOV/2024 12:47:50, 1.8447E+19, , 0

2, 1, 0, 209715200, 512, 1,YES,UNUSED , 1413328,01/NOV/2024 12:01:46, 1422257,01/NOV/2024 12:47:50, 0

3, 2, 0, 209715200, 512, 1,NO ,CURRENT , 1422270,01/NOV/2024 12:47:53, 1.8447E+19, , 0

4, 2, 0, 209715200, 512, 1,YES,UNUSED , 1413325,01/NOV/2024 12:01:46, 1422270,01/NOV/2024 12:47:53, 0

Elapsed: 00:00:00.12

13:04:19 SQL> sho parameter thread

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

ofs_threads ,integer ,4

parallel_threads_per_cpu ,integer ,1

thread ,integer ,0

threaded_execution ,boolean ,FALSE

13:04:28 SQL> select * from v$thread

13:04:40 2 /

THREAD#,STATUS,ENABLED , GROUPS,INSTANCE ,OPEN_TIME ,CURRENT_GROUP#, SEQUENCE#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME ,ENABLE_CHANGE#,ENABLE_TIME ,DISABLE_CHANGE#,DISABLE_TIME ,LAST_REDO_SEQUENCE#,LAST_REDO_BLOCK,LAST_REDO_CHANGE#,LAST_REDO_TIME , CON_ID

----------,------,--------,----------,--------------------------------------------------------------------------------,--------------------,--------------,----------,------------------,--------------------,--------------,--------------------,---------------,--------------------,-------------------,---------------,-----------------,--------------------,----------

1,OPEN ,PUBLIC , 2,ORACL19CSB1 ,01/NOV/2024 10:15:35, 1, 19, 1413328,01/NOV/2024 12:01:46, 1,29/MAY/2023 14:05:48, 0, , 0, 0, 1422339,01/NOV/2024 12:48:09, 0

2,OPEN ,PUBLIC , 2,ORACL19C2 ,01/NOV/2024 10:15:22, 3, 11, 1413325,01/NOV/2024 12:01:46, 565402,29/MAY/2023 14:51:43, 0, , 11, 22, 1422341,01/NOV/2024 12:48:10, 0

Elapsed: 00:00:00.02

13:04:41 SQL> select name from v$tempfile;

NAME

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

/oradata/ORACL19CSB1/datafile/o1_mf_temp_%u_.tmp

Elapsed: 00:00:00.01

13:05:25 SQL> select file#,name from v$datafile order by 1;

FILE#,NAME

----------,---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1,/oradata/ORACL19CSB1/datafile/o1_mf_system_ml9n6dhc_.dbf

2,/oradata/ORACL19CSB1/datafile/o1_mf_sysaux_ml9n6dho_.dbf

3,/oradata/ORACL19CSB1/datafile/o1_mf_undotbs1_ml9n6djs_.dbf

4,/oradata/ORACL19CSB1/datafile/o1_mf_undotbs2_ml9n6dpf_.dbf

5,/oradata/ORACL19CSB1/datafile/o1_mf_users_ml9n6dn5_.dbf

Elapsed: 00:00:00.00

13:05:44 SQL> sho parameter log_archive_dest_1

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

log_archive_dest_1 ,string ,LOCATION=USE_DB_RECOVERY_FILE_

, ,DEST VALID_FOR=(ALL_LOGFILES,A

, ,LL_ROLES) DB_UNIQUE_NAME=ORAC

, ,L19CSB1

log_archive_dest_10 ,string ,

log_archive_dest_11 ,string ,

log_archive_dest_12 ,string ,

log_archive_dest_13 ,string ,

log_archive_dest_14 ,string ,

log_archive_dest_15 ,string ,

log_archive_dest_16 ,string ,

log_archive_dest_17 ,string ,

log_archive_dest_18 ,string ,

log_archive_dest_19 ,string ,

13:05:53 SQL> sho parameter db_reco

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

db_recovery_file_dest ,string ,/oraarch

db_recovery_file_dest_size ,big integer,500M

13:06:00 SQL> sho parameter log_archive_dest_2

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

log_archive_dest_2 ,string ,service=ORACL19C ASYNC valid_f

, ,or=(ONLINE_LOGFILE,PRIMARY_ROL

, ,E) db_unique_name=ORACL19C

log_archive_dest_20 ,string ,

log_archive_dest_21 ,string ,

log_archive_dest_22 ,string ,

log_archive_dest_23 ,string ,

log_archive_dest_24 ,string ,

log_archive_dest_25 ,string ,

log_archive_dest_26 ,string ,

log_archive_dest_27 ,string ,

log_archive_dest_28 ,string ,

log_archive_dest_29 ,string ,

13:06:06 SQL> sho parameter log_archive_con

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

log_archive_config ,string ,DG_CONFIG=(ORACL19C,ORACL19CSB

, ,1)

13:06:17 SQL> sho parameter fal_ser

NAME ,TYPE ,VALUE

------------------------------------,-----------,------------------------------

fal_server ,string ,ORACL19C

13:06:24 SQL> !tnsping ORACL19C

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-NOV-2024 13:06:33

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = centosoraclecl1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORACL19C)))

OK (10 msec)

Put the Standby DB in recovery mode

Standby

Try Activating MRP:

alter database recover automatic managed standby database disconnect from session;

select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

select process,status,client_process,sequence# from gv$managed_standby;

13:06:33 SQL> select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,SWITCHOVER_STATUS

----------------,------------------------------,---------,--------------------

PHYSICAL STANDBY,ORACL19CSB1 ,ORACL19C ,RECOVERY NEEDED

Elapsed: 00:00:00.01

13:07:17 SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS ,STATUS ,CLIENT_P, SEQUENCE#

---------,------------,--------,----------

ARCH ,CONNECTED ,ARCH , 0

DGRD ,ALLOCATED ,N/A , 0

ARCH ,CONNECTED ,ARCH , 0

RFS ,IDLE ,Archival, 0

RFS ,IDLE ,LGWR , 12

RFS ,IDLE ,LGWR , 20

RFS ,IDLE ,UNKNOWN , 0

12 rows selected.

Elapsed: 00:00:00.01

13:07:24 SQL> alter database recover automatic managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:06.32

13:07:42 SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS ,STATUS ,CLIENT_P, SEQUENCE#

---------,------------,--------,----------

ARCH ,CONNECTED ,ARCH , 0

DGRD ,ALLOCATED ,N/A , 0

ARCH ,CONNECTED ,ARCH , 0

RFS ,IDLE ,Archival, 0

RFS ,IDLE ,LGWR , 12

RFS ,IDLE ,LGWR , 20

RFS ,IDLE ,UNKNOWN , 0

MRP0 ,APPLYING_LOG,N/A , 20

13 rows selected.

Elapsed: 00:00:00.00

13:08:03 SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS ,STATUS ,CLIENT_P, SEQUENCE#

---------,------------,--------,----------

ARCH ,CLOSING ,ARCH , 12

DGRD ,ALLOCATED ,N/A , 0

ARCH ,CONNECTED ,ARCH , 0

ARCH ,CLOSING ,ARCH , 20

ARCH ,CONNECTED ,ARCH , 0

RFS ,IDLE ,Archival, 0

RFS ,IDLE ,LGWR , 13

RFS ,IDLE ,LGWR , 21

RFS ,IDLE ,UNKNOWN , 0

MRP0 ,APPLYING_LOG,N/A , 13

13 rows selected.

Elapsed: 00:00:00.01

13:09:49 SQL>

Validate the standby db

Standby

primary:

SQL> select count(1) from system.test01nov24;

COUNT(1)

----------

1780

SQL> alter system archive log current;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

1 1 21 209715200 512 1 YES ACTIVE 1426761 01-NOV-24 1427607 01-NOV-24 0

2 1 22 209715200 512 1 NO CURRENT 1427607 01-NOV-24 1.8447E+19 0

3 2 13 209715200 512 1 YES ACTIVE 1426768 01-NOV-24 1427604 01-NOV-24 0

4 2 14 209715200 512 1 NO CURRENT 1427604 01-NOV-24 1.8447E+19 0

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID

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

1 1 21 209715200 512 1 YES INACTIVE 1426761 01-NOV-24 1427607 01-NOV-24 0

2 1 22 209715200 512 1 NO CURRENT 1427607 01-NOV-24 1.8447E+19 0

3 2 13 209715200 512 1 YES INACTIVE 1426768 01-NOV-24 1427604 01-NOV-24 0

4 2 14 209715200 512 1 NO CURRENT 1427604 01-NOV-24 1.8447E+19 0

SQL>

sby:

13:12:10 SQL> select database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;

DATABASE_ROLE ,DB_UNIQUE_NAME ,NAME ,SWITCHOVER_STATUS

----------------,------------------------------,---------,--------------------

PHYSICAL STANDBY,ORACL19CSB1 ,ORACL19C ,NOT ALLOWED

Elapsed: 00:00:00.01

13:12:23 SQL> select name,open_mode from v$database;

NAME ,OPEN_MODE

---------,--------------------

ORACL19C ,READ ONLY

Elapsed: 00:00:00.01

13:12:33 SQL> alter database recover automatic managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:06.05

13:12:57 SQL> select process,status,client_process,sequence# from gv$managed_standby;

PROCESS ,STATUS ,CLIENT_P, SEQUENCE#

---------,------------,--------,----------

ARCH ,CONNECTED ,ARCH , 0

DGRD ,ALLOCATED ,N/A , 0

ARCH ,CONNECTED ,ARCH , 0

RFS ,IDLE ,Archival, 0

RFS ,IDLE ,LGWR , 21

RFS ,IDLE ,LGWR , 13

MRP0 ,APPLYING_LOG,N/A , 13

11 rows selected.

Elapsed: 00:00:00.05

13:13:00 SQL> select count(1) from system.test01nov24;

COUNT(1)

----------

1780

Elapsed: 00:00:00.02

13:13:05 SQL> select * from v$standby_log;

GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIM,NEXT_CHANGE#,NEXT_TIME,LAST_CHANGE#,LAST_TIME, CON_ID

----------,----------------------------------------,----------,----------,----------,----------,----------,---,----------,-------------,---------,------------,---------,------------,---------,----------

11,442875896 , 1, 21, 209715200, 512, 1075712,YES,ACTIVE , 1426761,01-NOV-24, , , 1427565,01-NOV-24, 0

12,UNASSIGNED , 1, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

13,UNASSIGNED , 1, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

21,442875896 , 2, 13, 209715200, 512, 141824,YES,ACTIVE , 1426768,01-NOV-24, , , 1427565,01-NOV-24, 0

22,UNASSIGNED , 2, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

23,UNASSIGNED , 2, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

6 rows selected.

Elapsed: 00:00:00.01

13:13:37 SQL> select * from v$standby_log;

GROUP#,DBID , THREAD#, SEQUENCE#, BYTES, BLOCKSIZE, USED,ARC,STATUS ,FIRST_CHANGE#,FIRST_TIM,NEXT_CHANGE#,NEXT_TIME,LAST_CHANGE#,LAST_TIME, CON_ID

11,442875896 , 1, 22, 209715200, 512, 19456,YES,ACTIVE , 1427607,01-NOV-24, , , 1427715,01-NOV-24, 0

12,UNASSIGNED , 1, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

13,UNASSIGNED , 1, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

21,442875896 , 2, 14, 209715200, 512, 19456,YES,ACTIVE , 1427604,01-NOV-24, , , 1427717,01-NOV-24, 0

22,UNASSIGNED , 2, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

23,UNASSIGNED , 2, 0, 209715200, 512, 0,YES,UNASSIGNED, , , , , , , 0

6 rows selected.

Elapsed: 00:00:00.01

13:14:24 SQL>

Let us learn Oracle Database Administration skills

Saturday, November 2, 2024

Oracle Heterogenous Dataguard Setup 19c - Detailed setup walk through

Objective: Setup a standalone standby database for a rac primary

Heterogenous here: based on type of deployment

Primary:

Standby:

Steps we will follow below:

Execution of the steps:

No comments:

Post a Comment

Flashback data archive steps