Oracle database Multi-instance real-time synchronization (implemented with Oracle management platform)

2. Environment:

   Windows 2008 server,Oracle 11g R2 database

4. Task Requirements:

   There are three DB instances, one instance for use by the management subsystem, one for use by the subsystem, and one for backup.

6. Selected technology:

   Oracle Stream Replication (stream replication)

8. Create an additional two instances

   SOURCE instance: ORCL

   Target 1:target

   Target 2:target2

10. Stop the application server and prevent users from making changes to the database by using the application server.

    and export the user's data in the ORCL instance, with the exp command

    For example exp nt_gxt/[email protected] File=e:\nt_gxt_201508140923.dmp

12. Environment settings

    To log on to a database using the command line

    
    

13. Sqlpus Sys/[email protected] as Sysdbaalter system set Global_names=true scope=both;alter system set job_queue_processes= Ten Scope=both;alter system set PARALLEL_MAX_SERVERS=20 scope=both;alter system set undo_retention=3600 Scope=both;alter System set nls_date_format= ' Yyyy-mm-dd HH24:MI:SS ' scope=spfile;alter system set streams_pool_size=512m scope=spfile; Alter system set utl_file_dir= ' * ' scope=spfile;alter system set open_links=4 scope=spfile;

    --The following command to set the archive mode: (Note: Modify the path)

    Alter system set log_archive_dest_1= ' Location=e:\devprogramsfile\oracle\archive\target ' scope=spfile;alter system Set Log_archive_start=true scope=spfile;alter system set log_archive_format= ' Arch%t_%s_%r.arc ' Scope=spfile;shutdown Immediate;startup Mount;

( if the startup Mount is an error, you need to register the instance statically and add the following to the listener.ora file. and restart the Monitoring service )

ALTER DATABASE Archivelog;alter database open;--If you want to see if the archive mode is set successfully you can use the command: Archive Log List--if the following is true, the setting is successful.

8. Create a Stream Admin user

1). Create a master Environment administrative user

Sqlplus Sys/[email protected] as sysdba# create a stream dedicated tablespace for the main environment (note Modify path) create tablespace tbs_stream datafile ' e:\ devprogramsfile\oracle\oradata\orcl\tbs_stream01.dbf ' size 100m autoextend on MaxSize Unlimited segment space Management auto; #将logminer的数据字典从system表空间转移到新建的表空间 to prevent full system table space execute Dbms_logmnr_d.set_tablespace (' Tbs_stream '); #创建Stream管理用户 create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp; #授权Stream管理用户grant Connect,resource,dba,aq_administrator_role to Strmadmin;begindbms_streams_auth.grant_admin_ Privilege (grantee = ' strmadmin ', grant_privileges = true); end;/

2). Create a stream from the environment to manage users

Sqlplus Sys/[email protected] as sysdba# create stream dedicated table space create tablespace tbs_stream datafile ' E:\DevProgramsFile\Oracle\ oradata\target\tbs_stream01.dbf ' size 100m autoextend on MaxSize Unlimited segment space management auto; #同样, The Logminer data dictionary is transferred from the system table space to the newly created tablespace, preventing the system tablespace from being filled with execute dbms_logmnr_d.set_tablespace (' Tbs_stream '); #创建Stream管理用户 Create user strmadmin identified by strmadmin default tablespace tbs_stream temporary tablespace temp; #授权Stream管理用户 Grant Connect,resource,dba,aq_administrator_role to Strmadmin;begindbms_streams_auth.grant_admin_privilege (grantee = > ' strmadmin ', grant_privileges = true); end;/

3). Create DBlink

3.1) Create a primary database data chain

According to the official Oracle 10GR2 stream document, the name of the database chain established for the primary database must be the same as the global_name from the database

Same. If you need to modify Global_name, execute "ALTER DATABASE rename global_name to xxx".

#以strmadmin身份, log in to the master database. Sqlplus strmadmin/[email protected]create Database link target connect to strmadmin identified by Strmadmin using ' target ' Create DATABASE link Target2 connect to strmadmin identified by Strmadmin using ' Target2 ';

3.2) Create a data chain from the database

#以strmadmin身份, log on from the database. Sqlplus strmadmin/[email protected]create Database link ORCL connect to strmadmin identified by Strmadmin using ' ORCL ';

4). Start the archive log

ALTER DATABASE add supplemental log data;

8. Using Oracle's own manager to create a stream replication-related process

1). Start the dbconsole of the ORCL instance

2). Click Database CONTROL–ORCL to start em

3). Log in with Strmadmin user in open browser window

4). Click Data Movement

5). Select ' Flow ' – ' Settings '

6). Select the replication scheme, the following host identity certificate enter the user name and password of the computer administrator, and tick save as preferred proof of identity.

then click Continue.

7). Select the library system user in the inclusion scheme (my is NT_GXT)

8). Configure

9). Set Start now

10). If the last step commits the times the following error

Executes the statement (Execute mgmt_user. Make_em_user (' strmadmin ');), executed with the system user login to the ORCL instance.

The same method is used to create a process for Target2. Implement multi-instance real-time synchronization.

Attention:

11g Database Empty table export problem.

SQL Code

View

Show parameter deferred_segment_creation;

Modify

alter system set DEFERRED_SEGMENT_CREATION=FALSE;

After three instances are complete, the service starts,

The last Oracle schematic

Oracle database Multi-instance real-time synchronization (implemented with Oracle management platform)