1.Primary数据库生成LogMiner字典信息（生成之前，确保待转换的物理Standby停止REDO应用）
ORCLPRI_LG >execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
2.逻辑Standby更名
ORCLSTD_LG >show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
ORCLSTD_LG >alter database recover to logical standby ORCLLDG;
alter database recover to logical standby ORCLLDG
*
ERROR at line 1:
ORA-19953: database should not be open
ORCLSTD_LG >shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
ORCLSTD_LG >alter database recover to logical standby ORCLLDG;
alter database recover to logical standby ORCLLDG
*
ERROR at line 1:
ORA-01034: ORACLE not available
ORCLSTD_LG >startup mount
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
Database mounted.
ORCLSTD_LG >alter database recover to logical standby ORCLLDG;
Database altered.
ORCLSTD_LG >show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
ORCLSTD_LG >shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
ORCLSTD_LG >startup mount
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
Database mounted.
3.重启生效
ORCLSTD_LG >show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCLLDG
ORCLSTD_LG >select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
4.创建本地和远程归档路径
修改Primary spfile：
orcl.__db_cache_size=92274688 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=58720256 orcl.__streams_pool_size=4194304 *.audit_file_dest=‘/u01/app/oracle/admin/orcl/adump‘ *.background_dump_dest=‘/u01/app/oracle/admin/orcl/bdump‘ *.compatible=‘10.2.0.1.0‘ *.control_files=‘/u01/app/oracle/oradata/orcl/control01.ctl‘,‘/u01/app/oracle/oradata/orcl/control02.ctl‘,‘/u01/app/oracle/oradata/orcl/control03.ctl‘#Restore Controlfile *.core_dump_dest=‘/u01/app/oracle/admin/orcl/cdump‘ *.db_block_size=8192 *.db_domain=‘‘ *.db_file_multiblock_read_count=16 *.db_file_name_convert=‘/u01/app/oracle/oradata/orcl‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/rec_catalog‘,‘/u01/app/oracle/oradata/orclstd‘ *.db_name=‘orcl‘ *.db_recovery_file_dest=‘/u01/app/oracle/flash_recovery_area‘ *.db_recovery_file_dest_size=2147483648 *.db_unique_name=‘orclpre‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)‘ *.fal_client=‘orcl_192.168.1.222‘ *.fal_server=‘orcls_192.168.1.223‘ *.job_queue_processes=10 *.log_archive_config=‘dg_config=(orclpre,orclldg)‘ *.log_archive_dest_1=‘location=/u01/arch_orcl‘ *.log_archive_dest_2=‘service=orcls_192.168.1.223 lgwr async valid_for=(online_logfiles, primary_role) db_unique_name=orclldg‘ *.log_archive_dest_state_2=‘ENABLE‘ *.log_archive_format=‘archive_%t_%s_%r.arclog‘ *.log_file_name_convert=‘/u01/app/oracle/oradata/orcl‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/rec_catalog‘,‘/u01/app/oracle/oradata/orclstd‘ *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sga_target=167772160 *.standby_file_management=‘auto‘ *.undo_management=‘AUTO‘ *.undo_tablespace=‘UNDOTBS1‘ *.user_dump_dest=‘/u01/app/oracle/admin/orcl/udump‘
修改Standby spfile：
orcl.__db_cache_size=96468992 orcls.__db_cache_size=100663296 orclstd.__db_cache_size=50331648 orcl.__java_pool_size=4194304 orcls.__java_pool_size=4194304 orclstd.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcls.__large_pool_size=4194304 orclstd.__large_pool_size=4194304 orcl.__shared_pool_size=54525952 orcls.__shared_pool_size=54525952 orclstd.__shared_pool_size=104857600 orcl.__streams_pool_size=4194304 orcls.__streams_pool_size=0 orclstd.__streams_pool_size=0 *.audit_file_dest=‘/u01/app/oracle/admin/orclstd/adump‘ *.background_dump_dest=‘/u01/app/oracle/admin/orclstd/bdump‘ *.compatible=‘10.2.0.1.0‘ *.control_files=‘/u01/app/oracle/oradata/orclstd/orclstd01.ctl‘,‘/u01/app/oracle/oradata/orclstd/orclstd02.ctl‘,‘/u01/app/oracle/oradata/orclstd/orclstd03.ctl‘ *.core_dump_dest=‘/u01/app/oracle/admin/orclstd/cdump‘ *.db_block_size=8192 *.db_domain=‘‘ *.db_file_multiblock_read_count=16 *.db_file_name_convert=‘/u01/app/oracle/oradata/orcl‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orcl‘,‘/u01/rec_catalog‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/rec_catalog‘ *.db_name=‘ORCLLDG‘#db_name *.db_recovery_file_dest=‘/u01/app/oracle/flash_recovery_area‘ *.db_recovery_file_dest_size=2147483648 *.db_unique_name=‘orclldg‘ *.dispatchers=‘(PROTOCOL=TCP) (SERVICE=orclXDB)‘ *.fal_client=‘orcl_192.168.1.223‘ *.fal_server=‘orcls_192.168.1.222‘ *.job_queue_processes=10 *.log_archive_config=‘dg_config=(orclpre,orclldg)‘ *.log_archive_dest_1=‘location=/u01/arch_ldg valid_for=(online_logfiles,all_roles) db_unique_name=orclldg‘ *.log_archive_dest_2=‘service=orcl_192.168.1.222 arch valid_for=(online_logfiles, primary_role) db_unique_name=orclpre‘ *.log_archive_dest_3=‘location=/u01/arch_std valid_for=(standby_logfiles,standby_role) db_unique_name=orclldg‘ *.log_archive_dest_state_2=‘enable‘ *.log_archive_format=‘archive_%t_%s_%r.arclog‘ *.log_file_name_convert=‘/u01/app/oracle/oradata/orcl‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orcl‘,‘/u01/rec_catalog‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/app/oracle/oradata/orclstd‘,‘/u01/rec_catalog‘ *.open_cursors=300 *.pga_aggregate_target=16777216 *.processes=150 *.remote_login_passwordfile=‘EXCLUSIVE‘ *.sga_target=167772160 *.standby_file_management=‘auto‘ *.undo_management=‘AUTO‘ *.undo_tablespace=‘UNDOTBS1‘ *.user_dump_dest=‘/u01/app/oracle/admin/orclstd/udump‘
ORCLSTD_LG >create spfile from pfile=‘/u01/pfile‘;
File created.
ORCLSTD_LG > startup mount
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
Database mounted.
ORCLSTD_LG > alterd database open;
SP2-0734: unknown command beginning "alterd dat..." - rest of line ignored.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORCLSTD_LG > alter database open resetlogs;
Database altered.
开始应用REDO数据
ORCLSTD_LG >alter database start logical standby apply;
Database altered.
ORCLSTD_LG >alter database stop logical standby apply;
Database altered.
ORCLSTD_LG >select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orclstd/redo03.log
NO
2 ONLINE
/u01/app/oracle/oradata/orclstd/redo02.log
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
1 ONLINE
/u01/app/oracle/oradata/orclstd/redo01.log
NO
ORCLSTD_LG >alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/orclstd/stdredo01.log‘ size 50m;
Database altered.
ORCLSTD_LG >alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/orclstd/stdredo02.log‘ size 50m;
Database altered.
ORCLSTD_LG >alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/orclstd/stdredo03.log‘ size 50m;
Database altered.
重新执行：
ORCLSTD_LG >alter database start logical standby apply immediate;
Database altered.
测试：
Primary：
SQL> select * from scott.test;
no rows selected
SQL> insert into scott.test values(1);
1 row created.
SQL> insert into scott.test values(2);
1 row created.
SQL> insert into scott.test values(3);
1 row created.
SQL> commit;
Commit complete.
Standby：
SQL> select * from scott.test;
ID
----------
1
2
3
逻辑Standby创建（转换）成功……
逻辑Data Guard的配置,布布扣,bubuko.com
原文：http://blog.csdn.net/u013827322/article/details/22161949