CNDBA社区
1.准备工作
首先得准备两个能够PING通，并且装了Oracle软件且已经建库的Linux虚拟机。操作系统不限，至少有一个虚拟机已经建好库，最好两个都建好相同SID的库，这样少很多创建目录的麻烦，这里库的SID都是orcl，Linux严格区分大小写，所以SID的大小写得注意。
我这里有两个名为PD和ST的Linux虚拟机。
PD:192.168.56.5（主库）
ST:192.168.56.6（备库）
2.在主库做一些操作
2.1强制force logging
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes Database mounted. SQL> alter database force logging; --修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。 Database altered.
2.2开启主库的归档模式
SQL> alter database archivelog; --修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的。 Database altered.
2.3创建standby redo log
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;
Database altered
SQL> select group#,type,member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log
4 STANDBY /u01/app/oracle/oradata/orcl/redo04.log
5 STANDBY /u01/app/oracle/oradata/orcl/redo05.log
6 STANDBY /u01/app/oracle/oradata/orcl/redo06.log
7 STANDBY /u01/app/oracle/oradata/orcl/redo07.log
2.4创建standby控制文件
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/orcl/standby.ctl';--创建standby控制文件，将这个文件放到备库去，让备库知道自己的角色是备库,这里建立完standby控制文件之后，数据库不能open，否则做为备库的控制文件之后，备库不能开启。因为在主库创建standby控制文件后open主库，主库是不会更新standby控制文件，拷贝至备库后会导致数据库和控制文件不一致。从普通的数据库来考虑也不难理解，因为数据库文件和控制文件不一致，是需要介质恢复的。 Database altered.
2.5创建pfile
SQL> create pfile from spfile;--这里创建pfile是为了做一些主库参数的配置，并且还得拷贝到备库再次修改成备库的配置。 File created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
2.6创建主库归档目录
[oracle@PD orcl]$ mkdir archivelog --建立这个目录是为了存放主库的归档日志文件，并且这个目录会和其他数据文件等等一起拷贝到备库。 [oracle@PD orcl]$ cd archivelog/ [oracle@PD archivelog]$ ls [oracle@PD archivelog]$ pwd /u01/app/oracle/oradata/orcl/archivelog
2.7在主备库同时创建静态监听listener和tnsname
建议用net manager建立
主库orcl_pd:192.168.56.5
[oracle@PD admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@PD admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL_ST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.6)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCL_PD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.5)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) [oracle@PD orcl]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-SEP-2016 15:41:16 Copyright (c) 1991, 2013, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused LSNRCTL> start Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/PD/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PD)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 22-SEP-2016 15:40:16 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/PD/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PD)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully LSNRCTL> exit
备库orcl_pd:192.168.56.6
[oracle@ST admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@ST admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL_ST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.6)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCL_PD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.5)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) [oracle@ST dbs]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 22-SEP-2016 15:39:12 Copyright (c) 1991, 2013, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 2: No such file or directory Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused LSNRCTL> start Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/ST/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ST)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 22-SEP-2016 15:38:00 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ST/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ST)(PORT=1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
2.8修改主库pfile文件
[oracle@PD dbs]$ vi initorcl.ora
orcl.__db_cache_size=637534208
orcl.__java_pool_size=16777216
orcl.__large_pool_size=83886080
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=671088640
orcl.__sga_target=989855744
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=234881024
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=1657798656
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
--下面这个是为了搭建DG添加的配置参数，主备库是有区分的，请注意
DB_UNIQUE_NAME=orcl_pd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/oradata/orcl/archivelog
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl_pd'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl_st ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl_st'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=orcl_st
STANDBY_FILE_MANAGEMENT=AUTO
2.9用pfile启动主库，并创建spfile
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora'
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
SQL> create spfile from pfile;
File created.
2.10拷贝主库文件至备库相同目录
这里需要拷贝所有的数据文件，控制文件，日志文件，口令验证文件，归档日志文件，记得备库相同目录下的文件先删除。
[oracle@PD dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@PD dbs]$ scp initorcl.ora orapworcl 192.168.56.6:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ The authenticity of host '192.168.56.6 (192.168.56.6)' can't be established. RSA key fingerprint is 97:68:4f:02:c5:ef:57:30:b9:28:c4:7a:3d:0f:48:fb. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.6' (RSA) to the list of known hosts. oracle@192.168.56.6's password: Permission denied, please try again. oracle@192.168.56.6's password: initorcl.ora 100% 1358 1.3KB/s 00:00 orapworcl 100% 1536 1.5KB/s 00:00 [oracle@PD orcl]$ cd /u01/app/oracle/oradata/orcl [oracle@PD orcl]$ scp -r * 192.168.56.6:/u01/app/oracle/oradata/orcl --加上r就是把目录下的所有文件都拷贝过去，包括之前的archivelog目录 oracle@192.168.56.6's password: control01.ctl 100% 9520KB 9.3MB/s 00:00 example01.dbf 100% 313MB 52.2MB/s 00:06 redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 50.0MB/s 00:01 redo04.log 100% 50MB 50.0MB/s 00:01 redo05.log 100% 50MB 50.0MB/s 00:01 redo06.log 100% 50MB 50.0MB/s 00:01 redo07.log 100% 50MB 50.0MB/s 00:01 standby.ctl 100% 9520KB 9.3MB/s 00:01 sysaux01.dbf 100% 540MB 45.0MB/s 00:12 system01.dbf 100% 750MB 41.7MB/s 00:18 temp01.dbf 100% 29MB 14.5MB/s 00:02 undotbs01.dbf 100% 105MB 35.0MB/s 00:03 users01.dbf 100% 5128KB 5.0MB/s 00:00
3.备库操作
3.1在备库修改从主库拷贝来的pfile
[oracle@ST dbs]$ cat initorcl.ora orcl.__db_cache_size=637534208 orcl.__java_pool_size=16777216 orcl.__large_pool_size=83886080 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=671088640 orcl.__sga_target=989855744 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=234881024 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.memory_target=1657798656 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcl_st LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)' LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/oradata/orcl/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_st' LOG_ARCHIVE_DEST_2= 'SERVICE=orcl_pd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_pd' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orcl_pd STANDBY_FILE_MANAGEMENT=AUTO
3.2修改备库的控制文件
将主库拷贝过来的standby控制文件重命名成control01.ctl和control02.ctl，并替换原有的同名文件。
[oracle@ST orcl]$ pwd /u01/app/oracle/oradata/orcl [oracle@ST orcl]$ cp standby.ctl control01.ctl [oracle@ST orcl]$ cp standby.ctl /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
3.3利用pfile启动备库到nomount状态
看看备库启动后的数据库状态是否为read only
[oracle@ST orcl]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 15:58:40 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY
4.启动主库并检验
[oracle@PD dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 17:59:40 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> col error for a10 SQL> col dest_name for a20 SQL> select dest_name,status,process,error,transmit_mode from v$archive_dest where target='STANDBY'; DEST_NAME STATUS PROCESS ERROR TRANSMIT_MOD -------------------- --------- ---------- ---------- ------------ LOG_ARCHIVE_DEST_2 VALID LGWR ASYNCHRONOUS
5.开启备库的Apply Service，用来应用接收的归档日志文件
SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
6.建表测试创建是否成功
主库
SQL> create table Csong(id number(10),name varchar2(20)); Table created. SQL> insert into Csong values(1,'Csong'); 1 row created. SQL> insert into Csong values(2,'Lyuanyuan'); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
备库
SQL> select sequence#,applied from v$archived_log; SEQUENCE# APPLIED ---------- --------- 6 YES 7 YES 8 YES 9 YES 10 YES 11 YES 12 YES 7 rows selected. SQL> desc Csong Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(10) NAME VARCHAR2(20) SQL> select * from Csong; ID NAME ---------- -------------------- 1 Csong 2 Lyuanyuan
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
