Preparation:
Confirm that the object and statement can be supported by standby
Ensure that the rows of each table in the primary database can be uniquely identified.
Environment:
Operating System: red hat linux enterprise 5
ORACLE: 11.2.0.1.0
PRIMARY:
IP: 192.168.1.11
SID: test
DB_UNIQUE_NAME: test
Installation path:/oracle/product/11.2.0/dbhome_1
Local archive path:/oracle/oradata/test/archive
Physics standby:
IP: 192.168.1.12
SID: dgtest
DB_UNIQUE_NAME: dgtest
Installation path:/oracle/product/11.2.0/dbhome_1
Local archiving path:/oracle/oradata/dgtest/archive
Logical standby:
IP: 192.168.1.15
SID: logicdg
DB_UNIQUE_NAME: logicdg
Installation path:/oracle/product/11.2.0/dbhome_1
Local archive path:/oracle/oradata/logicdg/local-archive
In this example, the physical standby to logical standby method is used to add a new logical standby in an existing replicasuard environment.
For more information about the original dataguard environment, see:
Http://xin23.blog.51cto.com/1827266/504066
You can also refer to this article to create a new physical standby. I will not go into details here.
The current environment is:
Primary: 192.168.1.11
Physics: 192.168.1.12
Logical: 192.168.1.15 (currently physical standby. To be converted)
1. Modify the primary initialization parameter file (only the modified part is listed)
*. Log_archive_dest_state_2 = defer
*. Log_archive_dest_state_3 = defer
*. Log_archive_config = 'dg _ config = (test, dgtest, logicdg )'
*. Log_archive_dest_2 = 'service = test12 arch valid_for = (online_logfiles, primary_role) db_unique_name = dgtest'
*. Log_archive_dest_3 = 'service = test15 arch valid_for = (online_logfiles, primary_role) db_unique_name = logicdg'
*. Fal_server = test11
*. Fal_client = test12
*. Standby_file_management = auto
*. Db_file_name_convert = '/oracle/oradata/test','/oracle/oradata/dgtest/dgtest', '/oracle/oradata/test ','/
Oracle/oradata/logicdg'
*. Log_file_name_convert = '/oracle/oradata/test','/oracle/oradata/dgtest/dgtest', '/oracle/oradata/test ','/
Oracle/oradata/logicdg'
2. view the synchronization status of the two physical standby instances.
Physics> select sequence #, applied from v $ archived_log;
SEQUENCE # APPLIED
-------------------
66 YES
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
7 rows selected.
-----------------------------------------------------------------------------
Logical> select sequence #, applied from v $ archived_log;
SEQUENCE # APPLIED
-------------------
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
6 rows selected.
3. Cancel the redo application for physical standby Conversion
Logical> alter database recover managed standby database cancel;
Database altered.
4. Generate a data dictionary with primary
Primary> execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
5. Convert physical standby to logical standby
Logical> alter database recover to logical standby logicdg;
Database altered.
Logical> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
Logical> startup mount
ORACLE instance started.
Total System Global Area 2488635392 bytes
Fixed Size 2215904 bytes
Variable Size 1392508960 bytes
Database Buffers 1090519040 bytes
Redo Buffers 3391488 bytes
Database mounted.
6. Adjust the standby initialization parameters
Logical> alter system set log_archive_dest_1 = 'location =/oracle/oradata/logicdg/local-archive valid_for = (online_logfiles, all_roles)
2 db_unique_name = logicdg ';
System altered.
Logical> alter system set log_archive_dest_5 = 'location =/oracle/oradata/logicdg/archive valid_for = (standby_logfiles, standby_role)
2 db_unique_name = logicdg ';
System altered.
7. Open the database using resetlogs
Logical> alter database open resetlogs;
Database altered.
8. Apply redo
Create standby redologs
Logical> alter database add standby logfile group 11'/oracle/oradata/logicdg/standbyredo11.log 'size 100 m;
Database altered.
Logical> alter database add standby logfile group 12'/oracle/oradata/logicdg/standbyredo12.log 'size 100 m;
Database altered.
Logical> alter database add standby logfile group 13'/oracle/oradata/logicdg/standbyredo13.log 'size 100 m;
Database altered.
Start the redo real-time application
Logical> alter database start logical standby apply immediate;
Database altered.