DBCA and DBUA are unable to start in Oracle 12.1.0.2 on IBM AIX

DBCA and DBUA are unable to start in Oracle 12.1.0.2  /12.2.0.1 on IBM AIX

Problem Description:

Today I performed a new installation of Oracle 12.1.0.2 on IBM AIX 7.1 in a single instance environment. After a successful Oracle Software instllation I tried to execute "dbca" utility (Database Configuration Assistant) in order to create the database and I recevied the following error:

$ ./dbca
UnsatisfiedLinkError exception loading native library: njni12
java.lang.UnsatisfiedLinkError: njni12 (rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
from module /oravl01/oracle/app/product/12.1.0.2/dbhome_1/lib/libnjni12.so(), but a runtime definition
of the symbol was not found.
rtld: 0712-002 fatal error: exiting.)
Exception in thread "main" java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.get(Ljava/lang/String;)Ljava/lang/String;
at oracle.net.config.Config.getNetDir(Unknown Source)
at oracle.net.config.Config.initConfig(Unknown Source)
at oracle.net.config.Config.(Unknown Source)
at oracle.sysman.assistants.util.NetworkUtils.(NetworkUtils.java:298)
at oracle.sysman.assistants.util.step.StepContext.(StepContext.java:388)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:1055)
at oracle.sysman.assistants.dbca.backend.Host.(Host.java:1048)
at oracle.sysman.assistants.dbca.wizard.UIHost.(UIHost.java:241)
at oracle.sysman.assistants.dbca.wizard.InteractiveHost.getInstance(InteractiveHost.java:78)
at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:208)
at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:121)
at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:226)
$ smit aio
Warning: Missing charsets in String to FontSet conversion
Warning: Cannot convert string "-dt-interface system-medium-r-normal-l*-*-*-*-*-*-*-*" to type FontSet
$

After an investigation in MOS (My Oracle Support) I found the following article:

AIX 12.1.0.2 Installation Fails with "rtld: 0712-001 Symbol CreateIoCompletionPort was referenced " (Doc ID 1949184.1)

What I found in the article is that this issue is a known issue for Oracle 12.1.0.2 in IBM AIX on POWER Systems (64-bit).

SOLUTION:

The solution, as described in Doc ID 1949184.1 is actually a very simple one, all you need to do is enable to IOCP (I/O Completion Ports). The steps to do that are:

1.

Log in as root and run the following command:
# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.

2. 
Change configured state at system restart from Defined to Available.
Restart the machine

3.
Run the lsdev command to confirm the IOCP status is set to Available:

$ lsdev | grep iocp
iocp0 Available I/O Completion Ports

4.
Relink Oracle Home. In order to do that, connect with the Oracle software owner user and then:

Set the ORACLE_HOME environment variable

Execute $ORACLE_HOME/bin/relink all

5. 
That's it ! Now the "dbca" and "dbua" runs successfuly 

Oracle Binaries installation failed with Error in CreateOUIProcess(): 13 : Permission denied

Oracle Binaries  installation failed with Error in CreateOUIProcess(): 13 : Permission denied


Error in CreateOUIProcess(): 13 : Permission denied”


CAUSE
~~~~~~~~~~

/tmp directory is mounted with noexec option.


You can verify this with following command:

mount
on /tmp type ext3 (rw,noexec,nosuid,nodev)

SOLUTION
~~~~~~~~~~~~~
1) Consult your System Administrator and Remove “noexec” on /tmp folder
For example, to temporarily disable the noexec flag:

mount -o remount,exec /tmp
 or

2) Redirect temp directory to other location which has exec and later retry installation

Example-

export TMP=/u01/oracle
export TMPDIR=$TMP



Oracle support reference document (Doc ID 1671840.1)

Enabling Flashback Database and checking the status

Enabling Flashback Database and checking the status
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Flashback status of a database can be checked from the below query and system parameters.
-          SQL> select NAME,FLASHBACK_ON from v$database; -          SQL> archive log list -          SQL> show parameter undo_retention -          SQL> show parameter DB_FLASHBACK_RETENTION_TARGET -          SQL> show parameter  db_recovery_file_dest -          SQL> show parameter  db_recovery_file_dest_size

Enabling Flashback Database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1. you can either revert the entire database to a prior point in time by restoring a backup and doing point-in-time recovery,
2. or you can enable Flashback Database.

When you enable Flashback Database, the database generates flashback logs in the fast recovery area.
These logs are used to flash back the database to a specified time.
During usual operation, the database occasionally logs images of data blocks to the flashback logs.
The database automatically creates, deletes, and resizes flashback logs.


command to check if Flashback Database is enabled for your target database:
SELECT FLASHBACK_ON FROM V$DATABASE;

To enable Flashback Database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Ensure that you configure a fast recovery area and that the database is running in ARCHIVELOG mode.


Connect Oracle Recovery Manager (RMAN) to the target database

Optionally, specify the length of the desired flashback window (in minutes) by setting the DB_FLASHBACK_RETENTION_TARGET initialization parameter.
The default value for this parameter is 1440 minutes, which is one day.
The following command specifies that the flashback window must be 3 days.


ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

Enable the Flashback Database feature for the whole database using the following command:

ALTER DATABASE FLASHBACK ON;

You can also execute the commands in this section by connecting to the target database using SQL*Plus instead of RMAN.

ORA-00314: log 42 of thread 2, expected sequence# 37363 doesn’t match 37361 and ORA-01624: log 1 needed for crash recovery of thread 1

ORA-00314: log 42 of thread 2, expected sequence# 37363 doesn’t match 37361
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ORA-01624: log 1 needed for crash recovery of thread 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

recover database until  cancel give above error
and clear log file gives below error

ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: '//'
ORA-00312: online log 1 thread 1: '//'

To implement the solution, please execute the following steps:

1) Switch the logfile on primary so that group can become non-current.

connect / as sysdba
alter system switch logfile;



2) Copy from primary to standby.
If you are  working with Group 1 on the primary then copy both  files for group 1 from the primary to the standby machine into an alternate location at operating system level.


3) Move current group one files on the standby to a different location


4) Rename the logfile- Move the copied  redo logs into the group 1 current location

ALTER DATABASE RENAME FILE
'/FULL_PATH_OF_OLD_LOCATION/AND_REDO_LOG_NAME.LOG'
TO
'/FULL_PATH_OF_NEW_LOCATION/AND_REDO_LOG_NAME.LOG';



5) Clear the logfile :

alter database clear logfile group;


6)alter database drop standby logfile group1 .



Clear standby redo with ora-314 error.

Stop recovery on standby side.

SQL> Alter database recover managed standby database cancel;

clear standby redo group 404. -



SQL>  alter database clear logfile group 404


You may have to use the 'unarchived'-Keyword to be able to clear the Standby RedoLog Group in most Cases, eg.

SQL> alter database clear unarchived logfile group 404;



another good solution is to copy the requested archive log into restored database, and recover database

1. copy the next 2-3 archives to target database same location or  different location(if it is different location, register the archive location through RMAN using below command )
CATALOG START WITH 'location path';


2.alter database recover database until cancel;
enter auto, when prompted

3.open database with resetlogs.

for better safety, shut immediate and startup the database.

ORA-00313: open failed for members of log group string of thread string tips

ORA-00313: open failed for members of log group string of thread string tips

ORA-00313 ,ORA-00312 open failed for members of log group

At this article I shall present how to resolve open failed for members of log groups. 

So last night i have seen many errors about those groups. 

I wanted to recreate my redo log groups however as my research there was an other solution. 

The Oracle docs note this on the ora-00313 error:

ORA-00313: open failed for members of log group string of thread string

Cause: The online log cannot be opened. May not be able to find file.

Action: See accompanying errors and make log available.

1.solution

 1-Check status of log file group

SQL> select group#,thread#,status from v$log ;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 ACTIVE
3 1 CURRENT

2- Clear logfile.

SQL> alter  database clear unarchived logfile group 1;
Database altered.

3-check again groups.

SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 ACTIVE
3 1 CURRENT

5-Switch log file to use

SQL>Alter system switch logfile;

6-check again groups.

SQL> select group#,thread#,status from v$log ;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 ACTIVE

So, that's it. if you have problem with an other log try for others to. 

İf so you have problem again try 2 solution.

2.Solution

1-CLOSE DATABASE.

SQL>shu immediate;
Database closed.

2-Check the status of the logile 

select
 b.group#, a.archived, a.status, b.status, b.member
from
 v$log a, v$logfile b
where a.group#=b.group#
order by 1,2;

    GROUP# ARC STATUS           STATUS  MEMBER
---------- --- ---------------- ------- ----------------------------
         1 YES   INACTIVE            /u01/app/oracle/oradata/redo01.log
         2 YES   ACTIVE               /u01/app/oracle/oradata/redo02.log
         3 NO    CURRENT            /u01/app/oracle/oradata/redo03.log

 If the status is CURRENT don't try do drop.

3. Drop log group
SQL>ALTER DATABASE DROP LOGFILE GROUP 1;

4-Add new Redo Log by :

SQL>ALTER DATABASE ADD LOGFILE GROUP 1 ' /u01/app/oracle/oradata/redo01.log' SIZE 50M;


4-Recover DB and Open

SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;

DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1)

DataPump Export (EXPDP) Creates Excessive Traces Even If It Runs Successfully (Doc ID 1154963.1) To BottomTo Bottom


APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS
A simple 11.2 DataPump export (EXPDP) at SCHEMA level (e.g. on SCOTT schema) can be shown to generate a number of DWxx (and also seen from P00x and ORAxx processes) traces in /diag/trace directory. The traces are of the following nature: *** MODULE NAME:(Data Pump Worker) *** ACTION NAME:(SYS_EXPORT_FULL_02) KUPC: Setting remote flag for this process to FALSE prvtaqis - Enter *** 2010-07-15 23:00:23.133 prvtaqis subtab_name upd prvtaqis sys table upd *** 2010-07-15 23:00:23.831 kwqberlst !retval block kwqberlst rqan->lagno_kwqiia 5 kwqberlst rqan->lascn_kwqiia > 0 block


Traces are typically < 100K in length. If the EXPDP run was successful the traces contain no error messages, if EXPDP raises an internal 600/7445 or other errors the traces might contain some equivalent information.


SOLUTION
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The first messages come from package body DBMS_PRVTAQIS which is called during EXPDP/IMPDP. ORACLE Support can confirm that in 11.2 source code the call to DBMS_PRVTAQIS is not considered to be debugging code so there is no option (event/parameter) to disable these traces. The traces will just need to be periodically deleted. It should be noted that EXPDP/IMPDP are not the only products to call DBMS_PRVTAQIS, any product that does can also create these traces and the MODULE NAME in the traces will clarify this. FIXED RELEASE ---- 11.2.0.4 PATCH FIX ---- Patch:12817425 is not available for 11.2.0.1. WORKAROUND ---- None. To purge trace files manually you could execute: adrci> purge -age {minutes} -type TRACE

RANGE - Interval Partitioning In Oracle

Range Interval Partitioning In Oracle

Interval Partitioning has been introduced in oracle 11g.
With this method, we can automate the creation of range partition .
While creating the partitioned table, we just need to define one partition.
New partitions will be created automatically based on interval criteria when the data is inserted to the table. We don’t need to create the future partitions.

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.PART_TEST_TABLE ( CORRELATION_ID VARCHAR2(100 BYTE), ATTRIBUTE_KEY VARCHAR2(50 BYTE), ATTRIBUTE_VALUE VARCHAR2(100 BYTE), EVENT_TIMESTAMP TIMESTAMP(6) ) TABLESPACE STCDBA PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION PART_TEST_TABLE_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) );

CREATE INDEX DBATEST.PART_TEST_TABLE_COR_ID_IDX ON DBATEST.PART_TEST_TABLE (CORRELATION_ID) LOCAL; Index created.

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point



2. Now check dba_tab_partitions

SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


3. Lets insert some data and check the dba_tab_partitions:

insert into dbatest.PART_TEST_TABLE values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016') commit; SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='PART_TEST_TABLE'; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------------- ---------------------------------- --------------------------------------------------------------------- PART_TEST_TABLE PART_TEST_TABLE_MIN TIMESTAMP' 2016-05-01 00:00:00'


We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))



Convert Existing Range partitioned table to interval partition:
ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));


Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause

Convert NON-Partitioned table into Partitioned table in oracle

Convert NON-Partitioned table into Partitioned table in oracle

This is one of the new feature of oracle 12.2 release .

Non-partitioned tables can be converted to partitioned table online without any downtime to the application , i.e no impact to the DML activities.

Till now for this activity, we were using dbms_redef methods. But in Oracle 12.2 release this has been simplified much.


See the below example:

1. Identify the non partitioned table.

desc BSSTDBA.PART_TEST_TABLE Name Type ---------------------------------------- -------- ----------------------------  ROW_ID                                    NOT NULL VARCHAR2(15 CHAR)  CREATED                                   NOT NULL DATE  CREATED_BY                                NOT NULL VARCHAR2(15 CHAR)  LAST_UPD                                  NOT NULL DATE  MODIFICATION_NUM                          NOT NULL NUMBER(10)  CONFLICT_ID                               NOT NULL VARCHAR2(15 CHAR)  ALW_PART_SHIP_FLG                         NOT NULL CHAR(1 CHAR)

SQL> col owner for a13 SQL> col table_name for a14 SQL>  set lines 299 SQL> select owner,table_name,partitioned from dba_tables where table_name='PART_TEST_TABLE'; OWNER         TABLE_NAME     PAR ------------- -------------- --- BSSTDBA       PART_TEST_TABLE      NO <br /> <br /> <br /> SQL> select count(*) from BSSTDBA.PART_TEST_TABLE;   COUNT(*) ----------     954598 SQL>create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id); Index created.

SQL> create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created); Index created.
 2. Alter table modify to partition the table.( partition key is column CREATED ) alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), PARTITION Created_MX VALUES LESS THAN (MAXVALUE)) ONLINE;

This activity will take some time depending upon amount of data table has.

While this alter statement is running, I have started running DML activities on the same table, To check whether it is impacting the DML activities.



SESSION 2:
  insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE; Lets check for blocking session:        SID USERNAME             MODULE                         STATUS   EVENT                          BLOCKING_SESSION ---------- -------------------- ------------------------------ -------- ------------------------------ ----------------        490 SYS                  sqlplus@bttstdev64 (TNS V1-V3) ACTIVE   enq: TX - row lock contention                 7 SID      > 490  SQL_TEXT > alter table BSSTDBA.PART_TEST_TABLE modify PARTITION BY RANGE (CREATED) (partition created_2105_p8 VALUES LESS THAN (TO_DATE('01/09/2015', 'DD/MM/YYYY')), partition created_2105_p9 VALUES LESS THAN  (TO_DATE('01/10/2015', 'DD/MM/YYYY')), partition created_2105_p10 VALUES LESS THAN (TO_DATE('01/11/2015', 'DD/MM/YYYY')), partition created_2105_p11 VALUES LESS THAN (TO_DATE('01/12/2015', 'DD/MM/YYYY')), partition created_2105_p12 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')), partition created_2016_p1 VALUE LESS THAN (MAXVALUE)) ONLINE SID    >  7 SQL_TEXT> insert into BSSTDBA.PART_TEST_TABLE select * from BSSTDBA.PART_TEST_TABLE;

We can see that the insert statement(SID 7), is blocking the alter table command(SID 490), not the other way around. It means during this partition conversion activity, if any DML requests are coming, then it will allow them to complete their request. This may slow down the partition conversion time, But it won’t impact the application. Once ALTER TABLE MODIFY is completed. Check the whether table was partitioned properly or not.

SQL> select partition_name,high_value from dba_tab_partitions where table_name='PART_TEST_TABLE'; PARTITION_NAME          HIGH_VALUE ----------------------- -------------------------------------------------------------------------------- CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA CREATED_MX              MAXVALUE 20 rows selected.

But what happened to the INDEXES:
select  index_name,PARTITIONED from dba_indexes where table_name='PART_TEST_TABLE'; INDEX_NAME          PARTITIONED    ------------------- ------------ PART_TEST_TABLE_IND1      NO      PART_TEST_TABLE_IND2      YES

We can see PART_TEST_TABLE_IND1 was NON partitioned, But PART_TEST_TABLE_IND2 was partitioned.



Oracle document Says:

If no index clause is mentioned in the alter table statement, then

nonprefixed indexes(i.e index column is not a partitioned key) will be become global non-partitioned Index.
prefixed indexes(i.e index column is a partitioned key) will become local partitioned Index.


PART_TEST_TABLE_IND1 - ------------------- INDEX_SQL - >  create index BSSTDBA.PART_TEST_TABLE_IND1 on BSSTDBA.PART_TEST_TABLE(row_id);

It is an nonprefixed Index i.e index column is not a partitioned key. So it became global non partitioned Index


PART_TEST_TABLE_IND2  -
--------------------

create index BSSTDBA.PART_TEST_TABLE_IND2 on BSSTDBA.PART_TEST_TABLE(created);

It is an prefixed Index.  i.e index column in a partitione key .
So this indexes became local partitioned Index.


SQL> select index_name,PARTITION_NAME,HIGH_VALUE from dba_ind_partitions where index_name='PART_TEST_TABLE_IND2'; INDEX_NAME          PARTITION_NAME          HIGH_VALUE ------------------- ----------------------- -------------------------------------------------------------------------------- PART_TEST_TABLE_IND2      CREATED_2016_P9         TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P10        TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P11        TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P12        TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P8         TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_2105_P9         TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PART_TEST_TABLE_IND2      CREATED_MX              MAXVALUE 20 rows selected. There are lot of 12.2 New features like moving a table online, Spliting a partitioned table online etc. which we will discuss very soon.