首页 > 代码库 > DG搭建物理备库
DG搭建物理备库
搭建过程难免会出现各种错误，所以之前最好做一个备份！！！
service iptables stop 关闭防火墙
[root@localhost ~]# setenforce 0
[root@localhost ~]# getenforce
Permissive
主库
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ db11g
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Nov 6 14:24:01 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 268436824 bytes
Database Buffers 352321536 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> show parameter db_uni
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_unique_name string jadl11g
SQL> alter system set db_unique_name=‘pjadl11g‘scope=spfile; ----更改主库的db_unique_name
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled ---归档模式
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
增加主库静态注册：
[oracle@localhost ~]$ asm
[oracle@localhost ~]$ srvctl stop listener
[oracle@localhost ~]$ srvctl start listener
[oracle@localhost ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 -Production on 06-NOV-2014 14:37:50
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version11.2.0.3.0 - Production
Start Date 06-NOV-2014 14:37:32
Uptime 0 days 0 hr. 0 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/grid/11g/network/admin/listener.ora
Listener Log File /u01/oracle/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 "jadl11g" has 1instance(s).
Instance "jadl11g", status UNKNOWN, has 1 handler(s) for thisservice...
Service "pjadl11g" has 1instance(s).
Instance "jadl11g", status UNKNOWN, has 1 handler(s) for thisservice...
Service "pjadl11g_DGMGRL" has 1instance(s).
Instance "jadl11g", status UNKNOWN, has 1 handler(s) for thisservice...
The command completed successfully
[oracle@localhost ~]$ db11g
[oracle@localhost ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 -Production on Thu Nov 6 14:38:58 2014
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: JADL11G(DBID=2088752907)
RMAN> backup database;---备份主库
Starting backup at 06-NOV-14
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
input datafile file number=00001name=/u01/oracle/oradata/jadl11g/system01.dbf
input datafile file number=00002name=/u01/oracle/oradata/jadl11g/sysaux01.dbf
input datafile file number=00005name=/u01/oracle/oradata/jadl11g/example01.dbf
input datafile file number=00003name=/u01/oracle/oradata/jadl11g/undotbs01.dbf
input datafile file number=00004name=/u01/oracle/oradata/jadl11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at06-NOV-14
channel ORA_DISK_1: finished piece 1 at06-NOV-14
piecehandle=/u01/oracle/fast_recovery_area/JADL11G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T143918_b5p5wrkb_.bkptag=TAG20141106T143918 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:01:27
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
including current control file in backupset
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at06-NOV-14
channel ORA_DISK_1: finished piece 1 at06-NOV-14
piecehandle=/u01/oracle/fast_recovery_area/JADL11G/backupset/2014_11_06/o1_mf_ncsnf_TAG20141106T143918_b5p5zhcy_.bkptag=TAG20141106T143918 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 06-NOV-14
RMAN> exit
Recovery Manager complete.
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Nov 6 14:41:47 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
SQL> create pfile from spfile; --创建参数文件
File created.
SQL> exit
Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
[oracle@localhost ~]$ cd /u01/oracle/11g/dbs/
[oracle@localhost dbs]$ ls
hc_jadl11g.dat init.ora orapwjadl11g spfilejadl11g.ora
initjadl11g.ora lkJADL11G snapcf_jadl11g.f
[oracle@localhost dbs]$ scp orapwjadl11g initjadl11g.ora192.168.1.184:/u01/oracle/11g/dbs/--复制主库的密码文件和参数文件到备库
The authenticity of host ‘192.168.1.184(192.168.1.184)‘ can‘t be established.
RSA key fingerprint is62:02:82:d1:dd:a3:f3:96:71:e5:c7:38:3c:fa:b4:c8.
Are you sure you want to continueconnecting (yes/no)? yes
Warning: Permanently added ‘192.168.1.184‘(RSA) to the list of known hosts.
oracle@192.168.1.184‘s password:
Permission denied, please try again.
oracle@192.168.1.184‘s password:
orapwjadl11g 100%1536 1.5KB/s 00:00
initjadl11g.ora 100% 984 1.0KB/s 00:00
主库增加备库日志：
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Nov 6 15:05:21 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
SQL> alter database add standby logfile group 10;---增加个数为：（线程数+1）*2
Database altered.
SQL> c/10/11
1* alter database addstandby logfile group 11
SQL> /
Database altered.
SQL> alterdatabase add standby logfile group 12;
Database altered.
SQL> alterdatabase add standby logfile group 13;
Database altered.
SQL> exit
Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
主库配置本地命名：
[oracle@localhost dbs]$ cat/u01/oracle/11g/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File:/u01/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
JADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.188)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
PJADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.188)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
SJADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.184)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
备库配置监听：
Netmgr
[oracle@localhost dbs]$ asm
[oracle@localhost dbs]$ srvctl stoplistener
[oracle@localhost dbs]$ srvctl startlistener
[oracle@localhost dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.3.0 -Production on 06-NOV-2014 14:50:32
Copyright (c) 1991, 2011, 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.3.0- Production
Start Date 06-NOV-2014 14:50:25
Uptime 0 days 0 hr. 0 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/grid/11g/network/admin/listener.ora
Listener Log File /u01/oracle/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 "jadl11g" has 1instance(s).
Instance "jadl11g", status UNKNOWN, has 1 handler(s) for thisservice...
Service "jadl11g_DGMGRL" has 1instance(s).
Instance "jadl11g", status UNKNOWN, has 1 handler(s) for thisservice...
Service "sjadl11g" has 1instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for thisservice...
The command completed successfully
[oracle@localhost dbs]$ vi init
initjadl11g.ora init.ora
[oracle@localhost dbs]$ vi initjadl11g.ora
修改db_unique_name为sjadl11g，其他不需要修改
备库建立相关文件夹：
[oracle@localhost dbs]$ grep "/u01" initjadl11g.ora---查看需要创建的文件，一定要创建相关文件，负责rman执行会出错的。
jadl11g.__oracle_base=‘/u01/oracle‘#ORACLE_BASEset from environment
*.audit_file_dest=‘/u01/oracle/admin/jadl11g/adump‘
*.control_files=‘/u01/oracle/oradata/jadl11g/control01.ctl‘,‘/u01/oracle/fast_recovery_area/jadl11g/control02.ctl‘
*.db_recovery_file_dest=‘/u01/oracle/fast_recovery_area‘
*.diagnostic_dest=‘/u01/oracle‘
[oracle@localhost dbs]$ ls/u01/oracle/admin/jadl11g/adump
ls: cannot access/u01/oracle/admin/jadl11g/adump: No such file or directory
[oracle@localhost dbs]$ mkdir/u01/oracle/admin/jadl11g/adump -p
[oracle@localhost dbs]$ ls/u01/oracle/oradata/jadl11g/
ls: cannot access/u01/oracle/oradata/jadl11g/: No such file or directory
[oracle@localhost dbs]$ ls/u01/oracle/oradata/
备库执行如下操作：
[oracle@localhost dbs]$ db11g
[oracle@localhost dbs]$ exportORACLE_SID=jadl11g
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onThu Nov 6 14:57:37 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 268436824 bytes
Database Buffers 352321536 bytes
Redo Buffers 3338240 bytes
SQL> create spfile from pfile;
File created.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 268436824 bytes
Database Buffers 352321536 bytes
Redo Buffers 3338240 bytes
SQL> exit
Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
主备库都需要配置本地命名：
[oracle@localhost dbs]$ vi /u01/oracle/11g/network/admin/tnsnames.ora
[oracle@localhost dbs]$ cat/u01/oracle/11g/network/admin/tnsnames.ora
JADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.188)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
SJADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.184)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
PJADL11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.188)(PORT = 1521))
(CONNECT_DATA =http://www.mamicode.com/
(SERVER = DEDICATED)
(SERVICE_NAME = jadl11g)
)
)
http://xxf-home.net/?p=139
http://blog.csdn.net/wyzxg/article/details/7280223
备库执行如下操作：
[oracle@localhost ~]$ rman target sys/123456@pjadl11g auxiliarysys/123456@sjadl11g
Recovery Manager: Release 11.2.0.3.0 -Production on Fri Nov 7 13:55:28 2014
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: JADL11G(DBID=2088752907)
connected to auxiliary database: JADL11G(not mounted)
RMAN> duplicate target database for standbynofilenamecheck from active database; --clone
Starting Duplicate Db at 07-NOV-14
using target database control file insteadof recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 devicetype=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/oracle/11g/dbs/orapwjadl11g‘ auxiliary format
‘/u01/oracle/11g/dbs/orapwjadl11g‘ ;
}
executing Memory Script
Starting backup at 07-NOV-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Finished backup at 07-NOV-14
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/oracle/oradata/jadl11g/control01.ctl‘;
restore clone controlfile to ‘/u01/oracle/fast_recovery_area/jadl11g/control02.ctl‘ from
‘/u01/oracle/oradata/jadl11g/control01.ctl‘;
}
executing Memory Script
Starting backup at 07-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output filename=/u01/oracle/11g/dbs/snapcf_jadl11g.f tag=TAG20141107T135544 RECID=8STAMP=863013345
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
Finished backup at 07-NOV-14
Starting restore at 07-NOV-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control filecopy
Finished restore at 07-NOV-14
contents of Memory Script:
{
sql clone ‘alter database mount standby database‘;
}
executing Memory Script
sql statement: alter database mount standbydatabase
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/oracle/oradata/jadl11g/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/oracle/oradata/jadl11g/system01.dbf";
set newname for datafile 2 to
"/u01/oracle/oradata/jadl11g/sysaux01.dbf";
set newname for datafile 3 to
"/u01/oracle/oradata/jadl11g/undotbs01.dbf";
set newname for datafile 4 to
"/u01/oracle/oradata/jadl11g/users01.dbf";
set newname for datafile 5 to
"/u01/oracle/oradata/jadl11g/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/oracle/oradata/jadl11g/system01.dbf" datafile
2auxiliary format
"/u01/oracle/oradata/jadl11g/sysaux01.dbf" datafile
3auxiliary format
"/u01/oracle/oradata/jadl11g/undotbs01.dbf" datafile
4auxiliary format
"/u01/oracle/oradata/jadl11g/users01.dbf" datafile
5auxiliary format
"/u01/oracle/oradata/jadl11g/example01.dbf" ;
sql ‘alter system archive log current‘;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to/u01/oracle/oradata/jadl11g/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 07-NOV-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001name=/u01/oracle/oradata/jadl11g/system01.dbf
output filename=/u01/oracle/oradata/jadl11g/system01.dbf tag=TAG20141107T135552
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002name=/u01/oracle/oradata/jadl11g/sysaux01.dbf
output filename=/u01/oracle/oradata/jadl11g/sysaux01.dbf tag=TAG20141107T135552
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005name=/u01/oracle/oradata/jadl11g/example01.dbf
output filename=/u01/oracle/oradata/jadl11g/example01.dbf tag=TAG20141107T135552
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003name=/u01/oracle/oradata/jadl11g/undotbs01.dbf
output filename=/u01/oracle/oradata/jadl11g/undotbs01.dbf tag=TAG20141107T135552
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004name=/u01/oracle/oradata/jadl11g/users01.dbf
output filename=/u01/oracle/oradata/jadl11g/users01.dbf tag=TAG20141107T135552
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
Finished backup at 07-NOV-14
sql statement: alter system archive logcurrent
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=863013428file name=/u01/oracle/oradata/jadl11g/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=863013428file name=/u01/oracle/oradata/jadl11g/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10STAMP=863013428 file name=/u01/oracle/oradata/jadl11g/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11STAMP=863013428 file name=/u01/oracle/oradata/jadl11g/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12STAMP=863013428 file name=/u01/oracle/oradata/jadl11g/example01.dbf
Finished Duplicate Db at 07-NOV-14
主备库同时执行如下操作
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production onFri Nov 7 13:59:09 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle LabelSecurity, OLAP, Data Mining,
Oracle Database Vault and Real ApplicationTesting options
SQL> show parameter dg_b
NAME TYPE VALUE
----------------------------------------------- ------------------------------
dg_broker_config_file1 string /u01/oracle/11g/dbs/dr1sjadl11g.dat
dg_broker_config_file2 string /u01/oracle/11g/dbs/dr2sjadl11g.dat
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;----打开主库、备库的DG broker
System altered.
备库执行如下操作：
[oracle@localhost ~]$ db11g
[oracle@localhost ~]$ dgmgrl sys/123456@pjadl11g ----连接到远程的主库上
DGMGRL for Linux: Version 11.2.0.3.0 -64bit Production
Copyright (c) 2000, 2009, Oracle. Allrights reserved.
Welcome to DGMGRL, type "help"for information.
Connected.
DGMGRL> help create
Creates a broker configuration
Syntax:
CREATE CONFIGURATION <configuration name> AS
PRIMARY DATABASE IS <database name>
CONNECT IDENTIFIER IS <connect identifier>;
DGMGRL> create configuration dgc as primary database ispjadl11g connect identifier is pjadl11g; --创建主库
Configuration "dgc" created withprimary database "pjadl11g"
DGMGRL> help add --帮助
Adds a standby database to the brokerconfiguration
Syntax:
ADDDATABASE <database name>
[AS CONNECT IDENTIFIER IS <connect identifier>]
[MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> add database sjadl11g as connect identifier issjadl11g maintained as physical; ---增加备库
Database "sjadl11g" added
DGMGRL> enable configuration ---是配置生效，自动修改一些参数
Enabled.
DGMGRL> show configuration ---查看DG是否成功搭建
Configuration - dgc
Protection Mode: MaxPerformance
Databases:
pjadl11g - Primary database
sjadl11g - Physical standbydatabase
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>DG搭建物理备库