Data Guard物理备库创建

主库：
IP： 192.168.219.20
主机名：node1
ORACLE_SID=dbpri
ORACLE_BASE=/opt/ora10g
ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1

备库：
IP： 192.168.219.131
主机名：node2
ORACLE_SID=dbstd
ORACLE_BASE=/opt/ora10g
ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1


1.节点1上用dbca创建数据库dbpri

2.将数据库改为force logging 模式

shutdown immediate;
startup mount;
alter database archivelog;
alter database force logging;

3.节点1上数据库创建standby controlfile，pfile

alter database create standby controlfile as '/opt/ora10g/product/10.2.0/db_1/dbs/dbstd.ctl';
  create pfile='/opt/ora10g/product/10.2.0/db_1/dbs/initdbpri.ora' from spfile;
  
4.节点1创建密码文件

shutdown immediate;

orapwd file=/opt/ora10g/product/10.2.0/db_1/dbs/orapwdbpri password=sys force=y ignorecase=y entries=5;

5.节点1创建standby redo log
 
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
 
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/ora10g/oradata/dbpri/stdbyredo01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/opt/ora10g/oradata/dbpri/stdbyredo02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/opt/ora10g/oradata/dbpri/stdbyredo03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/opt/ora10g/oradata/dbpri/stdbyredo04.log') SIZE 52428800;
 
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

6.将节点1数据库关闭，数据文件、参数文件、密码文件、控制文件复制到节点2                                                                                           
scp /opt/ora10g/oradata/dbpri/redo01.log       node2:/opt/ora10g/oradata/dbpri/                                                                                               
scp /opt/ora10g/oradata/dbpri/redo02.log       node2:/opt/ora10g/oradata/dbpri/                                                                                               
scp /opt/ora10g/oradata/dbpri/redo03.log       node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/stdbyredo01.log  node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/stdbyredo02.log  node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/stdbyredo03.log  node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/stdbyredo04.log  node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/sysaux01.dbf     node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/system01.dbf     node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/temp01.dbf       node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/undotbs01.dbf    node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/oradata/dbpri/users01.dbf      node2:/opt/ora10g/oradata/dbpri/
scp /opt/ora10g/product/10.2.0/db_1/dbs/dbstd.ctl    node2:/opt/ora10g/oradata/dbpri/control01.ctl
scp /opt/ora10g/product/10.2.0/db_1/dbs/dbstd.ctl    node2:/opt/ora10g/oradata/dbpri/control02.ctl
scp /opt/ora10g/product/10.2.0/db_1/dbs/orapwdbpri node2:/opt/ora10g/product/10.2.0/db_1/dbs/orapwdbstd        
scp /opt/ora10g/product/10.2.0/db_1/dbs/initdbpri.ora  node2:/opt/ora10g/product/10.2.0/db_1/dbs/initdbstd.ora
                 
7.修改节点1、2的参数文件

create spfile from pfile='/opt/ora10g/product/10.2.0/db_1/dbs/pfileprimary.ora';

 db_name是两个节点一样的(dbpri)
 db_unique_name是每个节点唯一的(dbpri\dbstd)
 log_archive_config 需要列出DG中所有节点的db_unique_name

两个节点上需要添加的参数包括：
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1
log_archive_dest_state_2
fal_server
log_file_name_convert
db_file_name_convert

8.修改listener.ora，tnsnames.ora

cd /opt/ora10g/product/10.2.0/db_1/network/admin
scp listener.ora node2:/opt/ora10g/product/10.2.0/db_1/network/admin/
scp tnsnames.ora node2:/opt/ora10g/product/10.2.0/db_1/network/admin/

建议使用netca创建。两个节点都需要创建dbpri、dbstd的tnsname

9.节点1数据库启动，节点2 mount standby database

节点1：
startup

节点2：
startup nomount
alter database mount standby database;

同时查看告警日志：
节点1：
tail -100f /opt/ora10g/diag/rdbms/primary/primary/trace/alert_primary.log
节点2：
tail -100f /opt/ora10g/diag/rdbms/standby/primary/trace/alert_primary.log
tail -100f /opt/ora10g/diag/rdbms/standby/standby/trace/alert_standby.log


10.节点2 实例接收归档

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
select max(sequence#) from v$archived_log;

若在节点2的归档目录看到归档日志自动生成，则搭建成功。


  查看数据库在DG中的角色：
  select DATABASE_ROLE from v$database;

以下为备注信息：
----------------------------------------------------------------------------------------------------
1.在两个节点上数据库未启动时，通过sqlplus / @dbstd as sysdba的方式连接会报错，是正常现象。
2.将数据复制到备库节点上后，当主库open、备库mount时，会自动将缺失的archive日志同步到备库的目录中。


pfile和spfile的同步
节点1：
create pfile='/opt/ora10g/product/10.2.0/db_1/dbs/initdbpri.ora' from spfile;
create spfile from pfile='/opt/ora10g/product/10.2.0/db_1/dbs/initdbpri.ora';
节点2：
create spfile from pfile='/opt/ora10g/product/10.2.0/db_1/dbs/initdbstd.ora';
create spfile from pfile='/opt/ora10g/product/10.2.0/db_1/dbs/initdbstd.ora';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~``
在主库和备库之间相互远程连接


当主库为open状态时，备库可以用sqlplus sys/sys@primary as sysdba方式登录；
当主库为shutdown状态是，备库sqlplus sys/sys@primary as sysdba方式登录，将报错：
[oracle@node2 admin]$ sqlplus sys/sys@primary as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 11 09:46:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges
类似的报错是正常的。当主库未启动时无法从远程连接成功。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~``

比较密码文件的方法：
1.使用strings命令分别查看两个节点上的密码文件
2.复制时，将名称改为与db_unique_name相对应的名称

节点1：
[oracle@node1 dbs]$ orapwd entries=5 force=y ignorecase=y file='/opt/ora10g/product/10.2.0/db_1/dbs/orapwdbpri';

Enter password for SYS: 

[oracle@node1 dbs]$ scp orapwdbpri node2:/opt/ora10g/product/10.2.0/db_1/dbs/orapwdbstd
orapwdbpri                                                                                                                            100% 2048     2.0KB/s   00:00

[oracle@node1 dbs]$ strings orapwdbpri 
]\[Z
ORACLE Remote Password file
INTERNAL
6A75B1BBE50E66AB
4DE42795E66117AE
F<      [

节点2：
[oracle@node2 dbs]$ strings orapwdbstd 
]\[Z
ORACLE Remote Password file
INTERNAL
6A75B1BBE50E66AB
4DE42795E66117AE
F<      [
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
监听信息：

[oracle@node1 ~]$ more /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /opt/ora10g

[oracle@node1 ~]$ more /opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = dbpri)
    )
  )

DBSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.131)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbstd)
    )
  )


节点2：
[oracle@node2 ~]$ more /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = PRIMARY)
      (ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
      (GLOBAL_DBNAME = primary)
    )
  )

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

ADR_BASE_LISTENER = /opt/ora10g

[oracle@node2 ~]$ more /opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.20)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbpri)
    )
  )


DBSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.219.131)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dbstd)
    )
  )

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
pfile
节点1：
[oracle@node1 ~]$ more /opt/ora10g/product/10.2.0/db_1/dbs/initdbpri.ora 
dbpri.__db_cache_size=281018368
dbpri.__java_pool_size=4194304
dbpri.__large_pool_size=4194304
dbpri.__oracle_base='/opt/ora10g'#ORACLE_BASE set from environment
dbpri.__pga_aggregate_target=297795584
dbpri.__sga_target=444596224
dbpri.__shared_io_pool_size=0
dbpri.__shared_pool_size=146800640
dbpri.__streams_pool_size=0
*.audit_file_dest='/opt/ora10g/admin/dbpri/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/ora10g/oradata/dbpri/control01.ctl','/opt/ora10g/flash_recovery_area/dbpri/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dbpri'
*.db_recovery_file_dest='/opt/ora10g/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='dbpri'
*.diagnostic_dest='/opt/ora10g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbpriXDB)'
*.fal_server='dbstd'
*.log_archive_config='dg_config=(dbpri,dbstd)'
*.log_archive_dest_1='location=/opt/ora10g/archive valid_for=(all_logfiles,all_roles)  db_unique_name=dbpri'
*.log_archive_dest_2='service=dbstd async valid_for=(online_logfiles,primary_role)  db_unique_name=dbstd'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.standby_file_management=auto
*.memory_target=741343232
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

节点2：
[oracle@node2 ~]$ more /opt/ora10g/product/10.2.0/db_1/dbs/initdbstd.ora 
dbpri.__db_cache_size=281018368
dbpri.__java_pool_size=4194304
dbpri.__large_pool_size=4194304
dbpri.__oracle_base='/opt/ora10g'#ORACLE_BASE set from environment
dbpri.__pga_aggregate_target=297795584
dbpri.__sga_target=444596224
dbpri.__shared_io_pool_size=0
dbpri.__shared_pool_size=146800640
dbpri.__streams_pool_size=0
*.audit_file_dest='/opt/ora10g/admin/dbpri/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/ora10g/oradata/dbpri/control01.ctl','/opt/ora10g/oradata/dbpri/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dbpri'
*.db_recovery_file_dest='/opt/ora10g/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.db_unique_name='dbstd'
*.diagnostic_dest='/opt/ora10g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbpriXDB)'
*.fal_server='dbpri'
*.log_archive_config='dg_config=(dbpri,dbstd)'
*.log_archive_dest_1='location=/opt/ora10g/archive valid_for=(all_logfiles,all_roles)  db_unique_name=dbstd'
*.log_archive_dest_2='service=dbpri async valid_for=(online_logfiles,primary_role)  db_unique_name=dbpri'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.arc'
*.standby_file_management=auto
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
==================================================================================

以下语句的查询结果：
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

node1中可以查到所有节点的结果，而node2只能查到自己。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-1142096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-1142096/