oracle 学习之DG的搭建

1.配置过程

确认为归档模式

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

配置归档日志存放路径（/home/oracle/arch提前创建）

主库：
SQL>alter system set log_archive_dest_1='location=/home/oracle/arch

db_unique_name=ora11g';

备库：

SQL> alter system set log_archive_dest_2='service=s74

db_unique_name=bei';

将主库、备库加载入DG中

SQL> alter system set log_archive_config='dg_config=(ora11g,bei)';

System altered.

SQL> alter system set standby_file_management=auto;

System altered.

开启强制记录日志功能

拷贝全库至备库

查看文件存放位置（日志文件、数据文件、控制文件都在此目录下）：

SQL> select * from v$logfile;

拷贝重做日志文件、数据文件至从库：

SQL>scp   *  172.16.150.66:/home/oracle/standby

生成备库的控制文件，并拷贝至备库：

SQL> alter database create standby controlfile as '/home/oracle/bei.ctl';

[oracle@localhost ora11g]$scp /home/oracle/1.ctl   172.16.150.74:/home/oracle/standby

拷贝参数文件、口令文件至备库：

SQL> show parameter spfile;

[oracle@oracle dbs]$ scp /u01/oracle/product/11.2.0/dbs /spfileora11g.ora 172.16.150.74:/home/oracle/standby

[oracle@oracle dbs]$ scp /u01/oracle/product/11.2.0/dbs / orapwora11g/172.16.150.74:/home/oracle/standby

将参数文件、口令文件拷贝至$ORACLE_HOME/dbs

主库配置tnsnames.ora

[oracle@oracle network]$ cd $ORACLE_HOME/network/admin

ORACLE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = oracle.com)

    )

  )

s74=

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = bei)

    )

  )

修改备库中的参数文件

ora11g.__db_cache_size=281018368

ora11g.__java_pool_size=4194304

ora11g.__large_pool_size=4194304

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

ora11g.__pga_aggregate_target=301989888

ora11g.__sga_target=452984832

ora11g.__shared_io_pool_size=0

ora11g.__shared_pool_size=150994944

ora11g.__streams_pool_size=0

*.audit_file_dest='/home/oracle/standby/adump' #修改路径并创建目录

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/home/oracle/standby/bei.ctl'#修改路径并创建目录

*.db_block_size=8192

*.db_domain='com'

*.db_name='oracle'

*.db_recovery_file_dest='/home/oracle/standby/fast_recovery_area' #修改路径并创建目录

*.db_recovery_file_dest_size=4322230272

*.diagnostic_dest='/u01/app/oracle'  #修改路径并创建目录

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

*.local_listener='LISTENER_ORACLE'

*.log_archive_config='dg_config=(oracle,bei)'

*.log_archive_dest_1='location=/home/oracle/standby/arch db_unique_name=bei'

*.memory_target=754652928

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

db_unique_name=bei  #添加

db_file_name_convert='/u01/oracle/oradata/oracle','/home/oracle/standby' #添加

log_file_name_convert='/u02/oracle/oradata/oracle/','/home/oracle/standby' #添加

启动备库

重新生成spfile：

SQL> create spfile from pfile;

启动库：

SQL> alter database mount standby database;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE

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

PHYSICAL STANDBY

启动监听

测试联通

自连：sqlplus  sys/[email protected]:1521/bei as sysdba

连接对方库：sqlplus  sys/[email protected]:1521/ora11g as sysdba

备库开启实时归档日志功能

alter database recover managed standby database disconnect from session;

主库切换到备库

SQL> select database_role,switchover_status  from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PRIMARY      TO STANDBY

alter database commit to switchover to standby with session

shutdown

备库切换到主库

SQL> select database_role,switchover_status  from v$database;

DATABASE_ROLE     SWITCHOVER_STATUS

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

PHYSICAL STANDBY TO PRIMARY（SESSIONS ACTIVE）


alter database commit to switchover to primary with session

shutdown

2.遇到的问题

数据库完成主库、备库之间的切换，再次启动主库时，出现：

SQL> archive log list

Database log mode           Archive Mode

Automatic archival           Enabled

Archive destination           /home/oracle/standby/arch

Oldest online log sequence     58

Next log sequence to archive   58

Current log sequence           58

SQL> select sequence# from v$log;

SEQUENCE#
----------
58
0
0

原因：没有进行日志切换

备库变成主库后，切换日志，从库的sequence号并没有发生改变

　　　　主库如何相互切换

　　　　如何实现redo_log的同步

3.其它问题

db_name service_name unique_name之间的关系

SQL> show parameter name;

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string     oracle

db_unique_name                       string     oracle

global_names                         boolean     FALSE

instance_name                        string     ora11g

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string     oracle.com

db_name:
1.在DG中db_name一样而db_unique_name不一致
2.在RAC中各节点db_name一样而instance_name不一样
db_unique_name：
1.在DataGuard中，主备库拥有相同的DB_NAME，为了区别，就必须有不同的DB_UNIQUE_NAME
instance_name:
1.可以和db_name不一致
service_name
1.客户端连接数据库时采用服务名

tnsnames.ora

s74=       #

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.150.74)#服务器IP
    (PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = bei) #数据库服务名

    )

  )

db_domain的作用