De dataguard configuratie in dit artikel komt er als volgt uit te zien.
| ODA prim DB | ODA stby DB | |||
| node | aj001 | aj002 | aj007 | aj008 |
| database |
CA040P |
CA040SB |
||
User oracle moet in staat zijn om zonder passphrase met ssh kunnen connecteren aan alle nodes.
De CA040P heeft 2 ACFS filesystems, deze gaan we ook maken voor de CA040SB
/dev/asm/datca040p-250 acfs 2.5T 1011G 1.5T 41% /u02/app/oracle/oradata/CA040P
/dev/asm/rdoca040p-375 acfs 500G 194G 307G 39% /u04/app/oracle/redo/CA040PWe creëren 2 asm volumes die qua grootte (ongeveer) gelijk zijn aan de primary DB
[grid@aj007 ~]$ asmcmd volcreate -G DATA -s 2T CA040SBDTA
[grid@aj007 ~]$ asmcmd volcreate -G DATA -s 500G CA040SBRDO
[grid@aj007 ~]$ asmcmd volinfo -G DATA CA040SBDTA
Diskgroup Name: DATA
Volume Name: CA040SBDTA
Volume Device: /dev/asm/ca040sbdta-239
State: ENABLED
Size (MB): 2097152
Resize Unit (MB): 64
Redundancy: MIRROR
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:
[grid@aj007 ~]$ asmcmd volinfo -G DATA CA040SBRDO
Diskgroup Name: DATA
Volume Name: CA040SBRDO
Volume Device: /dev/asm/ca040sbrdo-239
State: ENABLED
Size (MB): 512000
Resize Unit (MB): 64
Redundancy: MIRROR
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:
[grid@aj007 ~]$Op deze Volume Devices maken we een acfs filesystem aan.
[root@aj007 ~]# /sbin/mkfs -t acfs /dev/asm/ca040sbdta-239
mkfs.acfs: version = 19.0.0.0.0
mkfs.acfs: on-disk version = 46.0
mkfs.acfs: volume = /dev/asm/ca040sbdta-239
mkfs.acfs: volume size = 2199023255552 ( 2.00 TB )
mkfs.acfs: Format complete.
[root@aj007 ~]# /sbin/mkfs -t acfs /dev/asm/ca040sbrdo-239
mkfs.acfs: version = 19.0.0.0.0
mkfs.acfs: on-disk version = 46.0
mkfs.acfs: volume = /dev/asm/ca040sbrdo-239
mkfs.acfs: volume size = 536870912000 ( 500.00 GB )
mkfs.acfs: Format complete.
[root@aj007 ~]#We maken 2 directories aan en registreren de Volume Devices op deze directoeies m.b.v. acfsutil registry .
[root@aj007 ~]# mkdir -p /u02/app/oracle/oradata/CA040SB
[root@aj007 ~]# mkdir -p /u04/app/oracle/redo/CA040SB
[root@aj007 ~]# acfsutil registry -a /dev/asm/ca040sbdta-239 /u02/app/oracle/oradata/CA040SB
acfsutil registry: mount point /u02/app/oracle/oradata/CA04SB successfully added to Oracle Registry
[root@aj007 ~]# acfsutil registry -a /dev/asm/ca040sbrdo-239 /u04/app/oracle/redo/CA040SB
acfsutil registry: mount point /u04/app/oracle/redo/CA040SB successfully added to Oracle Registry
[root@aj007 ~]#Maak user oracle eigenaar van deze directories en controleer de schrijfrechten voor oracle. Dit is belangrijk voor rman.
[root@aj007 ~]# chown -R oracle. /u04/app/oracle/redo/CA040SB
[root@aj007 ~]# chown -R oracle. /u02/app/oracle/oradata/CA040SB
[oracle@aj007 ~]$ touch /u04/app/oracle/redo/CA040SB/rgs
[oracle@aj007 ~]$ touch /u02/app/oracle/oradata/CA040SB/rgs
[oracle@aj007 ~]$ rm /u04/app/oracle/redo/CA040SB/rgs
[oracle@aj007 ~]$ rm /u02/app/oracle/oradata/CA040SB/rgsDe files listener.ora en tnsnames.ora staan op een shared filesystem wat binnen een ODA gedeeld wordt, aanpassingen hoeven dus maar op 1 node doorgevoerd te worden.
Op een node van de primary database moet een entry worden toegevoegd . De SID_NAME in de entry moet verwijzen naar de RAC instance op deze node.
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CA040P)(SDU=32767)(SID_NAME=CA040P1)(ORACLE_HOME=/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_2)))Op een node waar de standby database wordt aangemaakt moet een entry worden toegevoegd . De SID_NAME in de entry moet verwijzen naar de RAC instance op deze node.
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=CA040SB)(SDU=32767)(SID_NAME=CA040SB1)(ORACLE_HOME=/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10)))Op beide nodes moet de tnsnames.ora worden aangepast om naar zowel de primary als de standby databases te kunnen connecteren via SQL*net.
# DG
CA919P=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CA040P)(UR=A)))
CA040SB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.7)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CA040SB)(UR=A)))Om de instance van de standby database aan te maken is een minimale init.ora file nodig. Op de 1e node (aj007) van de standby oda wordt file initCA040SB1.ora aangemaakt.
[oracle@aj007 ~]$ cat initCA040SB1.ora
db_name=CA040P
db_unique_name=CA040SB
sga_max_size=4G
[oracle@aj007 ~]$Start de database met behulp van de zojuist aangemaakte init.ora file en start in nomount.
[oracle@aj007 ~]$ export ORACLE_SID=CA040SB1
[oracle@aj007 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 29 08:13:10 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/initCA040SB1.ora';
ORACLE instance started.
Total System Global Area 4294963272 bytes
Fixed Size 8904776 bytes
Variable Size 3170893824 bytes
Database Buffers 1073741824 bytes
Redo Buffers 41422848 bytes
SQL>Kopieer de passwordfile van de primary DB naar de standby node.
[oracle@aj001 ~]$ scp /u02/app/oracle/oradata/CA040P/dbs/orapwCA040P aj007:/u02/app/oracle/oradata/CA040SB/dbs/orapwCA040SB
FIPS mode initialized
_____________________________________________________________
WARNING:
To protect the system from unauthorized use and to ensure
that the system is functioning properly, activities on this
system are monitored and recorded and subject to audit.
Use of this system is expressed consent to such monitoring
and recording. Any unauthorized access or use of this Auto-
mated Information System is prohibited and could be subject
to criminal and civil penalties.
Avinty IT Operations tel. +31 (0)88 0550600
_____________________________________________________________
orapwCA040P 100% 2048 2.0MB/s 00:00
[oracle@aj001 ~]$Test de SQL*Net connecties vanaf de 1e nodes van beide ODA's naar zowel de primary db als de standby db.
sqlplus sys/<password>CA040SB as sysdba
sqlplus sys/<password>@CA040P as sysdbaKijk op de primary database hoeveel redolog groupen en threads er zijn en hoe groot deze zijn.
CA040P>select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
_________ __________ _____________
1 1 1073741824
2 1 1073741824
3 2 1073741824
4 2 1073741824
CA040P> Controleer of er standby logfies aanwezig zijn voor alle threads met dezelfde grootte en één meer per thread dan v$log. Als deze niet aanwezig zijn moeten deze aangemaakt worden.
CA040P>select group#,thread#, bytes from v$standby_log;
GROUP# THREAD# BYTES
_________ __________ _____________
5 1 1073741824
6 1 1073741824
7 1 1073741824
8 2 1073741824
9 2 1073741824
10 2 1073741824
6 rows selected.
CA040P>In bovenstaand voorbeeld zijn er 4 GROUP# (1 t/m 4)voor de logfiles, verdeel over 2 GROUP# per THREAD# en 6 GROUP# (5 t/m 10) ook voor 2 THREAD#voor de standby logs.
Controleer parameters log_archive_confi en cluster_database, pas deze eventueel aan. In onderstaand voorbeeld was log_archive_config niet gezet en wordt deze aangepast, cluster_database is wel goed gezet en hoeft niet aangepast te worden.
CA040P>show parameter log_archive_config
NAME TYPE VALUE
------------------ ------ -----------
log_archive_config string nodg_config
CA040P>alter system set log_archive_config='DG_CONFIG=(CA040P,CA040SB)';
System SET altered.
CA040P>show parameter cluster_database
NAME TYPE VALUE
-------------------------- ------- -----
cluster_database boolean TRUE
CA040P> duplicate_CA040P.rmanHierna zijn de voorbereidingen voltooid.
Maak rman script duplicate_CA040P.rman aan
connect target sys/<password>@CA040P_DGMGRL
connect auxiliary sys/<password>@CA040SB_DGMGRL
run {
allocate channel PRM1 device type disk;
allocate channel PRM2 device type disk;
allocate channel PRM3 device type disk;
allocate channel PRM4 device type disk;
allocate channel PRM5 device type disk;
allocate channel PRM6 device type disk;
allocate channel PRM7 device type disk;
allocate channel PRM8 device type disk;
allocate auxiliary channel STB1 device type disk;
allocate auxiliary channel STB2 device type disk;
allocate auxiliary channel STB3 device type disk;
allocate auxiliary channel STB4 device type disk;
allocate auxiliary channel STB5 device type disk;
allocate auxiliary channel STB6 device type disk;
allocate auxiliary channel STB7 device type disk;
allocate auxiliary channel STB8 device type disk;
duplicate target database for standby from active database
dorecover
spfile
parameter_value_convert='CA040P','CA040SB'
set db_name='CA040P'
set db_unique_name='CA040SB'
set db_file_name_convert='/u02/app/oracle/oradata/CA040P/CA040P/','/u02/app/oracle/oradata/CA040SB/'
set log_file_name_convert='/u04/app/oracle/redo/CA040P/CA040P/', '/u04/app/oracle/redo/CA040SB/', '/u03/fast_recovery_area/CA040P/','/u03/fast_recovery_area/CA040SB/'
set dg_broker_start='true'
set audit_file_dest='/u01/app/odaorabase/oracle/admin/CA040SB/adump/'
set db_recovery_file_dest='/u03/fast_recovery_area'
set db_recovery_file_dest_size='42949672960'
set db_create_file_dest='/u02/app/oracle/oradata/CA040SB'
set instance_number='1'
set thread='1'
set undo_tablespace='UNDOTBS1'
reset log_archive_dest_2
;
release channel PRM1;
release channel PRM2;
release channel PRM3;
release channel PRM4;
release channel PRM5;
release channel PRM6;
release channel PRM7;
release channel PRM8;
release channel STB1;
release channel STB2;
release channel STB3;
release channel STB4;
release channel STB5;
release channel STB6;
release channel STB7;
release channel STB8;
} CONTROLFILE_CHANGE#Voer dit script uit in de directory waar het script aangemaakt is
rman @duplicate_CA040P.rman log=duplicate_CA040P.log trace=duplicate_CA040P.trcPas de database parameters aan op de zojuist aangemaakte STANDBY database.
ALTER SYSTEM SET instance_number=1 SCOPE=SPFILE SID='CA040SB1';
ALTER SYSTEM SET instance_number=2 SCOPE=SPFILE SID='CA040SB2';
ALTER SYSTEM SET thread=1 SCOPE=SPFILE SID='CA040SB1';
ALTER SYSTEM SET thread=2 SCOPE=SPFILE SID='CA040SB2';
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=SPFILE SID='CA040SB1';
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=SPFILE SID='CA040SB2';
ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.129.33)(PORT=1521))' SID='CA040SB1';
ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.129.34)(PORT=1521))' SID='CA040SB2';Controleer vervolgens of voor alle datafiles de CHECKPOINT_CHANGE# gelijk is aan de CONTROLFILE_CHANGE#, anders is de database niet te openen zonder een extra recovery actie.
SQL> select checkpoint_change#, controlfile_change# from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
_____________________ ______________________
9305300874506 9305305086544
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE# STATUS ENABLED
________ _________________________________________________________________________________________________________________ _____________________ _______________ _________ _____________
1 /u02/app/oracle/oradata/CA040SB/CA040SB/datafile/o1_mf_system_ll78ljvd_.dbf 9305305086544 SYSTEM READ WRITE
3 /u02/app/oracle/oradata/CA040SB/CA040SB/datafile/o1_mf_sysaux_ll78ljx7_.dbf 9305305086544 ONLINE READ WRITE
4 /u02/app/oracle/oradata/CA040SB/CA040SB/datafile/o1_mf_undotbs1_ll78ljy4_.dbf 9305305086544 ONLINE READ WRITE
5 /u02/app/oracle/oradata/CA040SB/CA040SB/FC947879427B1F3BE05301894B0A0753/datafile/o1_mf_system_ll78lk2s_.dbf 2780867 SYSTEM READ ONLY
6 /u02/app/oracle/oradata/CA040SB/CA040SB/FC947879427B1F3BE05301894B0A0753/datafile/o1_mf_sysaux_ll78lk6s_.dbf 2780867 ONLINE READ ONLY
7 /u02/app/oracle/oradata/CA040SB/CA040SB/FC947879427B1F3BE05301894B0A0753/datafile/o1_mf_undotbs1_ll78lk9g_.dbf 2780867 ONLINE READ ONLY
8 /u02/app/oracle/oradata/CA040SB/CA040SB/datafile/o1_mf_undotbs2_ll78lkg9_.dbf 9305305086544 ONLINE READ WRITE
14 /u02/app/oracle/oradata/CA040SB/CA040SB/datafile/o1_mf_users_ll78lkjp_.dbf 9305305086544 ONLINE READ WRITE
15 /u02/app/oracle/oradata/CA040SB/CA040SB/93DD0DA4CB354E95E0532164A8C04AC0/datafile/o1_mf_system_ll78llty_.dbf 9305305086544 SYSTEM READ WRITE
16 /u02/app/oracle/oradata/CA040SB/CA040SB/93DD0DA4CB354E95E0532164A8C04AC0/datafile/o1_mf_sysaux_ll78lt81_.dbf 9305305086544 ONLINE UNKNOWN
17 /u02/app/oracle/oradata/CA040SB/CA040SB/93DD0DA4CB354E95E0532164A8C04AC0/datafile/o1_mf_undotbs1_ll78lvr6_.dbf 9305305086544 ONLINE UNKNOWN
18 /u02/app/oracle/oradata/CA040SB/CA040SB/93DD0DA4CB354E95E0532164A8C04AC0/datafile/o1_mf_users_ll78lwd9_.dbf 9305305086544 ONLINE UNKNOWN
etc.In bovenstaand voorbeeld is CHECKPOINT_CHANGE# 9305305086544 gelijk is aan CONTROLFILE_CHANGE# 9305305086544 en is er GEEN aanvullende recovery nodig. Indien deze niet gelijk zijn krijg je bij hetread only openen van de standby database onderstaande foutmelding.
SQL> alter database open read only;
Error starting at line : 1 in command -
alter database open read only
Error report -
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u02/oradata/CA919SB/datafile/o1_mf_system_lkzq8hgd_.dbf'
10458. 00000 - "standby database requires recovery"
*Cause: A standby database was in a state requiring media recovery
when an attempt was made to open the standby database.
*Action: Perform the necessary recovery and open the standby database.
SQL>Als de standby database extra recovery nodig heeft moet met de functie scn_to_timestamp het tijdstip van dit scn achterhaald worden.
SQL> select scn_to_timestamp(9305305086544) from dual;
SCN_TO_TIMESTAMP(9305305086544)
__________________________________
06-OCT-23 12.10.05.000000000 PM
SQL>Alle archvelog files na deze timestamp zijn nodig voor recovery. Op PRIMARY NODE achterhaal je welke files dit zijn en deze kopieer je naar de STANDBY NODE
[oracle@ab037 2023_10_06]$ ls -ltr
total 985940
-rw-r----- 1 oracle asmadmin 349465600 Oct 6 08:06 o1_mf_1_753_lkz8z9px_.arc
-rw-r----- 1 oracle asmadmin 221171200 Oct 6 08:06 o1_mf_2_797_lkz8zcmt_.arc
-rw-r----- 1 oracle asmadmin 246784 Oct 6 08:07 o1_mf_1_754_lkz91s6x_.arc
-rw-r----- 1 oracle asmadmin 185344 Oct 6 08:07 o1_mf_2_798_lkz91tv3_.arc
-rw-r----- 1 oracle asmadmin 4425728 Oct 6 11:27 o1_mf_1_764_lkznr87r_.arc
-rw-r----- 1 oracle asmadmin 2994176 Oct 6 11:27 o1_mf_2_808_lkznr9p6_.arc
-rw-r----- 1 oracle asmadmin 11858432 Oct 6 12:22 o1_mf_2_809_lkzqyfgl_.arc
-rw-r----- 1 oracle asmadmin 412976640 Oct 6 12:22 o1_mf_1_765_lkzqyds5_.arc
[oracle@ab037 2023_10_06]$ scp o1_mf_2_809_lkzqyfgl_.arc ab031:/u03/fast_recovery_area/CA919DB/archivelogVoer een catalog uit om de nieuwe files op de STANDBY database bekend te maken bij rman.
RMAN> catalog start with '/u03/fast_recovery_area/CA919SB/archivelog';
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/CA919SB/archivelog/o1_mf_2_809_lkzqyfgl_.arc
File Name: /u03/fast_recovery_area/CA919SB/archivelog/o1_mf_1_765_lkzqyds5_.arcVoer hierna een recover database uit
RMAN> recover database;
Starting recover at 06-10-2023 12:45:58
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
starting media recovery
archived log for thread 1 with sequence 765 is already on disk as file /u03/fast_recovery_area/CA919SB/archivelog/o1_mf_1_765_lkzqyds5_.arc
archived log for thread 2 with sequence 809 is already on disk as file /u03/fast_recovery_area/CA919SB/archivelog/o1_mf_2_809_lkzqyfgl_.arc
archived log file name=/u03/fast_recovery_area/CA919SB/archivelog/o1_mf_1_765_lkzqyds5_.arc thread=1 sequence=765
archived log file name=/u03/fast_recovery_area/CA919SB/archivelog/o1_mf_2_809_lkzqyfgl_.arc thread=2 sequence=809
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-10-2023 12:46:12
RMAN>Hierna kan de database read only geopend worden.
SQL> alter database open read only;
Database altered.De rman duplicate is uitgevoerd op de 1e instance en hierdoor is de spfile en password file aangemaakt met een verkeerde naam. Ook bevat de spfile nog enkele verwijzingen naar de PRIMARY database instances. Maak een nieuwe spfile aan voor STANDBY en verwijder alle verwijzingen CA040P.xxxxx. Als het goed is zijn deze ook aanwezig voor CA040SB.xxxxx.
create pfile='/tmp/initCA040SB1.ora from spfile;
[oracle@aj007 tmp]$ vi initCA040SB1.ora
[oracle@aj007 tmp]$ export ORACLE_SID=CA040SB1
[oracle@aj007 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 9 11:45:10 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/initCA040SB1.ora';
ORACLE instance started.
Total System Global Area 6.8719E+10 bytes
Fixed Size 37472704 bytes
Variable Size 8187281408 bytes
Database Buffers 6.0130E+10 bytes
Redo Buffers 365178880 bytes
Database mounted.
SQL> create spfile='/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/spfileCA040SB.ora' from pfile='/tmp/initCA040SB1.ora';
File created.
SQL> shu immediateVervolgens gaan we de STANDBY database registreren bij het cluster.
srvctl add database -db CA040SB -oraclehome ${ORACLE_HOME} -dbtype RAC -s "read only"
srvctl modify database -db CA040SB -role PHYSICAL_STANDBY
srvctl add instance -db CA040SB -i CA040SB1 -n aj007
srvctl add instance -db CA040SB -i CA040SB2 -n aj008
srvctl modify database -db CA040SB -spfile /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/spfileCA040SB.ora
mv /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/orapwCA040SB1 /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/orapwCA040SB
srvctl modify database -db CA040SB -pwfile /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/orapwCA040SB
srvctl config database -d CA040SB
Database unique name: CA040SB
Database name:
Oracle home: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10
Oracle user: oracle
Spfile: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/spfileCA040SB.ora
Password file: /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_10/dbs/orapwCA040SB
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths: /u01/app/odaorahome
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dbaoper
Database instances: CA040SB1,CA040SB2
Configured nodes: aj007,aj008
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managedHierna is de database via srvctl te starten.
[oracle@aj007 tmp]$ srvctl status database -d CA040SB
Instance CA040SB1 is not running on node aj007
Instance CA040SB2 is not running on node aj008
[oracle@aj007 tmp]$ srvctl start instance -d CA040SB -i CA040SB1
[oracle@aj007 tmp]$ srvctl start instance -d CA040SB -i CA040SB2
[oracle@aj007 tmp]$ srvctl status database -d CA040SB
Instance CA040SB1 is running on node aj007
Instance CA040SB2 is running on node aj008
[oracle@aj007 tmp]$Als laatste stap moet de (centrale) tnsnames.ora op alle nodes aangepast worden, zodat ook alle instances van de STANDBY database benaderd kunnen worden.
CA040P=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CA040P)(UR=A)))
CA040SB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.7)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.75.137.8)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CA040SB)(UR=A)))Nadat de rman duplicate met succes uitgevoerd is wordt de op de primary node 1 de dataguard configuration ‘CA040P’ aangemaakt en wordt database 'CA040SB' toegevoegd.
[oracle@aj001 admin]$ dgmgrl sys/<<PW hier>>@CA040P
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Oct 9 12:01:22 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CA040P"
Connected as SYSDBA.
DGMGRL> create configuration 'CA040P' as primary database is 'CA040P' connect identifier is 'CA040P';
Configuration "CA040P" created with primary database "CA040P"
DGMGRL> add database 'CA040SB' as connect identifier is 'CA040SB';
Database "CA040SB" added
DGMGRL> enable configuration
Enabled.
DGMGRL> enable database CA040P
Enabled.
DGMGRL> enable database CA040SB
Enabled.Het is verstandig om na het aanmaken van de broker configuratie te controleren of alle instances bereikbaar zijn voor de broker.
DGMGRL> validate network configuration for all
Connecting to instance "CA040P1" on database "CA040P" ...
Connected to "CA040P"
Checking connectivity from instance "CA040P1" on database "CA040P" to instance "CA040SB1" on database "CA040SB"...
Succeeded.
Checking connectivity from instance "CA040P1" on database "CA040P" to instance "CA040SB2" on database "CA040SB"...
Succeeded.
Connecting to instance "CA040P2" on database "CA040P" ...
Connected to "CA040P"
Checking connectivity from instance "CA040P2" on database "CA040P" to instance "CA040SB1" on database "CA040SB"...
Succeeded.
Checking connectivity from instance "CA040P2" on database "CA040P" to instance "CA040SB2" on database "CA040SB"...
Succeeded.
Connecting to instance "CA040SB1" on database "CA040SB" ...
Connected to "CA040SB"
Checking connectivity from instance "CA040SB1" on database "CA040SB" to instance "CA040P1" on database "CA040P"...
Succeeded.
Checking connectivity from instance "CA040SB1" on database "CA040SB" to instance "CA040P2" on database "CA040P"...
Succeeded.
Connecting to instance "CA040SB2" on database "CA040SB" ...
Connected to "CA040SB"
Checking connectivity from instance "CA040SB2" on database "CA040SB" to instance "CA040P1" on database "CA040P"...
Succeeded.
Checking connectivity from instance "CA040SB2" on database "CA040SB" to instance "CA040P2" on database "CA040P"...
Succeeded.
Oracle Clusterware on database "CA040P" is available for database restart.
Oracle Clusterware on database "CA040SB" is available for database restart.
DGMGRL>De dataguard configuration kan nu gecontroleerd worden.
DGMGRL> show configuration
Configuration - CA040P
Protection Mode: MaxPerformance
Members:
CA040P - Primary database
CA040SB - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 3 seconds ago)
DGMGRL> show database CA040P
Database - CA040P
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CA040P1
CA040P2
Database Status:
SUCCESS
DGMGRL> show database CA040SB
Database - CA040SB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 10.71 MByte/s
Real Time Query: ON
Instance(s):
CA040SB1
CA040SB2 (apply instance)
Database Status:
SUCCESS
DGMGRL>In bovenstaand voorbeeld is te zien dat het APPLY proces op de 2e instance wordt uitgevoerd. Via SQL*Plus kan gecontroleerd worden of het MRP proces aktief is.
CA040SB>select process, status, sequence# from gv$managed_standby where inst_id=2;
PROCESS STATUS SEQUENCE#
__________ _______________ ____________
DGRD ALLOCATED 0
ARCH CONNECTED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 APPLYING_LOG 1064
11 rows selected.
CA040SB>Via de url https://www.ludovicocaldara.net/dba/script-to-check-data-guard-status-from-sql/ is een voorbeeld script (check_dg_config.sql) te vinden om de status van de data guard configuration te controleren. In onderstaande voorbeeld is dit script op PRIMARY en STANDBY uitgevoerd.
CA040P>@check_dg_config.sql
Checking Data Guard Configuration for CA040P
--------------------------------------
___OK: Current database has a Data Guard config.
___OK: Current database is enabled in Data Guard.
___OK: Data Guard status for the database is: SUCCESS
___OK: Primary (CA040P) is reachable.
___OK: LogXptStatus of primary is VALID.
_WARN: Flashback Logging is disabled.
___OK: The database is PRIMARY, skipping standby checks.
--------------------------------------
RESULT: _WARN: 0 errors - 1 warnings
CA040SB>@check_dg_config.sql
Checking Data Guard Configuration for CA040SB
--------------------------------------
___OK: Current database has a Data Guard config.
___OK: Current database is enabled in Data Guard.
___OK: Data Guard status for the database is: SUCCESS
___OK: Primary (CA040P) is reachable.
___OK: current DB (CA040SB) is reachable.
___OK: LogXptStatus of primary is VALID.
_WARN: Flashback Logging is disabled.
___OK: The database is STANDBY, executing standby checks.
___OK: The database intended state is APPLY-ON.
_WARN: Real Time Apply is used.
___OK: There are no PDBs with OFFLINE datafiles
___OK: apply lag is +00 00:00:00.000000
___OK: transport lag is +00 00:00:00.000000
--------------------------------------
RESULT: _WARN: 0 errors - 2 warningsAls laatste kan ook in de dictionary view gv$dataguard_status gecontroleerd worden of er fouten in de configuratie voorkomen.
CA040SB>SELECT gvi.thread#, timestamp, message
2 FROM gv$dataguard_status gvds, gv$instance gvi
3 WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal')
4* ORDER BY timestamp, thread#;
no rows selected
CA040SB>