09年初写的
辣椒找翻出来的
物理dg
==============================
[oracle@localhost dbs]$ uname -a
Linux localhost.localdomain 2.6.9-67.0.0.0.1.ELsmp #1 SMP Sun Nov 18 00:23:42 EST 2007 i686 i686 i386 GNU/Linux
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
=============================
主库
SID：A
DB_NAME:A
DB_UNIQUE_NAME:A
从库
SID：B
DB_NAME:A
DB_UNIQUE_NAME:B
<解释>:我是在单机上做的实验
在单机上可以创建同名的数据库
但为了区分实例名是不同的
-----------------------------
配置网络
配置lisner
可以使用的工具有
netca
netmgr
配置后如下
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = oracl)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = A)
)
(SID_DESC =
(GLOBAL_DBNAME = oracle)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = B)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
配置tnsnames.ora
可以在netmgr下用图形来配置，也可以使用命令行来配置
配置后内容如下
B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = B)
)
)
A =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = A)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
<解释>:网络配置后可以通过tnsping来测试是否连通
[oracle@localhost admin]$ tnsping A
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-DEC-2008 14:12:46
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
= A)))
OK (10 msec)
[oracle@localhost admin]$ tnsping B
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-DEC-2008 14:12:48
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = B)
))
OK (10 msec)
[oracle@localhost admin]$
----------------------------------------
为了方便配置，先使用pfile修改，再生成spfile
主节点参数文件如下
[oracle@localhost dbs]$ more initA.ora
A.__db_cache_size=100663296
A.__java_pool_size=4194304
A.__large_pool_size=4194304
A.__shared_pool_size=54525952
A.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/A/adump'
*.background_dump_dest='/opt/oracle/admin/A/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/A/control01.ctl'
*.core_dump_dest='/opt/oracle/admin/A/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='A'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/A/udump'
--以下为dg添加
*.db_unique_name=A
*.log_archive_config='dg_config=(A,B)'
*.log_archive_dest_1='location=/opt/oracle/archive/A/online valid_for=(online_logfiles,primary_role) db_unique_name=A'
*.log_archive_dest_2='service=B valid_for=(online_logfiles,primary_role) db_unique_name=B'
*.log_archive_dest_state_2=defer
*.log_archive_dest_3='location=/opt/oracle/archive/A/standby valid_for=(standby_logfiles,standby_role) db_unique_name=A'
*.db_file_name_convert=('B','A')
*.log_file_name_convert=('B','A')
*.standby_file_management=auto
*.archive_lag_target=1800
修改从节点参数文件
与主节点相互参照，修改内容如下
*.db_unique_name=B
*.log_archive_config='dg_config=(A,B)'
*.log_archive_dest_1='location=/opt/oracle/archive/B/online valid_for=(online_logfiles,primary_role) db_unique_name=B'
*.log_archive_dest_2='service=A valid_for=(online_logfiles,primary_role) db_unique_name=A'
*.log_archive_dest_state_2=defer
*.log_archive_dest_3='location=/opt/oracle/archive/B/standby valid_for=(standby_logfiles,standby_role) db_unique_name=B'
*.db_file_name_convert=('A','B')
*.log_file_name_convert=('A','B')
*.standby_file_management=auto
*.archive_lag_target=1800
另外还要修改
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BXDB)'
------------------------------------------
参数解释：
db_unique_name
为区分两个节点，定义数据库唯一名
最好与实例名相同容易辨认
主从数据库唯一名不能相同
log_archive_config
给出主从连接标识符就可以
log_archive_dest_1
dg最重要的参数之一
指定归档的第一个目录，一般都是本地的
valid_for指定生效的条件
本例中为使用online_logfiles作为同步日志的对象
也就是使用lgwr来传送日志
后面的primary_role是这个地址生效的条件
当当前为主节点的时候生效
最后还要给出
db_unique_name来确认
db_file_name_convert
当主从数据文件的目录不同的时候
为了方便管理，可以让oracle自动转换目录
前面给的是从节点的目录，后面的是主节点的
log_file_name_convert
同上，不过转换的是日志文件
standby_file_management
设置为auto后，主节点田加数据文件后从节点自动添加
archive_lag_target
日志应用延迟时间
可以不使用
------------------------------------
所有的网络条件以及参数都准备好后
开始复制主库
复制主库可以使用各种方法
如：
直接copy数据库文件
rman 异地恢复
duplicate数据库
dbca带有数据文件的模版
等方法都可以
<注意>：从库也需要口令文件
可以使用orapw命令创建
也可以copy主节点的口令文件
然后重命名为从节点默认口令文件的形式
[oracle@localhost dbs]$ pwd
/opt/oracle/product/10.2.0/dbs
[oracle@localhost dbs]$ ls -ltr
total 80
-rw-rw---- 1 oracle oinstall 1544 Dec 31 13:13 hc_A.dat
-rw-rw---- 1 oracle oinstall 24 Dec 31 13:14 lkA
-rw-r----- 1 oracle oinstall 1536 Dec 31 13:15 orapwA
-rw-r--r-- 1 oracle oinstall 1291 Dec 31 13:18 initA.ora
-rw-rw---- 1 oracle oinstall 1544 Dec 31 13:30 hc_B.dat
-rw-r--r-- 1 oracle oinstall 1428 Dec 31 13:31 initB.ora.b
-rw-r--r-- 1 oracle oinstall 1291 Dec 31 13:32 initB.ora
-rw-rw---- 1 oracle oinstall 24 Dec 31 13:37 lkB
-rw-r----- 1 oracle oinstall 3584 Jan 5 08:37 spfileA.ora
-rw-r----- 1 oracle oinstall 3584 Jan 5 08:42 spfileB.ora
[oracle@localhost dbs]$ cp orapwA orapwB
[oracle@localhost dbs]$ ls -ltr
total 88
-rw-rw---- 1 oracle oinstall 1544 Dec 31 13:13 hc_A.dat
-rw-rw---- 1 oracle oinstall 24 Dec 31 13:14 lkA
-rw-r----- 1 oracle oinstall 1536 Dec 31 13:15 orapwA
-rw-r--r-- 1 oracle oinstall 1291 Dec 31 13:18 initA.ora
-rw-rw---- 1 oracle oinstall 1544 Dec 31 13:30 hc_B.dat
-rw-r--r-- 1 oracle oinstall 1428 Dec 31 13:31 initB.ora.b
-rw-r--r-- 1 oracle oinstall 1291 Dec 31 13:32 initB.ora
-rw-rw---- 1 oracle oinstall 24 Dec 31 13:37 lkB
-rw-r----- 1 oracle oinstall 3584 Jan 5 08:37 spfileA.ora
-rw-r----- 1 oracle oinstall 3584 Jan 5 08:42 spfileB.ora
-rw-r----- 1 oracle oinstall 1536 Jan 5 09:32 orapwB
-----------------------
复制数据库我采用的是直接copy数据文件，在线日志文件的形式
-----------------------
启动主从库到nomount阶段
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
使用pfile生成spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
alter system set log_archive_dest_state_2=enable;
主从都是如此，再重新引导启动数据库
------------------------------
主从都启动到nomount阶段
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /opt/oracle/product/10.2.0/dbs
/spfileA.ora
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /opt/oracle/product/10.2.0/dbs
/spfileB.ora
使用的都是spfile
主库
SQL> alter database mount;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
设置了主库归档以及强制记录日志
--------------------------------
创建从库控制文件
SQL>alter database create standby controlfile as '/opt/oracle/oradata/B/control01.ctl';
Database altered.
<补充>为了实验方便，主从都使用了1个控制文件，生产环境这样很危险
---------------------------------
mount从库
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
打开主库
SQL> alter database open;
Database altered.
---------------------------
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13177610/viewspace-676334/ ，如需转载，请注明出处，否则将追究法律责任。
