Oracle one-time production sub-Library, upgrade, migration

Today, we completed a library operation with a high-load central database and implemented a rolling upgrade of Oracle (10.2.0.1->10.2.0.4) with a business interruption of just 15 minutes. Platform: RHEL as 4 + Oracle 10.2.0.1 Sub-Library Purpose:

1) Move out of Business/business_app two business system User data, reduce the business pressure and load of the central bank, adjust the database framework to deal with the impact of the new business system on-line to the Central Library;

2) New library upgrade Oracle10.2.0.1 to 10.2.0.4, the database itself fixes a lot of bugs, enhance the stability of the database.

3) Adjust the timing task and change the original scheduled task from Crontab/job to Oracle Scheduler.

The following are the approximate operating steps:

--1) Install Oracle10.2.0.4 in advance and deploy Streams replication (schema replication)

BEGIN
Dbms_streams_adm. Maintain_schemas (
Schema_names = ' Business,business_app ',
Source_directory_object = ' Dir_source ',
Destination_directory_object = ' Dir_dest ',
Source_database = ' sour.net ',
Destination_database = ' DEST. LK ',
Perform_actions = TRUE,
Script_name = NULL,
Script_directory_object = NULL,
Capture_name = ' cap_erating ',
capture_queue_table = ' tab_cap_que_erating ',
Capture_queue_name = ' cap_que_erating ',
Capture_queue_user = NULL,
Propagation_name = ' prop_erating ',
Apply_name = ' app_erating ',
apply_queue_table = ' tab_app_que_erating ',
Apply_queue_name = ' app_que_erating ',
Apply_queue_user = NULL,
Dump_file_name = ' Sour.dmp ',
log_file = ' SOUR.log ',
Bi_directional = FALSE,
INCLUDE_DDL = TRUE,
instantiation = Dbms_streams_adm.instantiation_schema
);
END;
/

--2) Check the target library streams replication is normal

SELECT ' Capture ' process_type,
Capture_name Process_name,
Status
Error_message
From Dba_capture
UNION All
SELECT ' propagation ' process_type,
Propagation_name Process_name,
Status
Error_message
From Dba_propagation
UNION All
SELECT ' Apply ' Process_type,
Apply_name Process_name,
Status
Error_message
From Dba_apply;

SELECT Apply_name,
LOCAL_TRANSACTION_ID,
SOURCE_COMMIT_SCN,
Error_creation_time,
Error_message
From Dba_apply_error
ORDER by SOURCE_COMMIT_SCN DESC;

--3) Stop all business systems and require only 15 minutes of interruption

--4) cancel the Source Library timer task (including crontab/job)

--5) Verify that the source library is still connected

SELECT username, status, COUNT (*)
From V$session
WHERE username in
(' Business ', ' Business_app ')
GROUP by username, status;

--6) Check if the target library has streams replication transactions

SELECT Streams_name,
Streams_type,
Cumulative_message_count,
First_message_time,
XIDUSN,
XIDSLT,
XIDSQN,
Last_message_time,
Total_message_count
From V$streams_transaction
ORDER by 3 DESC;

--7) Get a rebuild sequence statement from the source library, streams Copy this particular note, because streams itself does not synchronize sequence values

Set pages 0
SELECT ' DROP SEQUENCE ' | | Sequence_owner | | '. ' | | Sequence_name | | ‘;‘
stmt
From Dba_sequences
WHERE Sequence_owner in
(' Business ', ' Business_app ')
UNION All
SELECT ' CREATE SEQUENCE '
|| Sequence_owner
|| ‘.‘
|| Sequence_name
|| ' Start with '
|| Last_number
|| ' MAXVALUE '
|| Max_value
|| ' MINVALUE '
|| Min_value
|| DECODE (Cycle_flag, ' N ', ' nocycle ', ' cycle ')
|| DECODE (cache_size, 0, ' NOCACHE ', ' Cache ' | | Cache_size)

|| DECODE (Order_flag, ' N ', ' noorder ', ' ORDER ')

|| '; ' stmt

From Dba_sequences
WHERE Sequence_owner in
(' Business ', ' Business_app ');

--8) Target Library rebuild sequence

--9) source and target libraries refresh synonyms, point to new dblink, synonym scripts ready in advance
--Source Library
@e:\ User Migration \sour_synonyms.sql
--Target Library
@e:\ User Migration \dest_synonyms.sql

--10) Source Library and Target library recompile failed objects
@?/rdbms/admin/utlrp.sql
exec uts.get_invalid;

--11) Each business system changes the database connection point, and starts each business system

--12) test the business system boot situation
SELECT username, status, COUNT (*)
From V$session
WHERE username in
(' Business ', ' Business_app ')
GROUP by username, status;

--12) Add timed tasks to scheduler for ease of management unification

--13) Delete stream configuration
exec dbms_streams_adm.remove_streams_configuration;

--14) Other finishing touches, such as developer queries for user authorizations, and more.

--end--

Ext.: http://www.cnblogs.com/jerryxing/p/3431148.html

Oracle one-time production sub-Library, upgrade, migration