Oracle 11g DG主从复制集群搭建

oracle 11g DG主备安装手册

1 环境准备

1.1 硬件准备

3 DG配置

强制日志记录

#sqlplus 登录 sqlplus / as sysdba #先关闭数据库 SQL> shutdown immediate  #启动到nomount状态 SQL> startup nomount #在启动到mount状态； SQL> alter database mount; #配置为强日志记录 SQL> alter database force logging;   #修改数据库为强制记日志，这是必须的操作，主库的每一步操作都得记录到日志中去。 #修改为归档模式 SQL> alter database archivelog;   #修改数据库为归档模式，因为dg是通过传送归档日志到备库然后应用来保证主备库一致的

创建redo 日志文件

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/oracle/oradata/orcl/redo04.log') size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/oracle/oradata/orcl/redo05.log') size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/data/oracle/oradata/orcl/redo06.log') size 50M; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/data/oracle/oradata/orcl/redo07.log') size 50M;

查询日志文件

SQL> select group#,type,member from v$logfile;     GROUP# TYPE ---------- ------- MEMBER --------------------------------------------------------------------------------          3 ONLINE /data/oracle/oradata/orcl/redo03.log          2 ONLINE /data/oracle/oradata/orcl/redo02.log          1 ONLINE /data/oracle/oradata/orcl/redo01.log     GROUP# TYPE ---------- ------- MEMBER --------------------------------------------------------------------------------          4 STANDBY /data/oracle/oradata/orcl/redo04.log          5 STANDBY /data/oracle/oradata/orcl/redo05.log          6 STANDBY /data/oracle/oradata/orcl/redo06.log     GROUP# TYPE ---------- ------- MEMBER --------------------------------------------------------------------------------          7 STANDBY /data/oracle/oradata/orcl/redo07.log 7 rows selected.

创建pfile

SQL> create pfile from spfile;#这里创建pfile是为了做一些主库参数的配置，并且还得拷贝到备库再次修改成备库的配置。只要通过pfile才能在主备库之间建立联系（主库和备库中的pfile相似但有区别） #再次关闭数据库并退出 SQL> shutdown immediate SQL> exit

创建归档目录

#在目录/data/oracle/oradata/orcl 下创建（自定义，不固定）

cd /data/oracle/oradata/orcl mkdir archivelog

配置监听,监听文件在/data/oracle/product/11.2.0/db_1/network/admin 下面

[oracle@localhost ~]$ cd /data/oracle/product/11.2.0/db_1/network/admin [oracle@localhost admin]$ vi listener.ora # listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )   ) #SID_NAME = orcl 就是在安装oracle软件的配置文件db_install.rsp 和创建数据库的配置文件dbca.rsp 中配置的SID SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = orcl)       (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)       (SID_NAME = orcl)      )   ) ADR_BASE_LISTENER = /data/oracle

#配置tns文件

[oracle@localhost admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORA11G =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = ora11g)     )   ) ORCL =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) #该链接配置备库的链接，后面备库创建好了再配也可以 ORCL_ST =    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.40)(PORT = 1521))      )      (CONNECT_DATA =        (SERVICE_NAME =orcl)        (UR=A)     )    )  #该链接配置主库链接，和上面一样的 ORCL_PD =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME =orcl)       (UR=A)     )   )

#监听配置好之后重启监听

lsnrctl stop lsnrctl start lsnrctl status

验证tns是否配置成功，如果不报错就说明tns配置成功

[oracle@localhost admin]$ tnsping ORCL_PD [oracle@localhost admin]$ tnsping ORCL_ST

修改pfile文件,默认pfile文件在/data/oracle/product/11.2.0/db_1/dbs 下

[oracle@localhost ~]$  cd /data/oracle/product/11.2.0/db_1/dbs [oracle@localhost dbs]$ vi initorcl.ora orcl.__db_cache_size=822083584 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=402653184 orcl.__sga_target=1174405120 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=301989888 orcl.__streams_pool_size=0 *.audit_file_dest='/data/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data/oracle/oradata/orcl/control01.ctl','/data/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/data/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/data/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.open_cursors=300 *.pga_aggregate_target=386924544 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1160773632 *.undo_tablespace='UNDOTBS1' DB_UNIQUE_NAME=orcl_pd  LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)'  LOG_ARCHIVE_DEST_1=   'LOCATION=/data/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

再次关闭数据库，并通过pfile启动到nomount状态,并且创建spfile

[oracle@oracle-tmp dbs]$ sqlplus / as sysdba SQL> shutdown immediate SQL> startup nomount pfile='/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora' SQL> create spfile from pfile; SQL> alter database mount; SQL> alter database open;

创建主库的备份目录

mkdir /data/oracle/oradata/orcl/backup

将目录/data/oracle/product/11.2.0/db_1/dbs 下的口令验证文件orapworcl 和pfile文件initorcl.ora 发送到备库的相同目录

[oracle@oracle-2 ~]$ cd /data/oracle/product/11.2.0/db_1/dbs/ [oracle@oracle-2 dbs]$ scp ./initorcl.ora 192.168.56.40:/data/oracle/product/11.2.0/db_1/dbs/ [oracle@oracle-2 dbs]$ scp ./orapworcl 192.168.56.40:/data/oracle/product/11.2.0/db_1/dbs/

3.2 备库配置

配置备库的监听

[oracle@localhost ~]$ cd /data/oracle/product/11.2.0/db_1/network/admin [oracle@localhost admin]$ vi listener.ora # listener.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     )   ) #SID_NAME = orcl 就是在安装oracle软件的配置文件db_install.rsp 和创建数据库的配置文件dbca.rsp 中配置的SID SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (GLOBAL_DBNAME = orcl)       (ORACLE_HOME = /data/oracle/product/11.2.0/db_1)       (SID_NAME = orcl)      )   ) ADR_BASE_LISTENER = /data/oracle

#配置tns文件

[oracle@localhost admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORA11G =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = ora11g)     )   ) ORCL =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orcl)     )   ) #该链接配置备库的链接，后面备库创建好了再配也可以 ORCL_ST =    (DESCRIPTION =      (ADDRESS_LIST =        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.40)(PORT = 1521))      )      (CONNECT_DATA =        (SERVICE_NAME =orcl)        (UR=A)     )    )  #该链接配置主库链接，和上面一样的 ORCL_PD =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.30)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME =orcl)       (UR=A)     )   )

#监听配置好之后重启监听

lsnrctl stop lsnrctl start lsnrctl status

验证tns是否配置成功，如果不报错就说明tns配置成功

[oracle@localhost admin]$ tnsping ORCL_PD [oracle@localhost admin]$ tnsping ORCL_ST

#Oracle 11G静态监听，需要手动注册，Oracle 12C 之后就是动态监听，无需手动注册

sqlplus / as sysdba SQL>alter system register; System altered.

修改从主库发送过来的pfile（注意，此处黑色部分是从主库拷贝过来的）

[oracle@localhost ~]$  cd /data/oracle/product/11.2.0/db_1/dbs [oracle@localhost dbs]$ vi initorcl.ora orcl.__db_cache_size=822083584 orcl.__java_pool_size=16777216 orcl.__large_pool_size=16777216 orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=402653184 orcl.__sga_target=1174405120 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=301989888 orcl.__streams_pool_size=0 *.audit_file_dest='/data/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data/oracle/oradata/orcl/control01.ctl','/data/oracle/fast_recovery_area/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.db_recovery_file_dest='/data/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/data/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.open_cursors=300 *.pga_aggregate_target=386924544 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=1160773632 *.undo_tablespace='UNDOTBS1' *.log_file_name_convert='/data/oracle/oradata/orcl','/data/oracle/oradata/orcl' *.db_file_name_convert='/data/oracle/oradata/orcl','/data/oracle/oradata/orcl' DB_UNIQUE_NAME=orcl_st LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_pd,orcl_st)' LOG_ARCHIVE_DEST_1=   'LOCATION=/data/oracle/oradata/orcl/archivelog   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)    DB_UNIQUE_NAME=orcl_st'  LOG_ARCHIVE_DEST_2=   'SERVICE=orcl_st 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

备库

mkdir /data/oracle/oradata/orcl/backup

启动备库到nomount状态

sqlplus / as sysdba SQL> shutdown immediate SQL> startup nomount pfile="/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora" SQL> quit

使用rman 开始ADG

#使用rman链接主库和备库

[oracle@oracle-1 dbs]$ rman target sys/oracle@orcl_pd auxiliary sys/oracle@orcl_st

#开始同步

RMAN> duplicate target database for standby from active database nofilenamecheck; RMAN> exit;

备库开启apply service

[oracle@oracle-1 dbs] sqlplus / as sysdba  #打开数据库  SQL> alter database open;  #检查数据库状态  SQL> select open_mode from v$database;  OPEN_MODE  --------------------  READ ONLY  SQL> alter database recover managed standby database disconnect from session;

4 测试

在主库创建表并插入数据

SQL> create table Csong(id number(10),name varchar2(20)); SQL> insert into Csong values(1,'Csong');  SQL> insert into Csong values(3,'sc');  SQL> commit;  SQL> alter system switch logfile;

在备库检查是否同步

SQL> desc Csong; SQL> select * from Csong;