Convert Oracle SE to EE

Upgrading Oracle database from Standard Edition to Enterprise Edition is very simple.

For example, we have running Oracle SE in ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

Let’s start converting it…

  1. Install new home in /u01/app/oracle/product/12.1.0/dbhome_2 just indicate EE during installation(not SE).
    Response file entries(db_install.rsp):

    ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
    oracle.install.db.InstallEdition=EE
  2. Shutdown database and listener from old home.
    . oraenv
    ORACLE_SID = [ORCL] ?
    
    lsnrctl stop
    sqlplus / as sysdba
    shutdown immediate;
  3. Change ORACLE_HOME in oratab and .bash_profile
    cat /etc/oratab
    ORCL:/u01/app/oracle/product/12.1.0/dbhome_2:Y
    cat ~/.bash_profile
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    export PATH=$PATH:$ORACLE_HOME/bin
    export ORACLE_SID=EYC
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export EDITOR=vi
  4. Copy listener.ora, tnsnames.ora, sqlnet.ora, spfileORCL.oraorapwORCL to new home
    cp /u01/app/oracle/product/12.1.0/dbhome_1/network/*.ora /u01/app/oracle/product/12.1.0/dbhome_2/network/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/12.1.0/dbhome_2/dbs/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL /u01/app/oracle/product/12.1.0/dbhome_2/dbs/

    Please verify that you don’t have old ORACLE_HOME indicated anywhere in these files.

  5. Renew environment variables
    . oraenv
    ORACLE_SID = [ORCL] ? 
    
    which sqlplus
    /u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus
    
    sqlplus / as sysdba
    startup;
  6. Check that you have EE
    SQL> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
    PL/SQL Release 12.1.0.1.0 - Production
    CORE    12.1.0.1.0      Production
    TNS for Linux: Version 12.1.0.1.0 - Production
    NLSRTL Version 12.1.0.1.0 - Production
Advertisements

Disabling/enabling general job execution before/after maintenance

During the maintenance you may need to disable jobs.

To disable jobs created by dbms_jobs set job_queue_processes to zero.

–Save old value

show parameter job_queue_processes
1000

–disable

alter system set job_queue_processes=0;

If you are using dbms_scheduler, this parameter does not work for you.

You will have to run the following:

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

After finishing maintenance, enable them:

alter system set job_queue_processes=1000;
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

 

SYS_OP_C2C internal function, implicit data type converstion

Our database is heavily loaded. In addition, developers are writing codes that make even Exadata to hang 🙂  They are the best hackers ever :):)

Investigating database performance using 13c Cloud Control found one very interesting SQL.
At glance everything is fine, but there is one thing that is important and makes SQL  heavy.

Top SQL:

SELECT nvl(max(bl.id), 0) 
FROM schemaname.eb_restriction_balance bl 
WHERE bl.restrictcode = :b1

SCHEMANAME.EB_RESTRICTION_BALANCE table structure:

SQL> DESC schemaname.eb_restriction_balance

Name Type Nullable Default Comments 
-------------- ------------ -------- ------- -------- 
ID NUMBER 
RESTRICTCODE VARCHAR2(30) 
CURRENCY VARCHAR2(3) 
RESTRICTAMOUNT NUMBER(14) 0 
BALANCE NUMBER(14) 0 
STATE NUMBER(5) 5 
INSERTDATE DATE sysdate 
UPDATEDATE DATE Y 
INN VARCHAR2(30) Y

RESTRICTCODE column is indexed.

So in ideal way the above select should use the index on RESTRICTCODE.

Cloud Control shows that CBO chooses TABLE ACCESS STORAGE FULL.

You can run SQL Tuning Advisor from Cloud Control easily. Advisor generated the following recommendation:

The execution plan of this statement can be improved by creating one or more indices. Consider running the Access Advisor to improve the physical schema design or creating the recommended index.schemaname.EB_RESTRICTION_BALANCE(SYS_OP_C2C(“RESTRICTCODE”))

SYS_OP_C2C means that there happened implicit data type conversion.
So we must find the exact bind value that was used at the time sql was run.

SQL> SELECT name, datatype_string, value_string
 2 FROM v$sql_bind_capture
 3 WHERE sql_id='dnb1771sbm98x';

NAME DATATYPE_STRING VALUE_STRING
------- ------------------ -------------
:B1 NVARCHAR2(128) BR16215493
:B1 NVARCHAR2(128) BR16213680

As you see bind value type was NVARCHAR, that is why oracle converted varchar2 to nvarchar2 and did not use index on RESTRICTCODE.

Solution is to make developer change code and pass the parameter with the type varchar2.

 

How to find remote session executing over a database link

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
substr(decode(bitand(ksuseidl,11),1,'ACTIVE',0,
decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'),
2,'SNIPED',3,'SNIPED', 'KILLED'),1,1) "S",
substr(event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx;

GTXID is the same on both databases.

################################### Sample output ###################################

##Destination

3   LBREPDB01-51715  LBREP.aa2c0b4f.94.11.4694801  5447.62951   I   SQL*Net me

##Source

2   LB\MARIAMI-41196:4058  LBREP.aa2c0b4f.94.11.4694801 87.36231  I  SQL*Net me

More Details:

SID – 87
SERIAL – 36231

Proxy User Authentication: Create DBLINK in another schema

Problem

We need to create private database link in another schema. But we don’t know the password for this user.

Solution (Proxy user)

For example, my username is marik and database link should be created in dbcleanup’s schema.

SQL> alter user DBCLEANUP grant connect through marik;
SQL> connect marik[DBCLEANUP]/123@orcl
SQL> show user
USER is "marik[DBCLEANUP]"
SQL> create database link MYLINK 
connect to LINKUSER identified by "password" using 'ORCL2';

Proxy users can be identified using the PROXY_USERS view.

SQL> select * from proxy_users;

The proxy authentication can be revoked using the following command.

SQL> alter user DBCLEANUP revoke connect through marik ;

RMAN: Displaying current backup progress

To check the progress of your current RMAN backup use this script:

#This is my favorite script

select recid
 , output_device_type
 , dbsize_mbytes
 , input_bytes/1024/1024 input_mbytes
 , output_bytes/1024/1024 output_mbytes
 , (output_bytes/input_bytes*100) compression
 , (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
 from v$rman_status rs
 , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
 and output_device_type is not null

Alert log table x$dbgalertext

Prior to 11g you had to create external table to query alert log information.

In 11g Oracle introduced X$DBGALERTEXT, which is mapped to the alert log file located at  $ORACLE_BASE/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/alert/log_XX.xml.

Note that table is located in SYS schema and you need a permission to see it’s content.

So, please keep in mind this table name, it is really useful to search errors in alert log.