Upgrade Oracle from 10.2.0.4 to 11.2.0.4

One of the task dbas have to do is to quickly upgrade Oracle database and binaries, this post will list you the short path to upgrade an Oracle database from 10g to 11.2.

  • Do a normal install of Oracle 11.2.0.4 with latest PSU applied. ( don’t touch the 10g version as you need it to upgrade the databases )
  • On the 10g database run the script utlu112i.sql this script is shipped with the 11.2.0.4 you just have installed. Fix all problems the script showed up!
  • On the 10g, connected as sysdba do the following cleaning tasks:
  • purge dba_recyclebin;
    exec dbms_stats.gather_dictionary_stats;
    shutdown immediate
  • On the 11.2 binaries, start the DB and upgrade it:
    cd $ORACLE_HOME/rdbms/admin
    $ sqlplus / AS sysdba
    SQL> startup UPGRADE
    SQL> SET echo ON
    SQL> SPOOL upgrade.log
    SQL> @catupgrd.sql
    $ sqlplus / AS sysdba
    SQL> STARTUP
    SQL> @utlu112s.sql
    SQL> @utlrp.sql
    SQL> @catuppst.sql
    SQL> @utlrp.sql
    SQL> shutdown
    SQL> startup
    SQL> ALTER system SET compatible='11.2.0.4' scope=spfile;
    SQL> shutdown immediate
    SQL> startup
    SQL> exit
  • Your DB is now upgraded to 11.2.0.4, congrats!
  • If utlu112i.sql let you know that you should upgrade the timezone definition, then follow the next few steps:
cd $ORACLE_HOME/rdbms/admin $
sqlplus / AS sysdba
shutdown immediate;
 
startup upgrade;
SET serveroutput ON
-- check if previous prepare window is ended
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
-- output should be
-- PROPERTY_NAME VALUE
-- —————————- ——————————
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
ALTER SESSION SET "_with_subquery"=materialize;
ALTER SESSION SET "_simple_view_merging"=TRUE;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
-- check if this select
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
-- gives this output:
-- PROPERTY_NAME VALUE
-- ————————— ——————————
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE
shutdown immediate
startup
ALTER SESSION SET "_with_subquery"=materialize;
ALTER SESSION SET "_simple_view_merging"=TRUE;
SET serveroutput ON
VAR numfail NUMBER
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
-- ouput of this will be a list of tables like:
-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- if there where no failures then end the upgrade.
 
VAR fail NUMBER
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
-- output that will be seen:
-- An upgrade window has been successfully ended.
-- Failures:0
 
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
-- needed output:
-- PROPERTY_NAME VALUE
-- —————————- ——————————
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
SELECT * FROM v$timezone_file;
-- needed output:
-- FILENAME                VERSION
-- ——————– ———-
-- timezlrg_14.dat              14
 
SELECT TZ_VERSION FROM registry$database;
UPDATE registry$database SET TZ_VERSION = (SELECT version FROM v$timezone_file);
commit;

Continue reading “Upgrade Oracle from 10.2.0.4 to 11.2.0.4”

Synchronize Key/Value Database between devices

These B4X modules will handle the synchronization of key/value databases between multiple devices and between platforms!

SS-2016-02-15_13.23.32

 

So one application runs on a server widely available on the internet such as a low cost VPS that can run java applications.

Then each application have to add simple code to get synchronized variables.

ckvs.Put("User1", "Key1", 100)
Log(ckvs.Get("User1", "Key1")) '100
Log(ckvs.GetDefault("User2", "Key1", 0)) '0 because User2/Key1 is different than User1/Key1

The SetAutoRefresh method sets the user(s) that will be fetched from the remote store.

For example if we want to auto-synchronize the data of “User1”:

ckvs.SetAutoRefresh(Array("User1"), 5)

Sample Code:

'Code module
#Region  Project Attributes 
	#ApplicationLabel: B4i Example
	#Version: 1.0.0 
	'Orientation possible values: Portrait, LandscapeLeft, LandscapeRight and PortraitUpsideDown
	#iPhoneOrientations: Portrait, LandscapeLeft, LandscapeRight
	#iPadOrientations: Portrait, LandscapeLeft, LandscapeRight, PortraitUpsideDown
	#Target: iPhone, iPad
	#MinVersion: 7
#End Region
 
Sub Process_Globals
	'These global variables will be declared once when the application starts.
	'Public variables can be accessed from all modules.
	Public App As Application
	Public NavControl As NavigationController
	Private Page1 As Page
	Type Item (UserField As String, KeyField As String, ValueField() As Byte, IdField As Long, TimeField As Long)
	Type Task (TaskName As String, TaskItem As Item)
	Private ckvs As ClientKVS
	Private TextField1 As TextField
	Private User As String = "u1"
End Sub
 
Private Sub Application_Start (Nav As NavigationController)
	NavControl = Nav
	Page1.Initialize("Page1")
	Page1.RootPanel.LoadLayout("1")
	NavControl.ShowPage(Page1)
	ckvs.Initialize(Me, "ckvs", "http://xxx.nopapers.org:12345")
	ckvs.SetAutoRefresh(Array(User), 0.1) 'auto refresh every 0.1 minutes
	TextField1.Text = ckvs.GetDefaultAndPut(User, "number", 0)
End Sub
 
Private Sub ckvs_NewData
	TextField1.Text = ckvs.Get(User, "number")
End Sub
 
Sub btnSet_Click
	Dim number As Int = TextField1.Text
	ckvs.Put(User, "number", number)
	Page1.ResignFocus
End Sub
 
Private Sub Page1_Resize(Width As Int, Height As Int)
 
End Sub
 
Private Sub Application_Background
 
End Sub

 

B4A_ClientKVS

B4i_ClientKVS

B4J_ClientKVS

B4J_ServerKVS

jBuilderUtils

 

Continue reading “Synchronize Key/Value Database between devices”