通过DG_BROKE搭建Oracle11g_adg
1.环境
| | db_primary | db_stanby
| - | :-: | -: |-:
| db版本 | 11.2.0.4.0 | 11.2.0.4.0
| os版本 | centos 6.4| centos 6.4
| db_unique_name | newtest| snewtest
| db name | newtest | newtest
| ip | 10.10.0.23 | 10.10.0.24
2.主库配置过程
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 22:20:50 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2.1开启 force logging
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2.2开启归档
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL>
2.3password file 配置
[oracle@localhost dbs]$ export ORACLE_SID=newtest
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 22:32:40 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ sqlplus system/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 22:32:45 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost dbs]$ ll orapw*
-rw-r-----. 1 oracle dba 1536 Jan 31 20:18 orapwnewtest
#同步密码文件以及spfile文件到备库
[oracle@localhost dbs]$ scp orapwnewtest 10.10.0.24:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@10.10.0.24's password:
orapwnewtest
[oracle@localhost dbs]$ scp spfilenewtest.ora 10.10.0.24:/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@10.10.0.24's password:
spfilenewtest.ora
3. 网路配置
3.1主库上配置listener.ora
[oracle@localhost admin]$ vi listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = newtest)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1/)
(SID_NAME = newtest)
)
)
~
#启动监听
[oracle@localhost admin]$ lsnrctl restart
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 22:52:19
Copyright (c) 1991, 2013, Oracle. All rights reserved.
NL-00853: undefined command "restart". Try "help"
[oracle@localhost admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 22:52:25
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
[oracle@localhost admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 22:52:31
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 21-MAR-2018 22:48:21
Uptime 0 days 0 hr. 4 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "newtest" has 2 instance(s).
Instance "newtest", status UNKNOWN, has 1 handler(s) for this service...
Instance "newtest", status READY, has 1 handler(s) for this service...
Service "newtestXDB" has 1 instance(s).
Instance "newtest", status READY, has 1 handler(s) for this service...
The command completed successfully
3.2主库上配置tnsnames
[oracle@localhost admin]$ vi tnsnames.ora
NEWTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newtest)
)
)
SNEWTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.24)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newtest)
)
)
#测试连通性
[oracle@localhost admin]$ tnsping NEWTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 22:56:57
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.23)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = newtest)))
OK (20 msec)
[oracle@localhost admin]$ sqlplus system/oracle@NEWTEST
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 22:57:26 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
3.3备库上配置listener.ora
[oracle@localhost admin]$ vi listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = newtest)
(ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1/)
(SID_NAME = newtest)
)
)
3.2备库上配置tnsnames
[oracle@localhost admin]$ vi tnsnames.ora
NEWTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.23)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newtest)
)
)
SNEWTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.24)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newtest)
)
)
#测试连通性
[oracle@localhost admin]$ tnsping sNEWTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 23:12:09
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = newtest)))
OK (0 msec)
[oracle@localhost admin]$ tnsping NEWTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 23:12:13
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.23)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = newtest)))
OK (0 msec)
[oracle@localhost admin]$ tnsping SNEWTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 21-MAR-2018 23:12:16
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.0.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = newtest)))
OK (10 msec)
4.参数设置
4.1主库设置
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 23:15:17 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL>
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string newtest
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter system set standby_file_management=auto
2 ;
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start = true ;
System altered.
#可以看到dg_broker已经启动
SQL> !ps -ef|grep dmon
oracle 13362 1 0 23:16 ? 00:00:00 ora_dmon_newtest
oracle 13365 13357 0 23:16 pts/0 00:00:00 /bin/bash -c ps -ef|grep dmon
oracle 13367 13365 0 23:16 pts/0 00:00:00 grep dmon
4.2备库设置
[oracle@localhost dbs]$ vi initnewtest.ora
newtest.__db_cache_size=339738624
newtest.__java_pool_size=4194304
newtest.__large_pool_size=8388608
newtest.__oracle_base='/home/oracle/app'#ORACLE_BASE set from environment
newtest.__pga_aggregate_target=272629760
newtest.__sga_target=515899392
newtest.__shared_io_pool_size=0
newtest.__shared_pool_size=155189248
newtest.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/admin/newtest/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/app/oradata/newtest/control01.ctl','/home/oracle/app/fast_recovery_area/newtest/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newtest'
*.db_recovery_file_dest='/home/oracle/app/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.dg_broker_start=TRUE
*.diagnostic_dest='/home/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newtestXDB)'
*.memory_target=786432000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
db_unique_name=snewtest
#创建目录
[oracle@localhost dbs]$ cd /home/oracle/app/oradata/newtest/
-bash: cd: /home/oracle/app/oradata/newtest/: No such file or directory
[oracle@localhost dbs]$ mkdir -p /home/oracle/app/admin/newtest/adump
[oracle@localhost dbs]$ mkdir -p /home/oracle/app/oradata/newtest
[oracle@localhost dbs]$ mkdir -p /home/oracle/app/fast_recovery_area
[oracle@localhost dbs]$ mkdir -p /home/oracle/app/fast_recovery_area/newtest
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 23:32:48 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile ;
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2257352 bytes
Variable Size 478154296 bytes
Database Buffers 301989888 bytes
Redo Buffers 2596864 bytes
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string snewtest
SQL>
5 rman搭建standy
[oracle@localhost dbs]$ rman target sys/oracle@NEWTEST auxiliary sys/oracle@SNEWTEST nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 21 23:35:59 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: NEWTEST (DBID=1783795369)
using target database control file instead of recovery catalog
connected to auxiliary database: NEWTEST (not mounted)
RMAN> Duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 21-MAR-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwnewtest' auxiliary format
'/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwnewtest' ;
}
executing Memory Script
Starting backup at 21-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
Finished backup at 21-MAR-18
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/home/oracle/app/oradata/newtest/control01.ctl';
restore clone controlfile to '/home/oracle/app/fast_recovery_area/newtest/control02.ctl' from
'/home/oracle/app/oradata/newtest/control01.ctl';
}
executing Memory Script
Starting backup at 21-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_newtest.f tag=TAG20180321T233805 RECID=1 STAMP=971393885
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 21-MAR-18
Starting restore at 21-MAR-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 21-MAR-18
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
"/home/oracle/app/oradata/newtest/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/app/oradata/newtest/system01.dbf";
set newname for datafile 2 to
"/home/oracle/app/oradata/newtest/sysaux01.dbf";
set newname for datafile 3 to
"/home/oracle/app/oradata/newtest/undotbs01.dbf";
set newname for datafile 4 to
"/home/oracle/app/oradata/newtest/users01.dbf";
set newname for datafile 5 to
"/home/oracle/app/oradata/newtest/users02.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/home/oracle/app/oradata/newtest/system01.dbf" datafile
2 auxiliary format
"/home/oracle/app/oradata/newtest/sysaux01.dbf" datafile
3 auxiliary format
"/home/oracle/app/oradata/newtest/undotbs01.dbf" datafile
4 auxiliary format
"/home/oracle/app/oradata/newtest/users01.dbf" datafile
5 auxiliary format
"/home/oracle/app/oradata/newtest/users02.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/app/oradata/newtest/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 21-MAR-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oradata/newtest/sysaux01.dbf
output file name=/home/oracle/app/oradata/newtest/sysaux01.dbf tag=TAG20180321T233815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oradata/newtest/system01.dbf
output file name=/home/oracle/app/oradata/newtest/system01.dbf tag=TAG20180321T233815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oradata/newtest/users02.dbf
output file name=/home/oracle/app/oradata/newtest/users02.dbf tag=TAG20180321T233815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oradata/newtest/users01.dbf
output file name=/home/oracle/app/oradata/newtest/users01.dbf tag=TAG20180321T233815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oradata/newtest/undotbs01.dbf
output file name=/home/oracle/app/oradata/newtest/undotbs01.dbf tag=TAG20180321T233815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 21-MAR-18
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=971394136 file name=/home/oracle/app/oradata/newtest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=971394136 file name=/home/oracle/app/oradata/newtest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=971394136 file name=/home/oracle/app/oradata/newtest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=971394136 file name=/home/oracle/app/oradata/newtest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=971394136 file name=/home/oracle/app/oradata/newtest/users02.dbf
Finished Duplicate Db at 21-MAR-18
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 23:43:33 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
6.主备库上漏掉了 log文件配置,导致log没有同步(问题1)
6.1 主库上创建log 租
SQL> desc v$standby_log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
DBID VARCHAR2(40)
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
BLOCKSIZE NUMBER
USED NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(10)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
SQL> select * from v$standby_log;
no rows selected
SQL> alter database add standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 5;
Database altered.
SQL> alter database add standby logfile group 6;
Database altered.
SQL> alter database add standby logfile group 7;
Database altered.
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /home/oracle/app/oracle/produc
t/11.2.0/dbhome_1/dbs/dr1snewt
est.dat
dg_broker_config_file2 string /home/oracle/app/oracle/produc
t/11.2.0/dbhome_1/dbs/dr2snewt
est.dat
dg_broker_start boolean TRUE
SQL> !ps -ef |grep dmon
oracle 13978 1 0 23:33 ? 00:00:00 ora_dmon_newtest
oracle 14051 14047 0 23:45 pts/2 00:00:00 /bin/bash -c ps -ef |grep dmon
oracle 14053 14051 0 23:45 pts/2 00:00:00 grep dmon
7.配置 dgmgrl
[oracle@localhost dbs]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create configuration dg_newtest as primary database is newtest connect identifier is newtest;
Configuration "dg_newtest" created with primary database "newtest"
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enble configuration;
Unrecognized command "enble", try "help"
DGMGRL> enable configuration ;
Enabled.
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
Warning: ORA-16789: standby redo logs not configured
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL> exit
这里又报了一个错:原来刚才只给主库配置了 redo logs 备库上遗漏了
#备库上配置 redo logs
[oracle@localhost dbs]$
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 23:50:38 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter database add standby logfile group 4;
Database altered.
SQL> alter database add standby logfile group 5;
Database altered.
SQL> alter database add standby logfile group 6 ;
Database altered.
SQL> alter database add standby logfile group 7;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
再次查看dgmgrl 配置
[oracle@localhost dbs]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> exit
现在么问题,success
查看主备的log_archive_dest参数,发现分别在主备的 log_archive_dest_1和 log_archive_dest_2上做了配置
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 21 23:52:35 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set linesize 200
SQL> show parameter log_archive_desc_1
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST, valid_for=(ALL_LOGFILES,
ALL_ROLES)
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18 string
log_archive_dest_19 string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="snewtest", LGWR ASYNC
NOAFFIRM delay=0 optional com
pression=disable max_failure=0
max_connections=1 reopen=300
db_unique_name="snewtest" net_
timeout=30, valid_for=(all_log
files,primary_role)
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
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
SQL>
Add database to broker
[oracle@localhost dbs]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> add database snewtest as connect identifier is snewtest maintained as physical;
Database "snewtest" added
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
snewtest - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database snewtest;
Enabled.
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
snewtest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
主备机更改local_listener
#主机
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL> alter system set local_listener=newtest;
System altered.
#备机
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SQL> alter system set local_listener=snewtest;
System altered.
8 测试ADG
主库创建表 插入数据
SQL> create table test (id int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
备库open 查询数据
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database open;
Database altered.
SQL> select *from test;
ID
----------
1
主库再次插入
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
查询备库
SQL> select *from test;
ID
----------
1
2
adg 功能实现！
