在主库中netca配置
[oracle@HE3dbs]$ cat /u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora
#listener.ora Network Configuration File:/u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora
#Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pri)
(ORACLE_HOME =/u01/app/oracle/product/11gr2/db_1)
(SID_NAME = orcl)
)
)
LISTENER=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE3)(PORT = 1521))
)
ADR_BASE_LISTENER= /u01/app/oracle
[oracle@HE3dbs]$ cat /u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora
#tnsnames.ora Network Configuration File:/u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora
#Generated by Oracle configuration tools.
PRIME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = std)
)
)
在备库中netca配置
[oracle@HE4dbs]$ cat /u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = std)
(ORACLE_HOME =/u01/app/oracle/product/11gr2/db_1)
(SID_NAME = orcl)
)
)
LISTENER=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE4)(PORT = 1521))
)
ADR_BASE_LISTENER= /u01/app/oracle
[oracle@HE4dbs]$ cat /u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora
PRIME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)
STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =HE4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = std)
)
)
在主备库分别执行
[oracle@HE3dbs]$ tnsping prime
[oracle@HE3dbs]$ tnsping stdby
[oracle@HE4dbs]$ tnsping prime
[oracle@HE4dbs]$ tnsping stdby
若ping不通,重启监听服务试试
配置主库为归档模式
SQL> archive loglist
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u01/app/oracle/product/11gr2/db_1/dbs/arch
Oldest online logsequence 52
Next log sequence toarchive 54
Current logsequence 54
更改主库为强制写日志
SQL> alter databaseforce logging;
数据库已更改。
SQL> selectforce_logging from v$database;
FORCE_LOGGING
---------------
YES
主库中创建initorcl.ora文件
SQL> create pfilefrom spfile;
文件已创建。
pfile文件位于/u01/app/oracle/product/11gr2/db_1/dbs
编辑initdb1.ora中的参数
[oracle@HE3 dbs]$cat initorcl.ora
orcl.__db_cache_size=440401920
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
orcl.__pga_aggregate_target=213909504
orcl.__sga_target=633339904
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=176160768
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=210763776
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=pri
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,std)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/11gr2/db_1/dbs/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=stdby
*.FAL_CLIENT=prime
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
上一步中pfile的内容改变了,相应地要改变spfile的内容。方法是新建一个spfile。
SQL> shutdownimmediate
SQL> startuppfile='/u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora'
SQL> createspfile from pfile;
在主库上创建备库需要的控制文件
SQL> shutdownimmediate
SQL> startupmount
SQL> alterdatabase create standby controlfile as '/tmp/standby.ctl';
SQL> shutdownimmediate
备份生产数据库
(1)在备库中创建oradata目录和admin目录
[oracle@HE4~]$ mkdir /u01/app/oracle/oradata
[oracle@HE4~]$ mkdir /u01/app/oracle/admin
(2)将主库数据库文件拷贝到备库机器上
[oracle@HE3~]$ scp -rp /u01/app/oracle/oradata/orcl HE4:/u01/app/oracle/oradata
[oracle@HE3~]$ scp -rp /u01/app/oracle/admin/orcl HE4:/u01/app/oracle/admin
(3)将主库的密码文件和pfile文件拷贝到备库
[oracle@HE3~]$ cd $ORACLE_HOME/dbs
[oracle@HE3dbs]$orapwd file=orapwdb1 password=MANAGER force=y ignorecase=y
[oracle@HE3dbs]$ scp -rp /u01/app/oracle/product/11gr2/db_1/dbs/orapworcl initorcl.oraHE4:/u01/app/oracle/product/11gr2/db_1/dbs
替换备库的控制文件
(1)在备库上删掉数据库所有的控制文件
[oracle@HE4~]$ rm -rf /u01/app/oracle/oradata/orcl/control0*.ctl
(2)将主库备份的控制文件拷贝到备份机器上,并复制一个(要与pfile中的*.control_files参数一致)
[oracle@HE3dbs]$ scp -rp /tmp/standby.ctl HE4:/u01/app/oracle/oradata/orcl/control01.ctl
[oracle@HE4orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@HE4orcl]$ cp control01.ctl control02.ctl
修改备库pfile
[oracle@HE4orcl]$ cat /u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora
orcl.__db_cache_size=440401920
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
orcl.__pga_aggregate_target=213909504
orcl.__sga_target=633339904
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=176160768
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'
*.open_cursors=300
*.pga_aggregate_target=210763776
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=633339904
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=std
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,std)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/11gr2/db_1/dbs/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=std'
*.LOG_ARCHIVE_DEST_2='SERVICE=prime LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'
*.LOG_ARCHIVE_MAX_PROCESSES=5
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER=prime
*.FAL_CLIENT=stdby
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'
*.STANDBY_FILE_MANAGEMENT=AUTO
注:
#*.DB_FILE_NAME_CONVERT='/路径','路径' (前为切换后的主库路径,后为切换后的备库路径,这里主备库目录结构完全一样,则无需设定)
#*.LOG_FILE_NAME_CONVERT='/路径','/路径' (同上,这两个名字转换参数是主备库的路径映射关系,可能会是路径全名,看情况而定)
在备库上创建spfile
[oracle@HE4 dbs]$ sqlplus / as sysdba (如果报错看右边,有可能是备库没停干净或者没卸载干净导致)
创建备库spfile
SQL>startup pfile='/u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora'
SQL>create spfile from pfile;
配置standby redolog
(最佳性能模式可以忽略,如果要转为其它两种模式则要建立)
[oracle@HE3dbs]$ mkdir /u01/app/oracle/oradata/orcl/standby
[oracle@HE4dbs]$ mkdir /u01/app/oracle/oradata/orcl/standby
在主库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#, BYTESFROM V$LOG; (这里已经有三个了,所以从group4开始加)
在备库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#,BYTES FROM V$STANDBY_LOG;
在主库和备库中创建日志组和redo log文件
SQL>ALTER DATABASE ADD STANDBY LOGFILE group 4('/u01/app/oracle/oradata/orcl/standby/slog1.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILEgroup 5 ('/u01/app/oracle/oradata/orcl/standby/slog2.rdo') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6('/u01/app/oracle/oradata/orcl/standby/slog3.rdo') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE group 7('/u01/app/oracle/oradata/orcl/standby/slog4.rdo') SIZE 50M;
注:standby redolog的组数参考公式:(online redolog组数 + 1) * 数据库线程数;单机线程数为1,RAC一般为2。
standby redolog的组成员数和大小也尽量和online redolog一样。
在备库上执行备库恢复模式
SQL> selectinstance_name,status from v$instance;
INSTANCE_NAME STATUS
----------------------------
db1 OPEN
SQL> alter databaserecover managed standby database disconnect from session; 或者
ALTER DATABASERECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
将备库启动到open read only的状态
SQL> shutdownimmediate
SQL> startup mount
SQL> alter databaseopen read only;
如需结束
结束这个恢复进程:
SQL> alter databaserecover managed standby database cancel;
将备库启动到open read only的状态
SQL> shutdownimmediate
SQL> startup mount
SQL> alter databaseopen read only;
查看主库和备库的日志同步情况,确保已经同步
主库:
SQL> archive loglist
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/app/oracle/oradata/archive
最早的联机日志序列 14
下一个存档日志序列 16
当前日志序列 16
备库:
SQL> archive loglist
数据库日志模式 存档模式
自动存档 启用
存档终点 /u01/app/oracle/oradata/archive
最早的联机日志序列 14
下一个存档日志序列 0
当前日志序列 16
检验
检验方法:在主库中创建一张表,看在备库中是否可以查到。
1 在主库中创建一张表
SQL>
create tabletest_table(id number);
表已创建
SQL> alter databaserecover managed standby database using current logfile disconnect from session;
数据库已更改
因为是max performance模式,所以有归档才能看到,可以手动切归档。看备库上是否可以查到
SQL> desctest_table;
名称 是否为空? 类型
------------------------------------------------- ----------------------------
ID NUMBER
3 再次验证数据是否同步。
主库:
SQL> insert intotest_table values(1);
已创建 1 行。
SQL> commit;
提交完成。
备库:
SQL> select * fromtest_table;
ID
----------
1
这里数据实现了同步,说明dataguard配置成功了。
本文出自 “岁伏” 博客,请务必保留此出处http://suifu.blog.51cto.com/9167728/1732715