快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

linux下oracle11gdg环境搭建

项目

企业建站必须是能够以充分展现企业形象为主要目的，是企业文化与产品对外扩展宣传的重要窗口，一个合格的网站不仅仅能为公司带来巨大的互联网上的收集和信息发布平台，创新互联面向各种领域：柔性防护网等成都网站设计、全网整合营销推广解决方案、网站设计等建站排名服务。


192.168.12.22（主）

192.168.12.23（从）

oracle sid

orcl

orcl

db_unique_name

uniquepdg

uniquesdg

tnsname

Tns_pdg

Tns_sdg


一、主库操作

确认主库是否打开归档、force logging

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> SELECT NAME,LOG_MODE,FORCE_LOGGING FROM v$DATABASE;

修改主库参数

SQL> create pfile='/u01/pfile.ora' from spfile;

SQL> alter system set db_unique_name=uniquepdg scope=both;

SQL> alter system set log_archive_config='dg_config=(uniquepdg,uniquesdg)' scope=both;

SQL> alter system set log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg' scope=spfile;

SQL> alter system set log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg' scope=both;

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;  //默认即是exclusive

SQL> alter system set log_archive_max_processes=10 scope=both;

主库上有关standby角色的参数

SQL> alter system set fal_server=tns_sdg scope=both;

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata' scope=spfile;

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata' scope=spfile;

SQL> alter system set standby_file_management=auto scope=both;

从库传送pfile参数

scp /u01/pfile2.ora 192.168.12.31:$ORACLE_HOME/dbs/

从主库拷贝密码文件

$ cd $ORACLE_HOME/dbs

$ scp orapworcl 192.168.12.23:$ORACLE_HOME/dbs/orapworcl

二、从库操作

创建相应目录

$ mkdir -p /u01/app/oracle/admin/orcl/adump

$ mkdir -p /u01/oradata/orcl

$ mkdir -p /u01/app/oracle/fast_recovery_area

$ mkdir -p /u01/arch

$ mkdir -p /u01/backup

备库以spfile启动

SQL> startup nomount  pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfile2.ora'

SQL> shutdown immediate;

SQL> startup nomount;

主库进行全备

rman target /

run {

allocate channel d0 type disk;

allocate channel d1 type disk;

backup format '/u01/backup02/full_t%t_s%s_p%p' database;

sql 'alter system archive log current';

backup format '/u01/backup02/arc_t%t_s%s_p%p' archivelog all;

release channel d0;

release channel d1;

}

创建备用控制文件

backup current controlfile for standby format '/u01/backup02/control01.ctl';

拷贝备份文件及备用控制文件到备库

$ cd /u01/backup

$ scp * 192.168.12.23:/u01/backup02

从库恢复控制文件

$ rman target /

RMAN> restore standby controlfile from '/u01/backup02/control01.ctl';

RMAN> alter database mount;

从备库还原数据文件

RMAN> restore database;

RMAN> recover database;----------------------------恢复完成后数据库处于mount状态

Connected.

SQL> select  open_mode  from  v$database;

OPEN_MODE

----------------------------------------

MOUNTED

主库：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata/orcl/sredo07.log' size 50M;

备库：

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/oradata2/orcl/sredo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/oradata2/orcl/sredo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/oradata2/orcl/sredo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/oradata2/orcl/sredo07.log' size 50M;

三、配置主备库监听及tns文件

主库监听文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orclpdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.221)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.0.221)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

备库监听文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orclsdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.222)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.0.222)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

主备库tns文件

$ more /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

TNS_PDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.0.221)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquepdg)

    )

  )

TNS_SDG =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.0.222)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = uniquesdg)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

备库打开只读模式

SQL> alter database open;         

//10g无法启动到read only模式,只能启动到mount模式

备库启动实时应用

SQL> alter database recover managed standby database using current logfile disconnect from session;

三、日常运维管理

DG环境的启动与关闭 

DG环境的关闭 

检查DG环境主备库的日志使用情况 

操作位置：主库&备库 

SQL> archive log list; 

主库与备库当前使用的日志编号相同

停主库的监听程序 

lsnrctl stop 

停备库的监听程序 

lsnrctl stop 

关闭主数据库 

SQL> shutdown immediate;

查看备库的开启模式 

SQL> select open_mode from v$database;  

如果发现当前数据库是read only with apply模式，则需要执行下面命令关闭归档日志应用程序，

如果发现是read only模式则直接关闭数据库即可。正常情况下备库应该时刻处于应用归档日志的模式。 

关闭备数据库的归档应用程序 

SQL> alter database recover managed standby database cancel; 

关闭备数据库 

SQL> shutdown immediate; 

这样，整个Data Guard环境就算是完整的关闭掉了...

DG环境的启动 

启动DG环境的主库 

sqlplus / as sysdba 

SQL> startup; 

SQL> select status from v$instance; 

启动主库的监听程序 

lsnrctl start 

启动DG环境的备库到mount或open状态 

sqlplus / as sysdba 

SQL> startup; 

SQL> startup mount; 

启动备库的监听程序 

lsnrctl start 

主库切换归档日志 

操作命令： 

SQL> alter system archive log current; 

查看备库是否有新应用过来的日志 

SQL> select sequence#,applied from v$archived_log; 

备库上开启归档日志应用进程 

SQL> alter database recover managed standby database disconnect from session; 

主库与备库验证当前redo log 

操作位置：主库&备库 

SQL> archive log list; 

如果此时发现主库与备库当前使用的redo日志的编号一致则说明重启的DG环境一切正常。 

这样，这个Data Guard环境就算是去正常的启动了... 

日常运维操作

 检查主备库是否存在GAP

主库检查current sequence#

SQL> select thread#,sequence#,status from v$log;

   THREAD#  SEQUENCE# STATUS

---------- ---------- ----------------

         1        847 INACTIVE

         1        848 INACTIVE

         1        849 CURRENT

主库检查LNS进程正在写的sequence#

SQL> select process,sequence#,status from v$managed_standby;

PROCESS    SEQUENCE# STATUS

--------- ---------- ------------

ARCH              847 CLOSING

ARCH              848 CLOSING

LNS               849 WRITING

备库检查正在应用的sequence#

SQL> SELECT PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

PROCESS      THREAD#  SEQUENCE# STATUS

--------- ---------- ---------- ------------

ARCH               1        848 CLOSING

ARCH               1        847 CLOSING

MRP0               1        849 APPLYING_LOG

RFS                0          0 IDLE

RFS                1        849 IDLE

检查是否存在GAP

SQL> SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;---由此可判断主备库无GAP，可进行正常switchover切换

set linesize 300

col DATABASE_ROLE  for 20

col DB_UNIQUE_NAME for a30 

col INSTANCE   for a30  

col OPEN_MODE  for a30

col PROTECTION_MODE  for a30

col PROTECTION_LEVEL   for a30

col SWITCHOVER_STATUS   for a30

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

配置文件内容说明

主库

orcl.__db_cache_size=264241152

orcl.__java_pool_size=4194304

orcl.__large_pool_size=71303168

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

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='segment2'

*.db_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUEPDG'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_server='TNS_SDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquepdg'

*.log_archive_dest_2='service=tns_sdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquesdg'

*.log_archive_max_processes=10

*.log_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata'

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

从库配置文件

orcl.__db_cache_size=327155712

orcl.__java_pool_size=4194304

orcl.__large_pool_size=8388608

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=314572800

orcl.__sga_target=465567744

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=117440512

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='segment2'

*.db_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata'

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='UNIQUESDG'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_server='TNS_PDG'

*.log_archive_config='dg_config=(uniquepdg,uniquesdg)'

*.log_archive_dest_1='location=/u01/arch valid_for=(all_logfiles,all_roles) db_unique_name=uniquesdg'

*.log_archive_dest_2='service=tns_pdg  async valid_for=(online_logfile,primary_role) db_unique_name=uniquepdg'

*.log_archive_max_processes=10

*.log_file_name_convert='/u01/app/oracle/oradata','/u01/app/oracle/oradata'

*.memory_target=780140544

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

主库监听

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquepdg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.23)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

从库监听

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = uniquesdg)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.12.23)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle


分享文章：linux下oracle11gdg环境搭建
网页URL：http://6mz.cn/article/jdpiho.html

其他资讯