Category: Uncategorized

Moving datafiles online in 12c

arcsdegeoLeave a comment

I want to share with you a very good new feature in 12c , you can now move “datafiles” ONLINE while applications are performing DML operations.

In 11g we used to put tablespace offline, then use the operating system “mv” command to move the datafile from one directory to another then use the command:

ALTER TABLESPACE RENAME ‘/orad08/oradbd14/DUMMY_SM.ORA’ TO ‘/orad07/oradbd14/DUMMY_SM.ORA’;

The drawback was that there is an outage while performing this operation.

Now, in 12c you can perform this operation ONLINE whit no issues.

To illustrate:

A tablepsace TS_DUMMY is currently having a datafile under orad07 directory

tablespace dummy

Executing the command:

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad07/oradbd14/DUMMY_SM.ORA’ TO ‘/orad10/oradbd14/DUMMY_SM.ORA’;

alter database command

NOW the data file has been relocated after the successful execution of the command:

dummy after re-location

Another option is using the same command with the keyword “KEEP” which copies the datafile to the destination directory but keeps the datafile in original directory. However, the default tablespace will reflect the new moved data file !

SQL> ALTER DATABASE MOVE DATAFILE ‘/orad10/oradbd14/DUMMY_SM.ORA’ TO ‘/orad09/oradbd14/DUMMY_SM.ORA’ KEEP;

keep option

2 datafiles exist in both directories

datafile both exists

Default datafile will be the moved one

default data file

You can’t do that with TEMP datafile or Archive,…etc

trying to move tmep datafile

Oracle 12c database password file

arcsdegeoLeave a comment

one of the new features in 12c is having the passwords in the password file being by default case sensitive. also, you can configure password file for the new system account users (SYSDG—-> for data guard, SYSBACKUP —-> for backup operations and administration)

Example of password file command:

orapwd file=EMO_DB_PASS entries=10 sysbackup=y sysdg=y

*** the  EMO_DB file (password file) will be created in the directory where you are currently in.

password file command

as shown in the above picture passwords are required to be entered for SYS,SYSBACKUP,SYSDG accounts.

the following command will be executed to access the database as SYSDG user for verification:

sqlplus SYSDG/cocoChannel7 as SYSDG

sqlplus example

Oracle Export Errors ORA-31693 ORA-29913 ORA-21700

arcsdegeoLeave a comment

While checking the export of status of one of my supported databases (11gR2), i found out the log file has the below errors:

ORA-31693: Table data object “SYSTEM”.”SCHEDULER_PROGRAM_ARGS” failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-21700: object does not exist or is marked for delete

To Resolve this error:

the error shows that you have dropped an object that has dependencies, so you need to re-create it and then drop it.

or directly drop the scheduler objects as shown below.

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’);

impdp ORA-39097 ORA-39065 ORA-56935

arcsdegeo3 Comments

While performing an import, I faced the below error:

ORA-39097: Data Pump job encountered unexpected error
ORA-39065: unexpected master process exception in DISPATCH
ORA-56935: existing datapump jobs are using a different version of time zone data file

Solution:

Check the value of  DST_UPGRADE_STATE by the following query:

SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

Then try executing the following:

ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;
exec dbms_dst.unload_secondary;

Re-execute the query:

SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

****check the value of  DST_UPGRADE_STATE should be NONE

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