datagurad related operation
备库的启动和关闭
启动：
startup nomount
alter database mount standby database
alter database recover managed standby database disconnect from session;
关闭
alter database recover managed standby database cancel
shutdown immediate;
创建表，boson
SQL> create table boson as select * from user_objects;
Table created.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/flash_recovery_area/ORCL/archivelog
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
可以查看备库的alter.log发现日志46已经接收并传送，当然也可以查看v$archived_log视图：
Fri Dec 7 02:37:12 2007
Media Recovery Log /u01/app/flash_recovery_area/ORCL/archivelog/1_45_640523332.dbf
Media Recovery Waiting for thread 1 sequence 46 (in transit)
Fri Dec 7 03:30:12 2007
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: ‘/u01/app/oradata/orcl/redo05.log’
Fri Dec 7 03:30:15 2007
Media Recovery Log /u01/app/flash_recovery_area/ORCL/archivelog/1_46_640523332.dbf
Media Recovery Waiting for thread 1 sequence 47 (in transit)
select first_time,applied,sequence#,status,next_time from v$archived_log;
FIRST_TIM APP SEQUENCE# STATUS NEXT_TIME
——— — ———- ———- ———
06-DEC-07 YES 37 A 06-DEC-07
06-DEC-07 YES 38 A 06-DEC-07
06-DEC-07 YES 39 A 06-DEC-07
06-DEC-07 YES 40 A 07-DEC-07
07-DEC-07 YES 41 A 07-DEC-07
07-DEC-07 YES 42 A 07-DEC-07
07-DEC-07 YES 43 A 07-DEC-07
07-DEC-07 YES 44 A 07-DEC-07
07-DEC-07 YES 45 A 07-DEC-07
07-DEC-07 YES 46 A 07-DEC-07
10 rows selected.
46号日志已经应用。
可以在备库查询BOSON表，首先关闭当前恢复模式，然后只读打开数据库
alter database recover managed standby database cancel
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from boson where rownum<10;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
——————————————————————————————————————————– —————————— ———- ————– ——————- ——— ——— ——————- ———- - - -
ICOL$ 20 2 TABLE 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
I_USER1 44 44 INDEX 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
CON$ 28 28 TABLE 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
UNDO$ 15 15 TABLE 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
C_COBJ# 29 29 CLUSTER 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
I_OBJ# 3 3 INDEX 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
PROXY_ROLE_DATA$ 25 25 TABLE 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
I_IND1 39 39 INDEX 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
I_CDEF2 51 51 INDEX 30-JUN-05 30-JUN-05 2005-06-30:19:10:16 VALID N N N
9 rows selected.
从只读模式 转换到恢复模式
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
2，创建表空间
SQL> create tablespace boson datafile’/u01/app/oradata/orcl/boson.dbf’size 50m
2 extent management local
3 segment space management auto;
Tablespace created.
SQL> alter system switch logfile;
System altered.
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/app/oradata/orcl/boson.dbf
Successfully added datafile 5 to media recovery
Datafile #5: ‘/u01/app/oradata/orcl/boson.dbf’
Media Recovery Waiting for thread 1 sequence 48 (in transit)
3角色切换
switchover
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
SESSIONS ACTIVE
SQL> select sid,process,program from v$session where type=’USER’ and
sid<>(select distinct sid from v$mystat); 2
SID PROCESS PROGRAM
———- ———— ————————————————
139 15767 oracle@primary (J000)
SQL> alter database commit to switchover to physical standby with session shutdown;
可以看日志：
SMON: disabling tx recovery
Fri Dec 7 05:21:40 2007
Stopping background process QMNC
Fri Dec 7 05:21:40 2007
Stopping background process CJQ0
Fri Dec 7 05:21:42 2007
Stopping Job queue slave processes
Fri Dec 7 05:21:50 2007
Waiting for Job queue slaves to complete
Fri Dec 7 05:22:35 2007
Job queue slave processes stopped
Waiting for dispatcher ‘D000′ to shutdown
All dispatchers and shared servers shutdown
Fri Dec 7 05:22:37 2007
SMON: disabling cache recovery
Fri Dec 7 05:22:38 2007
Shutting down archive processes
Archiving is disabled
Fri Dec 7 05:22:43 2007
ARCH shutting down
ARC1: Archival stopped
Fri Dec 7 05:22:48 2007
ARCH shutting down
ARC0: Archival stopped
Fri Dec 7 05:22:49 2007
Thread 1 closed at log sequence 49
Successful close of redo thread 1
Fri Dec 7 05:22:49 2007
ARCH: Noswitch archival of thread 1, sequence 49
ARCH: End-Of-Redo Branch archival of thread 1 sequence 49
ARCH: Archiving is disabled due to current logfile archival
Clearing standby activation ID 1168281793 (0×45a290c1)
The primary database controlfile was created using the
‘MAXLOGFILES 16′ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ’srl1.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ’srl2.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ’srl3.f’ SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE ’srl4.f’ SIZE 52428800;
Archivelog for thread 1 sequence 49 required for standby recovery
MRP0 started with pid=13, OS id=15772
Fri Dec 7 05:22:50 2007
MRP0: Background Managed Standby Recovery process started (orcl)
Managed Standby Recovery not using Real Time Apply
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /u01/app/flash_recovery_area/ORCL/archivelog/1_49_640523332.dbf
Identified End-Of-Redo for thread 1 sequence 49
Fri Dec 7 05:22:56 2007
Media Recovery End-Of-Redo indicator encountered
Fri Dec 7 05:22:56 2007
Media Recovery Applied until change 599769
Fri Dec 7 05:22:56 2007
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
Resetting standby activation ID 1168281793 (0×45a290c1)
Fri Dec 7 05:22:57 2007
Waiting for MRP0 pid 15772 to terminate
Fri Dec 7 05:22:57 2007
MRP0: Background Media Recovery process shutdown (orcl)
Fri Dec 7 05:22:58 2007
idle dispatcher ‘D000′ terminated, pid = (13, 1)
Fri Dec 7 05:22:58 2007
Switchover: Complete - Database shutdown required (orcl)
Fri Dec 7 05:22:58 2007
Completed: alter database commit to switchover to physical standby with session shutdown
然后在备库执行切换成主库：
SQL> alter database commit to switchover to primary;
Database altered.
可以看日志执行过程：
Media Recovery End-Of-Redo indicator encountered
Fri Dec 7 04:59:48 2007
Media Recovery Applied until change 599769
Fri Dec 7 04:59:48 2007
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
Resetting standby activation ID 1168281793 (0×45a290c1)
Fri Dec 7 04:59:48 2007
MRP0: Background Media Recovery process shutdown (orcl)
Fri Dec 7 05:01:35 2007
alter database commit to switchover to primary
Fri Dec 7 05:01:35 2007
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Fri Dec 7 05:01:35 2007
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 599769
Online log /u01/app/oradata/orcl/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oradata/orcl/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oradata/orcl/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 599767
Fri Dec 7 05:01:35 2007
Switchover: Complete - Database shutdown required (orcl)
Completed: alter database commit to switchover to primary
Fri Dec 7 05:01:50 2007
ARC0: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Fri Dec 7 05:01:55 2007
ARCH shutting down
ARC1: Archival stopped
Fri Dec 7 05:02:05 2007
ARCH shutting down
ARC0: Archival stopped
切换完后，要在原来的主库添加REDO文件，
物理备库转换为逻辑的备库：这一段信息来自NINGOO
10.在主库redo中生成数据字典信息
logical standby使用了logminer技术，需要生成数据字典信息。
SQL> exec dbms_logstdby.build
PL/SQL 过程已成功完成。
执行该过程需要等到所有活动事务结束。所以，如果有条件的话，最好重启主库到restrict模式后再执行
。另外，还要利用flashback查询来获得一致性数据，所有请将undo_retention设置得足够大。
11.为备库生成spfile
SQL> create spfile from pfile;
文件已创建。
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 142606336 bytes
Fixed Size 1247732 bytes
Variable Size 83887628 bytes
Database Buffers 50331648 bytes
Redo Buffers 7139328 bytes
SQL> alter database mount standby database;
数据库已更改。
SQL> alter database recover to logical standby lstandby;
数据库已更改。
该操作同时会将参数db_name设置为test
13.打开logical standby
SQL> alter database open resetlogs;
数据库已更改。
14.启用日志应用
SQL> alter database start logical standby apply;
数据库已更改。
在alert日志中可以发现相关的恢复进程启用成功的信息。
15.验证
在主库上
SQL> insert into ning.test values(2,’a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
等待一段时间后，从逻辑备库上查询，得到结果
SQL> select * from ning.test;
ID NAME
———- ————————————————————
2 a
说明逻辑备库已经运行正常。至此，逻辑备库创建成功。
16.Logical standby相关视图
v$logstdby
v$logstdby_process
v$logstdby_state
v$logstdby_stats
v$logstdby_transaction
dba_logstdby_events
dba_logstdby_history
dba_logstdby_log
dba_logstdby_not_unique
dba_logstdby_parameters
17.启用实时日志应用(real-time apply)
oracle10g加入了实时应用的新特性，需要使用lgwr来传送日志才能使用。
a.首先在备库添加standby redo logfile
SQL> alter database add standby logfile group 4 (’e:/oracle/ora10/oradata/test/standby
04.redo’) size 10m;
Database altered.
SQL> alter database add standby logfile group 5 (’e:/oracle/ora10/oradata/test/standby
05.redo’) size 10m;
Database altered.
SQL> alter database add standby logfile group 6 (’e:/oracle/ora10/oradata/test/standby
06.redo’) size 10m;
Database altered.
SQL> alter database add standby logfile group 7 (’e:/oracle/ora10/oradata/test/standby
07.redo’) size 10m;
Database altered.
b.在主库将日志传送方式改为lgwr
alter system set log_archive_dest_2=’service=test lgwr async db_unique_name=test’
c.停止日志应用
SQL> alter database stop logical standby apply;
数据库已更改。
d.启用real-time apply
SQL> alter database start logical standby apply immediate;
数据库已更改。
e.在主库插入记录并commit
SQL> insert into ning.test values(3,’b');
已创建 1 行。
SQL> commit;
提交完成。
f.马上在备库就可以查到对应的数据了
SQL> select * from ning.test;
ID NAME
———- ————————————————————
2 a
