Oracle Data Guard asigură high availability, protecția datelor precum și recuperarea în caz de dezastru a datele. Data Guard furnizează un set cuprinzător de servicii care creează, mențin, gestionează și monitorizează una sau mai multe baze de date standby pentru a permite bazelor de date Oracle supraviețuirea în caz de dezastru și corupere de date. Dacă baza de date primară devine indisponibilă din cauza unei întreruperi neplanificată sau planificată atunci Data Guard poate trece orice bază de date standby în rolul de producție, minimizând astfel timpul de nefuncționare.
Oracle Data Guard broker este un framework distribuit care automatizează și centralizează crearea, întreținerea și monitorizarea configurației Data Guard.
Mai jos descriu pașii de configurare pentru Data Guard broker iar pentru ca nu am disponibil un mediu Data Guard voi crea unul folosindu-ma de o mașina Linux pe care rulează o instanta de baza de date Oracle 12.1.0.2, instanta de standby va rula pe aceeași mașina folosind același home Oracle.
Pentru a permite broker-ului sa repornească instantele este necesar ca listener.ora sa fie asemănător cu exemplu de mai jos:
[oracle@scott admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scott)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl_DGMGRL)
(ORACLE_HOME = /opt/oracle/product/12.1.0/dbhome)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl_dg_DGMGRL)
(ORACLE_HOME = /opt/oracle/product/12.1.0/dbhome)
(SID_NAME = orcl_dg)
)
)
[oracle@scott admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.1.0/dbhome/network/admin/tnsnames.ora
LISTENER =
(ADDRESS = (PROTOCOL = TCP)(HOST = scott)(PORT = 1521))
ORCL,orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scott)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_DG, orcl_dg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scott)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_dg)
(UR = A)
)
)
Crearea fișierului de inițializare parametrii pentru instanta de standby:
[oracle@scott ~]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 16:41:00 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create pfile=’/home/oracle/initorcl.ora’ from spfile;
File created.
[oracle@scott ~]$ cat initorcl.ora
*.audit_file_dest=’/opt/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.2.0′
*.control_files=’/u01/oracle/orcl/control01.ctl’,’/u01/oracle/FRA/orcl/control02.ctl’
*.db_block_size=8192
*.db_name=’orcl’
*.db_recovery_file_dest=’/u01/oracle/FRA’
*.db_recovery_file_dest_size=560m
*.diagnostic_dest=’/opt/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
*.local_listener=’LISTENER’
*.memory_target=1040m
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
[oracle@scott ~]$
[oracle@scott ~]$ cp initorcl.ora initorcl_dg.ora
[oracle@scott ~]$ mkdir /u01/oracle/orcl_dg/
[oracle@scott ~]$ mkdir /u01/oracle/FRA/orcl_dg
[oracle@scott ~]$ mkdir /opt/oracle/orcl_dg
[oracle@scott ~]$ mkdir -p /opt/oracle/admin/orcl_dg/adump
Am adus câteva modificări fișierului
[oracle@scott ~]$ cat initorcl_dg.ora
*.audit_file_dest=’/opt/oracle/admin/orcl_dg/adump’
*.audit_trail=’db’
*.compatible=’12.1.0.2.0′
*.control_files=’/u01/oracle/orcl_dg/control01.ctl’,’/u01/oracle/FRA/orcl_dg/control02.ctl’
*.db_block_size=8192
*.db_name=’orcl’
*.db_unique_name=orcl_dg
*.db_file_name_convert=’orcl’,’orcl_dg’
*.log_file_name_convert=’orcl’,’orcl_dg’
*.db_recovery_file_dest=’/u01/oracle/FRA’
*.db_recovery_file_dest_size=560m
*.diagnostic_dest=’/opt/oracle/orcl_dg’
*.local_listener=’LISTENER’
*.memory_target=1040m
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
Copiez fișierul de parole pentru cea de-a doua instanta:
[oracle@scott dbs]$ pwd
/opt/oracle/product/12.1.0/dbhome/dbs
[oracle@scott dbs]$ cp orapworcl orapworcl_dg
Daca nu am greșit pana acum putem pornit în nomount instanta de standby.
[oracle@scott dbs]$ env | grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/opt/oracle/
ORACLE_HOME=/opt/oracle/product/12.1.0/dbhome/
[oracle@scott dbs]$ export ORACLE_SID=orcl_dg
[oracle@scott ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 18:14:15 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’/home/oracle/initorcl_dg.ora’;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1090519040 bytes
Fixed Size 2923440 bytes
Variable Size 654312528 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes
SQL> alter system set dg_broker_start=true;
Crearea fișierelor Standby Redo Logs pe instanta primara:
[oracle@scott ~]$ export ORACLE_SID=orcl
[oracle@scott ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 18:21:38 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select group#,thread#,bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
–––- –––- –––––
1 1 50
2 1 50
3 1 50
SQL> select MEMBER from v$logfile;
MEMBER
–––––––––––––––––––––––––––
/u01/oracle/orcl/redo03.log
/u01/oracle/orcl/redo02.log
/u01/oracle/orcl/redo01.log
SQL> alter database add standby logfile ‘/u01/oracle/orcl/stdr_4.log’ size 50m;
Database altered.
SQL> alter database add standby logfile ‘/u01/oracle/orcl/stdr_5.log’ size 50m;
Database altered.
SQL> alter database add standby logfile ‘/u01/oracle/orcl/stdr_6.log’ size 50m;
Database altered.
SQL> select MEMBER from v$logfile;
MEMBER
–––––––––––––––––––––––––––
/u01/oracle/orcl/redo03.log
/u01/oracle/orcl/redo02.log
/u01/oracle/orcl/redo01.log
/u01/oracle/orcl/stdr_4.log
/u01/oracle/orcl/stdr_5.log
/u01/oracle/orcl/stdr_6.log
6 rows selected.
SQL> alter system set dg_broker_start=true;
System altered.
Duplicam instanta primara cu ajutorul RMAN-ului:
[oracle@scott ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcl_dg
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Aug 24 19:17:19 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1416202268)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Eroarea se rezolva prin adăugarea stringului „UR=A” in tnsnames.
[oracle@scott ~]$ rman target sys/oracle@orcl auxiliary sys/oracle@orcl_dg
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Aug 24 19:28:56 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1416202268)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 24-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/opt/oracle/product/12.1.0/dbhome/dbs/orapworcl’ auxiliary format
‘/opt/oracle/product/12.1.0/dbhome/dbs/orapworcl_dg’ ;
}
executing Memory Script
Starting backup at 24-AUG-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
Finished backup at 24-AUG-15
contents of Memory Script:
{
restore clone from service ‘orcl’ standby controlfile;
}
executing Memory Script
Starting restore at 24-AUG-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/orcl_dg/control01.ctl
output file name=/u01/oracle/FRA/orcl_dg/control02.ctl
Finished restore at 24-AUG-15
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/oracle/orcl_dg/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
„/u01/oracle/orcl_dg/system01.dbf”;
set newname for datafile 3 to
„/u01/oracle/orcl_dg/sysaux01.dbf”;
set newname for datafile 4 to
„/u01/oracle/orcl_dg/undotbs01.dbf”;
set newname for datafile 6 to
„/u01/oracle/orcl_dg/users01.dbf”;
restore
from service ‘orcl’ clone database
;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/oracle/orcl_dg/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-AUG-15
using channel ORA_AUX_DISK_1
Starting restore at 24-AUG-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/oracle/orcl_dg/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/oracle/orcl_dg/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/oracle/orcl_dg/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service orcl
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/oracle/orcl_dg/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 24-AUG-15
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=888607805 file name=/u01/oracle/orcl_dg/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=888607805 file name=/u01/oracle/orcl_dg/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=888607805 file name=/u01/oracle/orcl_dg/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=888607805 file name=/u01/oracle/orcl_dg/users01.dbf
Finished Duplicate Db at 24-AUG-15
Duplicarea s-a terminat cu succes. Acum mai trebuie configurat Data Guard Broker-ul.
[oracle@scott ~]$ dgmgrl sys/oracle@orcl
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type „help” for information.
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION dgmcfg AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl;
Configuration „dgmcfg” created with primary database „orcl”
DGMGRL> ADD DATABASE orcl_dg AS CONNECT IDENTIFIER IS orcl_dg MAINTAINED AS PHYSICAL;
Database „orcl_dg” added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration – dgmcfg
Protection Mode: MaxPerformance
Members:
orcl – Primary database
orcl_dg – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 17 seconds ago)
DGMGRL> show database orcl_dg
Database – orcl_dg
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: 102.00 KByte/s
Real Time Query: OFF
Instance(s):
orcl_dg
Database Status:
SUCCESS
Nu rămâne decât sa testam dacă funcționează:
DGMGRL> show configuration;
Configuration – dgmcfg
Protection Mode: MaxPerformance
Members:
orcl – Primary database
orcl_dg – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 53 seconds ago)
DGMGRL> switchover to orcl_dg;
Performing switchover NOW, please wait…
Operation requires a connection to instance „orcl_dg” on database „orcl_dg”
Connecting to instance „orcl_dg”…
Connected as SYSDBA.
New primary database „orcl_dg” is opening…
Operation requires start up of instance „orcl” on database „orcl”
Starting instance „orcl”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is „orcl_dg”
[oracle@scott ~]$ dgmgrl sys/oracle@orcl_dg
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type „help” for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration – dgmcfg
Protection Mode: MaxPerformance
Members:
orcl_dg – Primary database
orcl – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 20 seconds ago)
[oracle@scott ~]$ export ORACLE_SID=orcl
[oracle@scott ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 19:53:28 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
–––––––
MOUNTED
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@scott ~]$ export ORACLE_SID=orcl_dg
[oracle@scott ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 19:53:53 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
–––––––
READ WRITE
Incă un swich pentru a reveni la configurarea inițială:
[oracle@scott ~]$ dgmgrl sys/oracle@orcl_dg
DGMGRL> switchover to orcl;
Performing switchover NOW, please wait…
Operation requires a connection to instance „orcl” on database „orcl”
Connecting to instance „orcl”…
Connected as SYSDBA.
New primary database „orcl” is opening…
Operation requires start up of instance „orcl_dg” on database „orcl_dg”
Starting instance „orcl_dg”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is „orcl”
[oracle@scott ~]$ dgmgrl sys/oracle@orcl
DGMGRL for Linux: Version 12.1.0.2.0 – 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type „help” for information.
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration – dgmcfg
Protection Mode: MaxPerformance
Members:
orcl – Primary database
orcl_dg – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 47 seconds ago)
oradbaro 
1 comentariu