配置DG物理备库

最新推荐文章于 2021-12-03 16:09:47 发布

cuje31551035

最新推荐文章于 2021-12-03 16:09:47 发布

阅读量116

点赞数

文章标签：数据库网络操作系统

测试环境

linux centos5.5

oracle版本：10.2.0.1.0

主库：192.168.0.22 hostname:pri.localdomain

备库：192.168.0.24 hostname:std.localdomain

修改主备库hosts文件

主库添加如下内容：

192.168.0.24 std.localdomain std

192.168.0.22 pri.localdomain pri

备库添加如下内容：

192.168.0.22 pri.localdomain pri

192.168.0.24 std.localdomain std

安装软件并创建数据库(以下创建路径以及文件属主均为oracle)

在主库22和备库24上分别安装oracle软件，并在主库22上创建实例名为dg的数据库，并设置为归档模式。

创建归档目录mkdir /opt/oracledata/arch

设置归档路径

alter system set log_archive_dest_1= “LOCATION=/opt/oracledata/arch” scope=spfile;

mount状态：

SQL> ALTER DATABASE FORCE LOGGING;

或

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

修改主库22 initdg.ora 文件

在文件后添加如下内容：

DB_UNIQUE_NAME=dg22

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg22,dg24)'

LOG_ARCHIVE_DEST_2=

'SERVICE=dg24 LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=dg24'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=dg24

FAL_CLIENT=dg22

STANDBY_FILE_MANAGEMENT=AUTO

创建standby日志文件

Adding a Standby Redo Log File Group to a Specific Thread

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5

2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Adding a Standby Redo Log File Group to a Specific Group Number

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

创建密码文件(dbca建库，不需要这步)

配置listener.ora和tnsnames.ora

$ORACLE_HOME/network/admin路径下配置

tnsnames.ora里面的service_name需要与initdg.ora(参数文件)中的DB_UNIQUE_NAME参数保持一致

启动监听lsnrctl start

配置完后，可以使用tnsping来判断是否配置正确

例主库上：tnsping dg22             tnsping dg24

备库上：tnsping dg22             tnsping dg24

或启动主库到open状态

主库上：sqlplus system/oracle@dg22

备库上：sqlplus system/oracle@dg22

主库：

listener.ora

# listener.ora Network Configuration File: /opt/oracle/10g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /opt/oracle/10g)

      (PROGRAM = extproc)

    )

)

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = pri.localdomain)(PORT = 1521))

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

    )

)

tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/10g/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

)

dg24=

(DESCRIPTION =

    (ADDRESS_LIST =

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

      (LOAD_BALANCE = on)

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg24)

      (FAILOVER_MODE =

                 (TYPE = SELECT)

                 (METHOD = BASIC)

                 (RETRIES = 180)

                 (DELAY = 5)

       )

    )

)

dg22=

(DESCRIPTION =

    (ADDRESS_LIST =

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

      (LOAD_BALANCE = on)

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg22)

      (FAILOVER_MODE =

                 (TYPE = SELECT)

                 (METHOD = BASIC)

                 (RETRIES = 180)

                 (DELAY = 5)

       )

    )

)

备库：

listener.ora

# listener.ora Network Configuration File: /opt/oracle/10g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /opt/oracle/10g)

      (PROGRAM = extproc)

    )

)

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = std.localdomain)(PORT = 1521))

    )

)

tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/10g/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

)

dg24=

(DESCRIPTION =

    (ADDRESS_LIST =

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

      (LOAD_BALANCE = on)

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg24)

      (FAILOVER_MODE =

                 (TYPE = SELECT)

                 (METHOD = BASIC)

                 (RETRIES = 180)

                 (DELAY = 5)

       )

    )

)

dg22=

(DESCRIPTION =

  (ADDRESS_LIST =

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

      (LOAD_BALANCE = on)

    )

    (CONNECT_DATA =

      (SERVICE_NAME = dg22)

      (FAILOVER_MODE =

                 (TYPE = SELECT)

                 (METHOD = BASIC)

                 (RETRIES = 180)

                 (DELAY = 5)

       )

    )

)

主库启动到mount状态

创建standby数据库控制文件

alter database create standby controlfile as '/opt/oracle/oradata/test.ctl';

重新创建spfile文件

create spfile from pfile=’ /opt/oracle/10g/dbs/initdg.ora’;

关闭数据库

创建standby数据库(oracle用户下)

需要将主库上创建的initdg.ora、standby的控制文件、密码文件orapwdg和数据文件传输到备库上去

在主库上：

将initdg.ora、standby的控制文件和密码文件orapwdg拷贝到admin目录下

cd $ORACLE_BASE

scp –r admin oradata std.localdomain:/opt/oracle

输入oracle用户密码即可

将归档文件传输过去

cd /opt/oracledata

scp –r arch std.localdomain:/opt/oracledata

配置standby数据库

将从主库拷贝到相应的路径下

修改initdg.ora文件

DB_UNIQUE_NAME=dg24

LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg22,dg24)'

LOG_ARCHIVE_DEST_2=

'SERVICE=dg22 LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=dg22'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

FAL_SERVER=dg22

FAL_CLIENT=dg24

STANDBY_FILE_MANAGEMENT=AUTO