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 dateDacă 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)

One comment

Lasă un răspuns

Completează mai jos detaliile despre tine sau dă clic pe un icon pentru autentificare:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s