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) .
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:
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
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
Now Try re-creating the table again ………. created successfully!!!!!
you can double check it from TOAD or Sql developer:
Interesting New Feature 😉
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
This can be done by either 2 methods:
Method 1:
Go to $ORACLE_HOME/bin
then execute the command:
$ file oracle
oracle: 64-bit XCOFF executable or object module not stripped
Method 2:-
Login to sqlplus,
use the following query,
SQL> select metadata from sys.kopm$ ;
Search in the output string if it has B023 string then the database is 32 bit else if it is B047 then it is 64 bit
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.
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.
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
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 😉
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
while upgrading Oracle database from version 11.2.0.3 to 12.1.0.1 successfully, i found out the below errors while performing a full database export for the new upgraded 12cR1 database:
ERROR at line 1:
ORA-21700: object does not exist or is marked for delete
ORA-06512: at “SYS.ANYDATA”, line 174
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 3583
why is this happening 😐 ??!
The reason i am facing this error is that Oracle Data Mining Modules (ODM) are not hosted in a separate schema “DMSYS” anymore when upgrading from 11.2.0.x to 11.2.0.3 or even from 10g !! (which should have been dropped while upgrading My Mistake 😦 )
Remark: Data Mining models are implemented as data dictionary objects in the SYS schema
To Correct this , you have either 2 ways:
********* First solutions:
Drop the DMSYS schema in the 11g database.
SQL> CONNECT / AS SYSDBA;
SQL> DROP USER DMSYS CASCADE;
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = ‘DMSYS’;
SQL> SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = ‘DMSYS’;
* if the previous query returns results then perform the following:
SQL> SET HEAD OFF
SQL> SPOOL dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> SELECT ‘Drop public synonym ‘ ||'”‘||SYNONYM_NAME||'”;’ FROM DBA_SYNONYMS
WHERE TABLE_OWNER = ‘DMSYS’;
SQL> SPOOL OFF
SQL> @dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> EXIT;
********* Second Solution:
perform the following in the upgraded 12c database.
connect / as sysdba
exec dbms_scheduler.drop_program(‘JDM_BUILD_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPLAIN_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_EXPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_IMPORT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PREDICT_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_PROFILE_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_SQL_APPLY_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_TEST_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_PROGRAM’);
exec dbms_scheduler.drop_program(‘JDM_XFORM_SEQ_PROGRAM’);
Geodata Master 