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;