Installing Oracle Database 12c in IBM AIX environment

arcsdegeoLeave a comment

The following is a “general purpose” procedure for 12cR1 binary instillation on AIX environment. you still need to refer to Oracle official documentation for further details and clarification.

******** Pre-requisites:

1. Operating System:

Oracle recommends that you install on AIX 6.1 Technology Level 9 or later.

AIX 7.1 Technology Level 1 Service Pack 3 (“7100-01-03-1207”) or later, 64-bit kernel

Note: You can install on AIX 7.1 Technology Level 1, but Oracle recommends that you install on AIX 7.1 Technology Level 3 or later.

2. User Profile , make sure that your profile is referencing to the right new Oracle Home.

3. check your Oracle Inventory file and the correct path.

Instillation Process:

Start the installation by running ./runInstaller from the installation directory

1

2

Choose “YES”

3

choose “skip software updates”

4

5

6

8 - original

15 14 - original 13 12 - original 10 9 8 - original 6 5 4 2 1 3

Oracle Database 11g future patches

arcsdegeoLeave a comment

for any Professional DBA, applying patches is an essential and crucial process. So, to get to the point.

Oracle Database (11.2.0.3) Last patch set will be released on July 2015 !

Oracle Database (11.2.04) Last patch set will be released on January 2018 !

This shows that Oracle are pushing people to go to 12c (my humble opinion) .

Oracle Database 12c: Extended Datatypes

arcsdegeoLeave a comment

The datatypes of varchar2, NVARCHAR2 has increased from 4000 bytes to 32767 bytes in 12c.

After the upgrade if you try to create a table with the new size you can’t do that directly:

create table mousaem.tab1 (id number,text varchar2(32767 byte));

You will receive the below error:

1

To enable this feature:

1.You first need to make sure that the parameter “COMPATIBLE=12.1.0” is added to your init.ora file. usually the initialization parameter file is located under Oracle Home then dbs file system in (unix/linux) environments.

COMPATIBLE=12.1.0

2. Then, add the following parameter in your init.ora file

         max_string_size=EXTENDED

3.Then you need to shut down the database

4.Then startup in “upgrade” mode

2

5.Then execute the script:

@?/rdbms/admin/utl32k.sql

6.Then shutdown the database normally

SQL> shutdown immediate

7.Then “startup” normally and check the parameter

3

Now Try re-creating the table again ………. created successfully!!!!!

4

you can double check it from TOAD or Sql developer:

5

Interesting New Feature 😉

ORA-39000: bad dump file specification & ORA-39142: incompatible version number 4.1 in dump file for (Oracle 11g impdp, oracle 12c expdp)

arcsdegeoLeave a comment

after i have exported the data from my source database which is 12cR1 and imported the dump to the destination database which is 11gR2 , i faced the below error:

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 4.1 in dump file “/export/dir/test.dmp”

In order to fix this error,  you need to lower the exported dump version using the “version” parameter from the source 12cR1 database, using the command:

nohup $ORACLE_HOME/bin/expdp “‘/as sysdba ‘”  DIRECTORY=DIR DUMPFILE=test.dmp  TABLES=sch1.test_table version=11.2 logfile=test_exp.log &

remark: nohup will let the expdp command execute on the background.

so the trick is to add the “version” parameter

removing oracle datapump job

arcsdegeoLeave a comment

you may run into situations where you need to stop an expdp or impdp job from running. killing the datapump process from the Operating System level won’t stop the job , infact if you use the below query you may still see it in “RUNNING” state.

SQL> select * from dba_datapump_jobs;

SOOOOOO you need to kill the datapump job from database-level as follows:

$ORACLE_HOME/bin/expdp “‘/as sysdba'” attach=SYS.”‘EXPORT_TABLE_NAME‘”

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

Check it again :

select job_name,owner_name,state from dba_datapump_jobs;

Remark: EXPORT_TABLE_NAME is the table name resulted in the query unser SYS account.

SELECT ANY DICTIONARY in Oracle 12c

arcsdegeo1 Comment

As part of new Oracle 12c Database security implementation, they have stripped the system privilege ” SELECT ANY DICTIONARY ” from accessing the following SYS tables: USER$, ENC$,DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS.

The reason they removed access from these tables, is just in case you grant “select any dictionary” to a non-dba account, this account wont be able to see the “hashed passwords”. Only the SYS account will be able to query these tables.

in 12c if you simulate that:

sqlplus

SQL> grant select any dictionary to account1;

exit

sqlplus account1/password@DB1

SQL> select * from SYS.USER$;

ORA-01031: insufficient privileges

Bingo……..you can’t query USER$ table although you are granted the “select any dictionary” privilege.

TOAD ORA-01031: insufficient privileges when connecting to Oracle 12c

arcsdegeo1 Comment

An issue  faced is that when we try to connect from TOAD (version: 11.6 ) to a newly upgraded Oracle database 12c we faced the error:

ORA-01031: insufficient privileges

TOAD Error Message ORA-01031

The reason we are facing this problem, is the newly security implementation by Oracle in 12c for the system privilege SELECT ANY DICTIONARY. They have stripped this system privilege from accessing the following tables:USER$, ENC$,DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS

The current TOAD version checks the old (SELECT ANY DICTIONARY) which already has this table SYS.USER$ part of its permission when it can’t access this table, it throws the error message:  ORA-01031

The reason in 12c they stripped those tables from “SELECT ANY DICTIONARY” is to protect the passwords in case this privilege is granted to non-DBA account , so the hashed passwords are not exposed (ONLY SYS account can query those tables).

So, to let the current version of TOAD works for 12c execute the following sql query:

SQL> grant select on sys.user$ to database_account;

Hope This Helps 😉

thumps_up

ORA-00604: error occurred at recursive SQL level 4 ORA-12751: cpu time or run time policy violation

arcsdegeoLeave a comment

while checking my database, i noticed there is a performance issue. So, to investigate further i have checked the alert log file.

(Remark: you can check the location of your alert log file using the query: select * from v$diag_info; )

the below is the contents of the log file

****************************************************************************************************

ORA-00604: error occurred at recursive SQL level 4
ORA-12751: cpu time or run time policy violation
Mon Jan 26 02:52:36 2015
Suspending MMON slave action ketbgac2_ for 82800 seconds
Mon Jan 26 03:02:37 2015
Suspending MMON slave action ketbgac1_ for 82800 seconds
Mon Jan 26 05:01:38 2015
Suspending MMON slave action kdilmclnslv_ for 82800 seconds
Mon Jan 26 06:23:43 2015
Errors in file /oraclex/oradbp61/diag/rdbms/oradbp61/oradbp61/trace/oradbp61_m00b_44433466.trc:
ORA-00604: error occurred at recursive SQL level 4
ORA-12751: cpu time or run time policy violation
Mon Jan 26 06:49:41 2015

************************************************************************************************

To Resolve this:

you need to monitor and check the performance of the server where database is hosted in.

also, you can try to figure out the SQL statement that is taking a long time to process.

in my situation, when trying to establish sqlplus from within the server the, sqlplus was in state of “hanging ”

so the only solution was to forcefully shutdown the database by killing the core Oracle Process.

Unix Command:

ps –ef | grep smon