Special Case of Bi-Directional Replication Part 3

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Dec 2, 2015

Special Case of Bi-Directional Replication Part 3


This is the third installment of the blog series. In this part we will walk through the upgrade of 11.1.0.7 single instance database having bi-directional streams setup to 11.2.0.3 single instance database.

Link to Part 1
Link to Part 2
Link to Part 4

Introduction: 

This is the third installment of the blog series. In this part we will walk through the upgrade of 11.1.0.7 single instance database having bi-directional streams setup to 11.2.0.3 single instance database.


upgrade

 

a) Pre-Upgrade Steps

===============================

1) Run the pre-upgrade information tool for collecting pre-upgrade information.

cd /home/oracle/pre_upgrade_11.2.0.3

sqlplus '/ as sysdba'

spool proda_preupgrade_analysis.txt

@utlu112i.sql

spool off

exit

2) Check for the integrity of the source database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from My Oracle Support (Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information

dbupgdiag.sql https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=556610.1).

cd /home/oracle/pre_upgrade_11.2.0.3

sqlplus '/ as sysdba'

@dbupgdiag.sql

exit

3) Find the users that have CONNECT role granted – in 11.2 the CONNECT Role is deprecated – From Oracle 10.2,'CONNECT' role only includes 'CREATE SESSION' privilege.

If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrade.

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT';

SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and

grantee NOT IN ('SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS',

'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'EXFSYS', 'SYSMAN',

'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

Note :-  In our case we are all good here.

We are upgrading from 11.1.0.7 where CONNECT role has only CREATE SESSION privilege

4) Create a script for DBLINK (in case the database has to be downgraded again)

Since any password in database links is encrypted and all of the database links with encrypted passwords must be dropped prior to the downgrade).

Use the following script that will generate all the commands that will recreate the original db links once the DB is downgraded:

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)

||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY '''

||L.PASSWORD||''' USING '''||L.HOST||''''

||chr(10)||';' TEXT

FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;

5) Check for TIMESTAMP WITH TIMEZONE Datatype. 

* For 11.1.0.6 or 11.1.0.7, there is no need to apply any patchset before upgrading to 11.2.0.3.

There is no need to apply DST patches on the 11.1.0.6 or 11.1.0.7 side first.

We can skip any DST related upgrade instructions.

The 11.2.0.3 RDBMS DST version after the upgrade to 11.2.0.3 will be the same DST version as used in 11.1.0.6 or 11.1.0.7.

6) Ensure that the National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 or AL16UTF16. 

If it is UTF8 or AL16UTF16, then no action is needed. 

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

 7) Check the Optimizer Statistics – To determine the schemas which lack statistics,

 either review the output of the utlu112i.sql script from step 1 or download and run the script

 from – Note 560336.1 Script to Check Schemas with Stale Statistics. 

 EXECUTE dbms_stats.gather_dictionary_stats;

8) Disable Oracle Database Vault

* Use the following link to properly disable the Oracle Database Vault:

http://docs.oracle.com/cd/E11882_01/server.112/e16544/dvdisabl.htm#BJEDGGGA

9) Back up Enterprise Manager Database Control Data.

If the DB is being monitored by Grid Control, there is no need for this step.

i. Set ORACLE_HOME to the old Oracle Home.

ii. Set ORACLE_SID to the SID of the database being upgraded.

iii. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.

iv. Change directory to Oracle Database 11g release 2 (11.2) home.

v. Run the emdwgrd command.

vi. Run the following command for single instance database:

$ emdwgrd -save -sid old_SID -path save_directory

* For more details, we can use the following link as well:

https://support.oracle.com/CSP/main/articlecmd=show&type=NOT&id=870877.1

10) Identify the DB users that will require Network ACL’s configuration.

This is required since 11.2 includes fine-grained access control

to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XMLDB.

In this case, we must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases.

The actions that should be taken are part of the Post Upgrade tasks (step c.5 

* Use the Pre-Upgrade Information Tool to identify the DB users that will require Network ACL’s configuration.

11) Check for corruption in the dictionary.

cd /home/oracle/pre_upgrade_11.2.0.3

sqlplus '/ as sysdba'

@gen_analyze.sql

@$ORACLE_HOME/rdbms/admin/utlvalid.sql

@analyze.sql

select* from INVALID_ROWS;

12) Ensure that no files need media recovery and that no files are in backup mode. 

SELECT * FROM v$recover_file;

SELECT * FROM v$backup WHERE status!= 'NOT ACTIVE';

13) Check for password-protected roles – In version 11.2, password protected roles are no longer enabled by default so it is recommended to remove the password from those roles to allow existing privileges to remain available.

select* from dba_roles;

14) Resolve outstanding distributed transactions prior to the upgrade.

 select* from dba_2pc_pending; 

15) Check to see if a standby database exists. 

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter

WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

16) Disable all batch and cron jobs.

 

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

alter system set job_queue_processes=0 scope=both

. wait for the current running jobs to finish

. comment the crontab scripts

Stop the Replication between server 1 (PRODA) & SERVER 2 (PRODB)

17) Ensure that the users’ SYS and SYSTEM have 'SYSTEM' as their default tablespace.

SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

18) Ensure that, if the aud$ table exists, it is in the SYS schema and in the SYSTEM tablespace.

 

SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';

19) Check whether the database has any externally authenticated SSL users.

If any SSL users are found, WE might need to deal with them after the upgrade: 

 SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL'; 

* For more information we can use the following link:

http://docs.oracle.com/cd/E11882_01/server.112/e10819/afterup.htm#CEGCJHDI

20) Note down the location of datafiles, redo logs, and control files.

Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.

SELECT name FROM v$controlfile;

SELECT file_name FROM dba_data_files;

SELECT group#, member FROM v$logfile;

21) Stop all Oracle-related processes on the PRODB server.

22) Start up the database in mount mode, enable flashback, and create restore point.

startup mount;

alter database flashback on;

alter system set db_recovery_file_dest_size=40G SCOPE=both;

create restore point before_upgrade GUARANTEE FLASHBACK DATABASE;

23) Back up the Database – Perform Cold Backup (Make sure we have ample free space in the backup mount point).

rman target /

RMAN> RUN

{

ALLOCATE CHANNEL cha1 TYPE DISK;

ALLOCATE CHANNEL cha2 TYPE DISK;

ALLOCATE CHANNEL cha3 TYPE DISK;

ALLOCATE CHANNEL cha4 TYPE DISK;

ALLOCATE CHANNEL cha5 TYPE DISK;

ALLOCATE CHANNEL cha6 TYPE DISK;

ALLOCATE CHANNEL cha7 TYPE DISK;

ALLOCATE CHANNEL cha8 TYPE DISK;

BACKUP DATABASE FORMAT '/opt/orabck/RMAN/PRODA/bkup_pieces/%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE;

Release CHANNEL cha1;

Release CHANNEL cha2;

Release CHANNEL cha3;

Release CHANNEL cha4;

Release CHANNEL cha5;

Release CHANNEL cha6;

Release CHANNEL cha7;

Release CHANNEL cha8;

};

24) Make a backup of the /u01/app/oracle/product/11.1.0/db_1/dbs/spfilePRODB.ora file. (Create pfile from spfile as well on a backup location.)

25) Make sure the ORACLE_BASE, ORACLE_HOME, and PATH environment variables point to the new Oracle 11g Release 2 (11.2) directories. 

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE=/u01/app/oracle 

26) Update the oratab entry to set the new ORACLE_HOME pointing to PRODB and disable automatic startup.

27) Set the environment to point to the new ORACLE_HOME.

 .oraenv

ORACLE_SID = [oracle] ? PRODB

b) Database upgrade Steps

===================================

1) At the operating system prompt, change to the new $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home. 

 cd $ORACLE_HOME/rdbms/admin 

2) Start up the database in upgrade mode. 

sqlplus '/ as sysdba'

startup UPGRADE 

3) Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script. 

set echo on

SPOOL upgrade.log

@catupgrd.sql

spool off 

4) Start up the database in normal mode and run the Post-Upgrade Status Tool

$ORACLE_HOME/rdbms/admin/utlu112s.sql, which provides a summary of the upgrade at the end of the spool log. 

sqlplus '/ as sysdba'

STARTUP

@utlu112s.sql 

5) Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory,

to perform upgrade actions that do not require the database to be in UPGRADE mode. 

@catuppst.sql 

6) Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session. 

@utlrp.sql 

7) Check for the integrity of the upgraded database by running dbupgdiag.sql – Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic

Information (dbupgdiag.sql). 

cd /home/oracle/pre_upgrade_11.2.0.3

sqlplus '/ as sysdba'

 @dbupgdiag.sql

 exit 

c) Post Upgrade Steps

============================== 

1) For the upgraded instance, modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener. 

 .oraenv

ORACLE_SID = [oracle] ? PRODB

 lsnrctl start

lsnrctl status 

2) Make sure the ORACLE_BASE, ORACLE_HOME, and PATH environment variables point to the Oracle 11g Release 2 (11.2) directories. 

3) Modify /etc/oratab entry to use automatic startup. 

4) Check the current version of the Oracle time zone definitions in the upgraded database –

This should be the same as the value found before the upgrade. 

SELECT version FROM v$timezone_file; 

5) Configure Fine-Grained Access to External Network Services. 

* Modify the values in the script below appropriately and execute the script for each principal detected in step 10 from part a. 

a) Pre-Upgrade Steps: 

acl_name.xml => Enter a name for the access control list XML file.

ACL description => 'file description',

principal => 'user_or_role',

is_grant => TRUE|FALSE,

privilege => 'connect|resolve',

host_name => host name 

DECLARE

acl_path VARCHAR2(4000);

BEGIN

SELECT acl INTO acl_path FROM dba_network_acls

WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL; 

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'principal','privilege') IS NULL THEN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'principal', is_grant, 'privilege');

END IF;

EXCEPTION

WHEN no_data_found THEN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml','ACL description', 'principal', is_grant, 'privilege');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');

END; 

COMMIT;

6) Enable back all previously disabled batch and cron jobs on the box.
 dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

 alter system set job_queue_processes=1000 scope=both;

 Enable the replication between SERVER 1 (PRODA) & SERVER 2 (PRODB)

.uncomment the crontab scripts 

Conclusion: With above mentioned steps we have completed the upgradation of bi-directional streams setup from version 11.1.0.7 to 11.2.0.3.

In the last part we finally migrate the upgraded bi-directional streams server to the RAC Setup on the ODA environment.

Link to Part 1
Link to Part 2
Link to Part 4 

 

 For any questions on the above steps, please click below:

Ask Ravi