虚拟机ORACLE10G Data Guard(DG) Physical Standby Database

今天小测了一下10g的physical DG，参考首选官方文档
dgsource :192.168.1.100
dgtarget:192.168.1.245
虚拟机网络配置选hostonly,因为我没插网线，保证两台虚拟机互相ping通就可以
下面开始在 dgsource上操作

[oracle@oraserver ~]$ /sbin/ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 00:0C:29:41:A5:E0
inet addr:192.168.1.100  Bcast:192.168.1.255  Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe41:a5e0/64 Scope:Link
UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
RX packets:182 errors:0 dropped:0 overruns:0 frame:0
TX packets:148 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:15513 (15.1 KiB)  TX bytes:19848 (19.3 KiB)
[oracle@oraserver ~]$ ps -ef|grep ora_
oracle    3474     1  0 16:22 ?        00:00:00 ora_pmon_anbob
oracle    3476     1  0 16:22 ?        00:00:00 ora_psp0_anbob
oracle    3478     1  0 16:22 ?        00:00:00 ora_mman_anbob
oracle    3480     1  0 16:22 ?        00:00:00 ora_dbw0_anbob
oracle    3482     1  0 16:22 ?        00:00:00 ora_lgwr_anbob
oracle    3484     1  0 16:22 ?        00:00:00 ora_ckpt_anbob
oracle    3486     1  0 16:22 ?        00:00:02 ora_smon_anbob
oracle    3488     1  0 16:22 ?        00:00:00 ora_reco_anbob
oracle    3490     1  0 16:22 ?        00:00:01 ora_cjq0_anbob
oracle    3492     1  0 16:22 ?        00:00:02 ora_mmon_anbob
oracle    3494     1  0 16:22 ?        00:00:00 ora_mmnl_anbob
oracle    3496     1  0 16:22 ?        00:00:00 ora_d000_anbob
oracle    3498     1  0 16:22 ?        00:00:00 ora_s000_anbob
oracle    3516     1  0 16:22 ?        00:00:00 ora_qmnc_anbob
oracle    3536     1  0 16:22 ?        00:00:00 ora_q000_anbob
oracle    3538     1  0 16:22 ?        00:00:00 ora_q001_anbob
oracle    6176     1  0 16:59 ?        00:00:00 ora_j000_anbob
oracle    6208  5462  0 17:00 pts/0    00:00:00 grep ora_
[oracle@oraserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 20 17:05:52 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ANBOB> select dbid,name,database_role from v$database;
DBID NAME                        DATABASE_ROLE
---------- --------------------------- ------------------------------------------------
1133676144 ANBOB                       PRIMARY
sys@ANBOB> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
sys@ANBOB> alter database force logging;
Database altered.
sys@ANBOB> show parameter db_un
NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name                       string                            anbob
sys@ANBOB> alter system set db_unique_name='ora10g_p' scope=spfile;
System altered.
sys@ANBOB> alter system set log_archive_config='dg_config=(ora10g_p,ora10_s)';
System altered.
sys@ANBOB> alter system set log_archive_dest_1='location=/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=ora10g_p' scope=spfile;
System altered.
sys@ANBOB> alter system set log_archive_dest_2='service=ora10g_s async valid_for=(online_logfiles,primary_role) db_unique_name=ora10g_s' scope=spfile;
System altered.
sys@ANBOB> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE;
System altered.
sys@ANBOB> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE;
System altered.
sys@ANBOB> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
sys@ANBOB> alter database create standby controlfile as '/home/oracle/ora10g_s.ctl
  2  ';
Database altered.
sys@ANBOB> create pfile = '/home/oracle/initora10_s.ora' from spfile;
File created.
sys@ANBOB> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           2
sys@ANBOB> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ANBOB> startup mount
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
sys@ANBOB> alter database archivelog;
Database altered.
sys@ANBOB> SELECT LOG_MODE, FORCE_LOGGING FROM V$DATABASE;
LOG_MODE                             FORCE_LOG
------------------------------------ ---------
ARCHIVELOG                           YES

创建口令文件
orapwd file=orapwanbob password=oracle entries=5
配置tnsnames.ora
---------configuration tnsnames.ora append-----------
ora10g_p =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = anbob)
)
)
ora10g_s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = anbob)
)
)
----------------------end-------------
1.245上只装了软件，现在copy数据文件到dgtarget机器
--传口令文件
[oracle@oraserver dbs]$ scp orapwanbob 192.168.1.245:/oracle/product/10.2.0/db_1/dbs
The authenticity of host '192.168.1.245 (192.168.1.245)' can't be established.
RSA key fingerprint is 65:2b:8d:4b:9f:6a:dc:75:79:23:a7:c8:cf:c2:13:33.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.245' (RSA) to the list of known hosts.
oracle@192.168.1.245's password:
Permission denied, please try again.
oracle@192.168.1.245's password:
orapwanbob 100% 1536 1.5KB/s 00:00
--传数据文件
[oracle@oraserver oradata]$ scp -r anbob 192.168.1.245:/oracle/oradata
oracle@192.168.1.245's password:
sysaux01.dbf 100% 250MB 6.6MB/s 00:38
users01.dbf 100% 5128KB 5.0MB/s 00:01
redo02.log 100% 50MB 7.1MB/s 00:07
temp01.dbf 100% 20MB 10.0MB/s 00:02
control02.ctl 100% 6896KB 6.7MB/s 00:01
redo03.log 100% 50MB 6.3MB/s 00:08
--传控制文件
[oracle@oraserver ~]$ scp ora10g_s.ctl 192.168.1.245:/oracle/oradata/anbob
oracle@192.168.1.245's password:
ora10g_s.ctl 100% 6896KB 6.7MB/s 00:01
--传参数文件
[oracle@oraserver ~]$ scp initora10_s.ora 192.168.1.245:/oracle/product/10.2.0/db_1/dbs
oracle@192.168.1.245's password:
Permission denied, please try again.
oracle@192.168.1.245's password:
initora10_s.ora 100% 1379 1.4KB/s 00:00
好，到dgtarget 机器上操作

[oracle@aix dbs]$ env |grep ORACLE_
ORACLE_SID=anbob
ORACLE_BASE=/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/oracle/product/10.2.0/db_1
[oracle@aix dbs]$ cd /oracle/oradata/anbob
[oracle@aix anbob]$ ls
control01.ctl  control03.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
control02.ctl  ora10g_s.ctl   redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@aix anbob]$ mv ora10g_s.ctl control01.ctl 
[oracle@aix anbob]$ cp control01.ctl  control02.ctl
[oracle@aix anbob]$ cp control01.ctl  control03.ctl
[oracle@aix anbob]$ ls
control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@aix anbob]$ cd /oracle/product/10.2.0/db_1/dbs/
[oracle@aix dbs]$ ls
initora10_s.ora
[oracle@aix dbs]$ vi initora10_s.ora 
--修改 
*.db_unique_name='ora10g_s'
*.fal_client='ora10g_s'
*.fal_server='ora10g_p'
*.log_archive_dest_1='LOCATION=/oracle/flash_recovery_area/arch VALID_FOR=(all_logfi
les,all_roles) DB_UNIQUE_NAME=ora10g_s'
*.log_archive_dest_2='SERVICE=ora10g_p async valid_for=(online_logfiles,primary_role
) db_unique_name=ora10g_p'
"initora10_s.ora" 34L, 1399C written                 

同样配置 tnsnames.ora
[oracle@aix db_1]$ cd network/admin/
[oracle@aix admin]$ vi tnsnames.ora
--追加
ora10g_p =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = anbob)
)
)
ora10g_s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.245)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = anbob)
)
)
启动两台机器的监听
互相sqlplus sys/oracle@xxx as sysdba 确保连通
dgtarget 上启动归档，开始接收日志
sql>startup mount
sql>alter system archivelog;
--初始化log apply服务
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

############测试#########
主库
sys@ANBOB> create table testdg(id int,name varchar2(10));
Table created.
sys@ANBOB> insert into testdg values(1,100);
1 row created.
sys@ANBOB> commit;
Commit complete.
sys@ANBOB> alter system switch logfile;
System altered.
备库
[oracle@aix ~]$ tail -f /oracle/admin/anbob/bdump/alert_anbob.log
Media Recovery Log /oracle/arch/1_29_756820226.dbf
Media Recovery Waiting for thread 1 sequence 30
Mon Aug 22 14:49:20 2011
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/oracle/arch/1_30_756820226.dbf'
Expanded controlfile section 11 from 28 to 112 records
Requested to grow by 84 records; added 3 blocks of records
Mon Aug 22 14:49:23 2011
Media Recovery Log /oracle/arch/1_30_756820226.dbf
Media Recovery Waiting for thread 1 sequence 31
[oracle@aix ~]$ ora
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 22 14:56:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> select sequence#,first_time,next_time,applied from v$archived_log;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- --------------- --------------- ---------
2 08-AUG-11 20-AUG-11 YES
3 20-AUG-11 20-AUG-11 YES
4 20-AUG-11 20-AUG-11 YES
5 20-AUG-11 20-AUG-11 YES
6 20-AUG-11 20-AUG-11 YES
7 20-AUG-11 20-AUG-11 YES
8 20-AUG-11 20-AUG-11 YES
9 20-AUG-11 20-AUG-11 YES
10 20-AUG-11 20-AUG-11 YES
11 20-AUG-11 20-AUG-11 YES
12 20-AUG-11 20-AUG-11 YES
13 20-AUG-11 20-AUG-11 YES
14 20-AUG-11 22-AUG-11 YES
15 22-AUG-11 22-AUG-11 YES
16 22-AUG-11 22-AUG-11 YES
17 22-AUG-11 22-AUG-11 YES
18 22-AUG-11 22-AUG-11 YES
19 22-AUG-11 22-AUG-11 YES
20 22-AUG-11 22-AUG-11 YES
21 22-AUG-11 22-AUG-11 YES
22 22-AUG-11 22-AUG-11 YES
23 22-AUG-11 22-AUG-11 YES
24 22-AUG-11 22-AUG-11 YES
25 22-AUG-11 22-AUG-11 YES
26 22-AUG-11 22-AUG-11 YES
27 22-AUG-11 22-AUG-11 YES
28 22-AUG-11 22-AUG-11 YES
29 22-AUG-11 22-AUG-11 YES
30 22-AUG-11 22-AUG-11 YES
29 rows selected.
idle> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
idle> alter database open;
Database altered.
idle> select * from testdg;
ID NAME
---------- ------------------------------
1 100