oracle dataguard搭建

   

Technorati 标签: oracle,dataguard,duplicate,主备,standby,primary

 

第一次搭建oracle dataguard。学oracle很长时间，却没有完整的搭过dg，说起来让人笑。总得有第一次，而且第一次总是很痛苦的。感谢庄哥和群内的朱老师的指点，也感谢公司同事标哥的支持。

     数据库版本：

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

一、两台机器平台信息

node248

[root@node248 ~]# uname -a
Linux node248.gewara 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[root@node248 ~]# hostname
node248.gewara
[root@node248 ~]# hostname -i
192.168.2.248

node249

Linux node249.gewara 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[root@node249 ~]# hostname
node249.gewara
[root@node249 ~]# hostname -i
192.168.2.249

二、node248建库

用dbca工具，用oracle自带的模板，不带sample schema，不用em，开启archive模式，同一管理密码oracle。global name：dbtest.node248.gewara，实例名：dbtest

三、配置主库

SQL>shutdown immediate

SQL>startup mount

SQL>alter database force logging;

SQL>alter database open;

SQL>create pfile=’/tmp/initdbtest.ora’ from spfile;

• 编辑/tmp/initdbtest.ora，添加如下参数

dbtest.__db_cache_size=671088640
dbtest.__java_pool_size=16777216
dbtest.__large_pool_size=16777216
dbtest.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dbtest.__pga_aggregate_target=671088640
dbtest.__sga_target=973078528
dbtest.__shared_io_pool_size=0
dbtest.__shared_pool_size=251658240
dbtest.__streams_pool_size=0
*._JOB_QUEUE_INTERVAL=120
*.audit_file_dest='/u01/app/oracle/admin/dbtest/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dbtest/control01.ctl','/u01/app/oracle/flash_recovery_area/dbtest/control02.ctl'
*.db_block_size=8192
*.db_domain='node248.gewara'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbtestXDB)'
*.memory_target=1639972864
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='db248'     #必须指定，并且跟备库不同

*.db_name='dbtest'  #数据库名，跟实例名最好一致，并且备库数据库名也一致
*.FAL_CLIENT='dbprimary'           #*.FAL两个参数，是为主备切换准备的，值都是tns中所配置的tnsname
*.FAL_SERVER='dbstandby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db248,db249)'   #指定主备库
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive/     #指定归档日志的存放路径，DB_UNIQUE_NAME为主库的DB_UNIQUE_NAME
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db248'
*.LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR ASYNC     #配置日志传输，DB_UNIQUE_NAME为备库的DB_UNIQUE_NAME
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=db249'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'   #归档日志命名规则*.STANDBY_FILE_MANAGEMENT='AUTO'      #备库文件管理方式为自动

• 用pfile启动数据库

SQL>shutdown immediate

SQL>startup pfile=’/tmp/initdbtest.ora’

SQL>create spfile from pfile=’/tmp/initdbtest.ora’

• 配置监听：修改$ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/products/11.2/db_1)
     (PROGRAM = extproc)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = dbtest.node248.gewara)
     (ORACLE_HOME = /u01/app/oracle/products/11.2/db_1)
     (SID_NAME = dbtest)
   )
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.248 )(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

配置好后，启动监听

$lsnrctl start

• 配置TNS，修改$ORACLE_HOME/network/admin/tnsnames.ora

DBSTANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.249)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbtest.node249.gewara)
    )
  )

DBPRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.248)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbtest.node248.gewara)
    )
  )

DBTEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.248)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbtest.node248.gewara)
    )
  )

• 重建密码文件:

主备库的密码文件必须保持一致，并且密码文件必须有固定的格式，具体为$ORACLE_HOME/dbs/orapw$ORACLE_SID

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10

密码文件修改后，重启数据库生效

SQL>shutdown immediate

SQL>startup

SYS@dbtest 00:52:32>select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                           TRUE  TRUE  FALSE

sys用户必须在v$pwfile_users中，并且

SYS@dbtest 00:52:32>show parameter passw

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile                   string     EXCLUSIVE

三、配置备库

• 创建catalog数据库

用dbca创建数据库，用oracle自带模板，不用em，启用归档，同一管理密码oracle，global name：dbcat1.node249.gewara，实例名：dbcat1

$export $ORACLE_SID=dbcat1

$sqlplus / as sysdba

SQL>create tablespace tbs_rman datafile ‘/u01/app/oracle/oradata/dbtest/tbs_rman01.dbf’ size 500m autoextned on next 1m extent management local;

SQL>create user rman identified by rman default tablespace tbs_rman temporary tablespace temp;

SQL>grant connect,resource,recovery_catalog_owner to rman;

SQL>conn rman/rman

SQL>show user

创建catalog

$rman

RMAN>connect catalog rman/rman@dbcat1;

RMAN>create catalog;

• 创建密码文件，或者直接将主库的密码文件复制过来，放在$ORACLE_HOME/dbs/下，更改文件属住为oracle:oinstall

orapwd file=$ORACLE_HOME/dbs/orapwdbtest password=oracle entries=10

• 配置TNS，修改$ORACLE_HOME/netword/admin/tnsnames.ora

DBCAT1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.249)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbcat1)
    )
  )

DBPRIMARY =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.248)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = dbtest.node248.gewara)
       (SID=dbtest)
      )
    )

DBSTANDBY =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.249)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = dbtest.node249.gewara)
     )
    )

• 配置监听

修改$ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = /u01/app/oracle/products/11.2/db_1)
   (PROGRAM = extproc)
  )
(SID_DESC =
    (GLOBAL_DBNAME = dbtest.node249.gewara)
    (ORACLE_HOME = /u01/app/oracle/products/11.2/db_1)
    (SID_NAME = dbtest)
)
(SID_DESC =
    (GLOBAL_DBNAME = dbcat1)
    (ORACLE_HOME = /u01/app/oracle/products/11.2/db_1)
    (SID_NAME = dbcat1)
)
)

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.249)( PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

启动监听

$lsnrctl start

• 测试tns是否配置成功

$tnsping dbprimary

$tnsping dbstandby

$tnsping dbcat1

• 为备库准备pfile

可以把之前为主库准备的pfile拷贝到node249上，做相应修改

# /tmp/initdbtest.ora

dbtest.__db_cache_size=671088640
dbtest.__java_pool_size=16777216
dbtest.__large_pool_size=16777216
dbtest.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dbtest.__pga_aggregate_target=671088640
dbtest.__sga_target=973078528
dbtest.__shared_io_pool_size=0
dbtest.__shared_pool_size=251658240
dbtest.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dbtest/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dbtest/control01.ctl','/u01/app/oracle/flash_recovery_area/dbtest/control02.ctl'
*.db_block_size=8192
*.db_domain='node249.gewara'
*.db_name='dbtest'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbtestXDB)'
*.memory_target=1639972864
*.open_cursors=300
*.processes=150
*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='db249'
*.FAL_CLIENT='dbstandby'
*.FAL_SERVER='dbprimary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(db248,db249)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archive/

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db249'  #这里的DB_UNIQUE_NAME为备库的DB_UNIQUE_NAME
*.LOG_ARCHIVE_DEST_2='SERVICE=dbprimary LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db248'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'

按照pfile中指定的目录，在oracle用户下，用mkdir –p 命令，创建所有需要的文件夹，保证所有目录的属主为oracle:oinstall。

• 启动备库到nomount状态：

$export ORACLE_SID=dbtest

$echo $ORACLE_SID

$sqlplus / as sysdba

SQL>startup nomount pfile=’/tmp/initdbtest.ora’

• 启动rman，用duplicate命令，建立备库

$rman

RMAN>connect catalog rman/rman@dbcat1

RMAN>connect target sys/oracle@dbprimary

RMAN>connect auxiliary sys/oracle@dbstandby

RMAN>register database;

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

四、添加standby logfile

• 主库：

SQL>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dbtest/stdy_redo04.log') size 10m;

SQL>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dbtest/stdy_redo05.log') size 10m;

SQL>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dbtest/stdy_redo06.log') size 10m;

SQL>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dbtest/stdy_redo07.log') size 10m;

• 备库：

SQL>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dbtest/stdy_redo04.log') size 10m;

SQL>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dbtest/stdy_redo05.log') size 10m;

SQL>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dbtest/stdy_redo06.log') size 10m;

SQL>alter database add standby logfile group 7 ('/u01/app/oracle/oradata/dbtest/stdy_redo07.log') size 10m;

SQL>shutdown immediate

SQL>startup nomount;

SQL>alter database mount standby database;

五、datagurad管理

• 启用dataguard

SQL>startup nomount;

SQL>alter database mount standby database;

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

• 切换为readonly状态

SQL>alter database recover managed standby database concel;

SQL>alter database open read only;

SQL>select * from dual;

• 主备切换：

主库端：

SQL>alter database commit to switchover to physical standby database with session shutdown;

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

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

备库端：

SQL>alter database commit to switchover to primary with session shutdown;

SQL>shutdown immediate

SQL>startup

 

trouble shooting:

ora-00845:

$su – root

#mount –t tmpfs shmfs –o size=3g /dev/shm