oracle备库搭建,ORACLE DataGuard 物理备库搭建

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/redo07.log') size 50M;

查看备库在线日志是否创建成功：

select group#, thread#, archived, status from v$standby_log;

5. 修改参数文件；

su - oracle

sqlplus / as sysdba

create pfile from spfile;

cd $ORACLE_HOME/dbs

vi initdgtest.ora

添加如下内容：

*.DB_UNIQUE_NAME='dgtest_pd'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/flash_recovery_area/DGTEST/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest_pd'

*.log_archive_dest_2='SERVICE=dgtest_st'

*.log_archive_dest_state_1=ENABLE

*.log_archive_dest_state_2=ENABLE

*.standby_file_management='AUTO'

*.standby_archive_dest='/u01/app/oracle/flash_recovery_area/DGTEST/archivelog'

*.FAL_SERVER='dgtest_pd'

*.FAL_CLIENT='dgtest_st'

*.LOG_ARCHIVE_MAX_PROCESSES=30

6. 使用新建的pfile启动数据库，并重建数据库

删除$ORACLE_HOME/dbs/spfiledgtest.ora

su - oracle

sqlplus / as sysdba

startup

create spfile from pfile

7. 创建备库控制文件：

alter database create standby controlfile as '/u01/standby.ctl';

8. 修改listener.ora, tnsnames.ora

listener.ora修改后的结果：

# listener.ora Network Configuration File: /u01/app/oracle/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 = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = dgtest)

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

(SID_NAME = dgtest)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

tnsnames.ora修改后的结果：

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DGTEST_PD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

DGTEST_ST =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

9. 关闭主库

su - oracle

sqlplus / as sysdba

shutdown immediate;

备库服务器操作：

1. 新建文件夹：

su - oracle

cd $ORACLE_BASE

mkdir -p oradata/dgtest

mkdir -p admin/dgtest/adump

mkdir -p admin/dgtest/bdump

mkdir -p admin/dgtest/cdump

mkdir -p admin/dgtest/dpdump

mkdir -p admin/dgtest/pfile

mkdir -p admin/dgtest/udump

mkdir -p flash_recovery_area/DGTEST/archivelog

mkdir -p flash_recovery_area/DGTEST/onlinelog

2. 从主库服务器复制文件至备库服务器：

a. 控制文件：

scp /u01/standby.ctl dataguard2:/u01/app/oracle/oradata/dgtest/control1.ctl;

在备库服务器上：

cp control01.ctl control02.ctl;

cp control02.ctl control03.ctl;

b. 数据文件：

scp /u01/app/oracle/oradata/dgtest/*.dbf  dataguard2:/u01/app/oracle/oradata/dgtest/*.dbf;

c. 在线日志文件：

scp /u01/app/oracle/oradata/dgtest/*.log  dataguard2:/u01/app/oracle/oradata/dgtest/*.log;

d. 密码文件：

cd $ORACLE_HOME/dbs

scp orapwdgtest dataguard2:/u01/app/oracle/product/10.2.0/db_1/dbs

e. 参数文件：

scp initdgtest.ora dataguard2:/u01/app/oracle/product/10.2.0/db_1/dbs

3. 修改参数文件：

*.DB_UNIQUE_NAME='dgtest_st'

*.log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/DGTEST/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgtest_st'

*.log_archive_dest_2='SERVICE=dgtest_pd'

*.log_archive_dest_state_1=ENABLE

*.log_archive_dest_state_2=ENABLE

*.standby_file_management='AUTO'

*.standby_archive_dest='/u01/app/oracle/flash_recovery_area/DGTEST/archivelog'

*.FAL_SERVER='dgtest_pd'

*.FAL_CLIENT='dgtest_st'

4. 从主库复制并修改listener.ora和tnsnames.ora

listener.ora修改后：

# listener.ora Network Configuration File: /u01/app/oracle/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 = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = dgtest)

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

(SID_NAME = dgtest)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

tnsnames.ora修改后内容：

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DGTEST_PD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

DGTEST_ST =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgtest)

)

)

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

Dataguard启动：

1. 启动备库

su - oracle

sqlplus / as sysdba

startup mout;

2. 启动备库日志实施进程：

alter database recover managed standby database disconnect from session;

3. 启动备库监听器：

lsnrctl start

4. 启动主库：

su - oracle

sqlplus / as sysdba

startup

5. 启动主库监听器：

lsnrctl start

检查：

1. 检查日志是否成功更新：

a. 查看备库日志状态：

select sequence#, first_time, next_time from v$archived_log;

b. 在主库上执行：

alter system switch logfile;

c. 检查备库上检查日志状态，是否与之前有变化：

select sequence#, first_time, next_time from v$archived_log;

d. 检查备库上日志是否实施：

select sequence#, applied from v$archived_log;

2. 数据检查：

a. 新建表空间：

在主库服务器：

sqlplus / as sysdba

create tablespace ts_test datafile '/u01/app/oracle/oradata/dgtest/ts_test.dbf' size 100M autoextend on next 10M;

alter system archive log current;

查看结果：

select * from v$dbfile;

在备库服务器检查是否同样新建了表空间：select * from v$dbfile;

b. 新建用户，表：

sqlplus / as sysdba;

create user test identified by test default tablespace ts_test;

grant dba to u_test;

create table test.t_test as select * from dba_tables;

select count(*) from test.t_test;

查看结果：

select count(*) from test.t_test;

在备库服务器检查：

切换成只读模式：

recover managed standby database cancel;

alter database open read only;

检查结果：

select count(*) from test.t_test;

切换会恢复管理模式：

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;