Environmental Status:Two virtual hosts A and b:1. A machine has installed ASM storage for Oracle 11g instance reference: HTTP://WWW.CNBLOGS.COM/JYZHAO/P/4332410.HTML2. b machine installed system, configuration and directory structure are consistent with a machine/U01 + 3 ASM Disk
DG Deployment Planning:
|
Primary |
Standby |
Host |
Jy-db |
Jy-dbs |
Db_name |
Jyzhao |
Jyzhao |
Db_unique_name |
Jyzhao |
jyzhao_s |
Instance_name |
Jyzhao |
jyzhao_s |
Store |
+data1 |
+data1 |
Archive |
/u01/app/oracle/product/11.2.0/db_1/dbs/arch |
/u01/app/oracle/product/11.2.0/db_1/dbs/arch |
Dgmgrl |
Jyzhao_dgmgrl |
Jyzhao_s_dgmgrl |
Grid_home |
/u01/app/11.2.0/grid |
/u01/app/11.2.0/grid |
Oracle_home |
/u01/app/oracle/product/11.2.0/db_1 |
/u01/app/oracle/product/11.2.0/db_1 |
first, pre-preparation1.1. A machine pack copy/u01/app to B machine (includes grid and Oracle Software installation directory)
# tar -zcvf app.tar.gz app
# scp app.tar.gz 192.168.99.160:/u01/
[email protected] ‘s password:
app.tar.gz 100% 3564MB 54.8MB / s 01:05
Decompress the machine B. Make sure that the second operation is completed before decompressing.
# pwd
/ u01
[[email protected] u01] # ls
app.tar.gz lost + found
[[email protected] u01] # tar -zxvf app.tar.gz
After the extraction is complete, check that the permissions are correct
# ls -lh
total 3.5G
drwxrwxr-x. 7 oracle oinstall 4.0K Mar 13 14:47 app
-rw-r--r--. 1 root root 3.5G Mar 15 22:28 app.tar.gz
1.2. B machine configuration user, system parameters, installation dependent packages, user environment variables, ASM disk configuration user, system parameters, installation dependent packages, user environment variables Do n’t bother to change
root user execute script
# /u01/app/oraInventory/orainstRoot.sh
# /u01/app/11.2.0/grid/root.sh
# /u01/app/11.2.0/grid/perl/bin/perl -I / u01 / app / 11.2.0 / grid / perl / lib -I / u01 / app / 11.2.0 / grid / crs / install / u01 / app / 11.2.0 / grid / crs / install / roothas.pl
Configure has
Need to establish asm disk group
Environment variables:
vi $ ORACLE_HOME / dbs / init + ASM.ora
* .asm_diskstring = ‘/ dev / mapper / ora *’
* .asm_power_limit = 1
* .diagnostic_dest = ‘/ u01 / app / grid’
* .instance_type = ‘asm’
* .large_pool_size = 12M
* .remote_login_passwordfile = ‘EXCLUSIVE’
$ sqlplus / as sysdba
SQL * Plus: Release 11.2.0.4.0 Production on Mon Mar 16 10:51:02 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
$ crsctl stat res -t
-------------------------------------------------- ------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
-------------------------------------------------- ------------------------------
Local Resources
-------------------------------------------------- ------------------------------
ora.ons
OFFLINE OFFLINE jy-dbs
-------------------------------------------------- ------------------------------
Cluster Resources
-------------------------------------------------- ------------------------------
ora.cssd
1 OFFLINE OFFLINE
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE jy-dbs
$ crsctl start resource ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘jy-dbs’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘jy-dbs’
CRS-2676: Start of ‘ora.diskmon’ on ‘jy-dbs’ succeeded
CRS-2676: Start of ‘ora.cssd’ on ‘jy-dbs’ succeeded
$ crsctl status res -t
-------------------------------------------------- ------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
-------------------------------------------------- ------------------------------
Local Resources
-------------------------------------------------- ------------------------------
ora.ons
OFFLINE OFFLINE jy-dbs
-------------------------------------------------- ------------------------------
Cluster Resources
-------------------------------------------------- ------------------------------
ora.cssd
1 ONLINE ONLINE jy-dbs
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE jy-dbs
$ sqlplus / as sysasm
SQL * Plus: Release 11.2.0.4.0 Production on Mon Mar 16 10:55:39 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 1135747072 bytes
Fixed Size 2260728 bytes
Variable Size 1108320520 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
SQL> select status from v $ instance;
STATUS
------------------------
STARTED
col description for a35
col process for a35
set linesize 120
select sid, serial #, process, name, description from v $ session join v $ bgprocess using (paddr);
col path for a45
col name for a30
select group_number, disk_number, mount_status, name, path from v $ asm_disk order by group_number, disk_number;
GROUP_NUMBER DISK_NUMBER MOUNT_STATUS NAME PATH
------------ ----------- -------------- ------------- ----------------- --------------------------------- ------------
0 0 CLOSED / dev / mapper / ora_vg-lv_asm3
0 1 CLOSED / dev / mapper / ora_vg-lv_asm2
0 2 CLOSED / dev / mapper / ora_vg-lv_asm1
View the disk group information of machine A:
select group_number, name, type, total_mb, free_mb from v $ asm_diskgroup
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ -------------------------------------- ---------------------- ------------ ---------- ----------
1 DATA1 EXTERN 30708 29017
B machine creates ASM disk group DATA1:
select group_number, name, type, total_mb, free_mb from v $ asm_diskgroup;
no rows selected
CREATE DISKGROUP data1 EXTERNAL REDUNDANCY DISK ‘/ dev / mapper / ora *’;
Diskgroup created.
select group_number, name, type, total_mb, free_mb from v $ asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ -------------------------------------- ---------------------- ------------ ---------- ------ ----
1 DATA1 EXTERN 30708 30654
At this point, preparations are over.
Second, DG deployment configuration 1. Primary database configuration
Make sure that the database's force_logging is turned on, set to archive mode, and the database is flash back on
SQL> select name from v $ datafile;
NAME
-------------------------------------------------- ------------------------------
+ DATA1 / jyzhao / datafile / system.256.874084601
+ DATA1 / jyzhao / datafile / sysaux.257.874084601
+ DATA1 / jyzhao / datafile / undotbs1.258.874084601
+ DATA1 / jyzhao / datafile / users.259.874084601
SQL> select force_logging from v $ database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 1006636152 bytes
Database Buffers 603979776 bytes
Redo Buffers 7245824 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
$ mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs/arch
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest_size = 5G;
System altered.
SQL> alter system set db_recovery_file_dest = ‘/ u01 / app / oracle / product / 11.2.0 / db_1 / dbs / arch’;
System altered.
SQL> select status from v $ instance;
STATUS
------------
MOUNTED
SQL> alter database flashback on;
Database altered.
Set the parameters of the database as before
The main settings are db_unique_name, log_archive_config, log_archive_dest_1, log_archive_dest_2, log_archive_format, fal_server, fal_client, standby_file_management settings, standby logfile addition, and password file creation.
alter system set db_unique_name = ‘jyzhao’ scope = spfile;
alter system set log_archive_config = ‘DG_CONFIG = (jyzhao, jyzhao_s)’ scope = spfile;
alter system set log_archive_dest_1 = ‘LOCATION = / u01 / app / oracle / product / 11.2.0 / db_1 / dbs / arch VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = jyzhao‘ scope = spfile;
alter system set log_archive_dest_2 = ‘SERVICE = jyzhao_s ASYNC LGWR VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = jyzhao_s’ scope = spfile;
alter system set log_archive_format = ‘arch_% r_% t_% s.arc’ scope = spfile;
alter system set fal_server = jyzhao_s scope = spfile;
alter system set fal_client = jyzhao scope = spfile;
alter system set standby_file_management = AUTO;
alter database add standby logfile group 4 size 50M;
alter database add standby logfile group 5 size 50M;
alter database add standby logfile group 6 size 50M;
alter database add standby logfile group 7 size 50M;
rm /u01/app/oracle/product/11.2.0/db_1/dbs/orapwjyzhao
orapwd file = $ ORACLE_HOME / dbs / orapwjyzhao password = oracle entries = 10 ignorecase = Y
grid user configuration monitoring
--listener.ora
DGL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DB) (PORT = 1521))
)
SID_LIST_DGL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = jyzhao)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = jyzhao)
)
(SID_DESC =
(GLOBAL_DBNAME = jyzhao_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = jyzhao)
)
)
ADR_BASE_DGL = / u01 / app / grid
oracle user configuration tnsnames.ora
--tnsnames.ora
JYZHAO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DB) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyzhao)
)
)
JYZHAO_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DBS) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyzhao_s)
)
)
grid user restart monitoring:
lsnrctl stop dgl
lsnrctl start dgl
oracle user test connection:
sqlplus sys / [email protected] as sysdba
sqlplus sys / [email protected] / jyzhao_dgmgrl as sysdba
sqlplus sys / [email protected] / jyzhao as sysdba
SQL> show parameter audi
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
audit_file_dest string / u01 / app / oracle / admin / jyzhao / a
dump
Restart primary
shutdown immediate
startup
2. Machine B: standby database configuration: grid user configuration monitoring
--listener.ora
DGL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DBS) (PORT = 1521))
)
SID_LIST_DGL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = jyzhao_s)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = jyzhao_s)
)
(SID_DESC =
(GLOBAL_DBNAME = jyzhao_s_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = jyzhao_s)
)
)
ADR_BASE_DGL = / u01 / app / grid
grid users start listening
$ lsnrctl start dgl
oracle user configuration tnsnames.ora
--tnsnames.ora
JYZHAO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DB) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyzhao)
)
)
JYZHAO_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = JY-DBS) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jyzhao_s)
)
)
echo "db_name = jyzhao" >> $ ORACLE_HOME / dbs / initjyzhao_s.ora
echo $ ORACLE_SID
sqlplus / as sysdba
startup nomount
oracle user test connection:
sqlplus sys / [email protected] as sysdba
sqlplus sys / [email protected] _s as sysdba
sqlplus sys / [email protected] / jyzhao_s_dgmgrl as sysdba
sqlplus sys / [email protected] / jyzhao_s as sysdba
3. Machine A operates the duplicate database to machine B and verifies that machine B can log in
$ sqlplus sys / [email protected] _s as sysdba
vi duplicate_dg.sql
duplicate target database
for standby
from active database
DORECOVER
spfile
set db_unique_name = ‘jyzhao_s’
set log_archive_dest_1 = ‘location = / u01 / app / oracle / product / 11.2.0 / db_1 / dbs / arch VALID_FOR = (ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME = jyzhao_s ’
set log_archive_dest_2 = ‘SERVICE = jyzhao ASYNC LGWR
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = jyzhao ‘
set standby_file_management = ‘AUTO’
set fal_server = ‘jyzhao’
set fal_client = ‘jyzhao_s’
set control_files = ‘+ DATA1’
set memory_target = ‘0’
set sga_target = ‘600M’;
[[email protected] ~] $ rman target / auxiliary sys / [email protected] cmdfile = duplicate_standby.sql
Recovery Manager: Release 11.2.0.4.0-Production on Mon Mar 16 23:21:37 2015
Copyright (c) 1982, 2011, Oracle and / or its affiliates. All rights reserved.
connected to target database: JYZHAO (DBID = 2463175424)
connected to auxiliary database: JYZHAO (not mounted)
RMAN> duplicate target database
2> for standby
3> from active database
4> DORECOVER
5> spfile
6> set db_unique_name = ‘jyzhao_s’
7> set log_archive_dest_1 = ‘location = / u01 / app / oracle / product / 11.2.0 / db_1 / dbs / arch VALID_FOR = (ALL_LOGFILES, ALL_ROLES)
8> DB_UNIQUE_NAME = jyzhao_s ’
9> set log_archive_dest_2 = ‘SERVICE = MACDBN ASYNC LGWR
10> VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = jyzhao ‘
11> set standby_file_management = ‘AUTO’
12> set fal_server = ‘jyzhao’
13> set fal_client = ‘jyzhao_s’
14> set control_files = ‘+ DATA1’
15> set memory_target = ‘0’
16> set sga_target = ‘600M’;
17>
Starting Duplicate Db at 16-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID = 111 device type = DISK
...
Recovery Manager complete.
[[email protected] ~] $
4. Machine B srvctl add database jyzhao_s Note: There is a problem with _s because the specified parameters are not enough:
srvctl add database -d jyzhao_s -o /u01/app/oracle/product/11.2.0/db_1 -p + DATA1 / JYZHAO_S / spfilejyzhao_s.ora -n jyzhao -i jyzhao_s
srvctl modify database -d jyzhao_s -r PHYSICAL_STANDBY
3. DG Switching Test 3.1 Manually switchover 1 The main library is switched to standby and started to mount
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v $ database;
alter database commit to switchover to physical standby;
2. The backup is switched to primary and started to open
select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v $ database;
alter database commit to switchover to primary;
3. New backup execution log application
alter database recover managed standby database using current logfile disconnect from session;
3.2 Data Guard Broker fast switchover
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start = true;
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ---- --------------------------
dg_broker_start boolean TRUE
Configure dgmgrl
create CONFIGURATION jydb as primary database is jyzhao CONNECT IDENTIFIER IS jyzhao;
add database jyzhao_s as CONNECT IDENTIFIER IS jyzhao_s MAINTAINED AS PHYSICAL;
enable configuration;
show configuration;
switchover to jyzhao_s;
switchover to jyzhao;
show database verbose jydb
References: Data Guard Broker Data Guard Concepts and Administration http://t.askmaclean.com/thread-2530-1-1.html
Oracle Platform 11g DG Test Environment Quick Reference for Linux Platform