Oracle: March 2017

Saturday, March 25, 2017

6th year blogoversary

Woohoo............its time to celebrate a successful completion of 6th year, yes its been 6 years since i started blogging. i started in Feb/March 2011 for my own understanding, Learning and reference and now it became a hobby of writing every experience i'm having in this oracle DBA world.

I Sincerely thank each and every one who are visiting my blog and i keep blogging.......:) Still more Years to go........

Saturday, March 4, 2017

impdp using network_link parameter ORA-39113: Unable to determine database version

SOURCE and TARGET databases are 12c databases

First copy the TNS of Source DB to the target side and test whether you are able to do tnsping the source db which we have copied

From Target DB:

Create a database link pointing to Source DB

SQL> create database link devdb connect to system identified by ****** using 'devdb';

Database link created.

oradev.domain.com:DEVDB:/opt/oracle/scripts $ impdp nologfile=y network_link=devdb parallel=8 content=metadata_only directory=SCRIPTS_DIR

Import: Release 12.1.0.2.0 - Production on Fri Mar 3 16:20:14 2017

Username: system

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

ORA-39006: internal error

ORA-39113: Unable to determine database version

ORA-06550: line 1, column 7:

PLS-00352: Unable to access another database 'DEVDB'

ORA-06550: line 1, column 7:

PLS-00201: identifier 'SYS@DEVDB' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

ORA-39097: Data Pump job encountered unexpected error -6550

SQL> select * from dba_db_links;

OWNER

--------------------------------------------------------------------------------

DB_LINK

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

HOST

--------------------------------------------------------------------------------

CREATED

---------

SYS

DEVDB

SYSTEM

devdb

03-MAR-17

SQL> sho parameter global

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

global_context_pool_size string

global_names boolean FALSE

global_txn_processes integer 1

Reason:

According to Oracle "private db links would not work in expdp/impdp"

Solution:

So create public database link and try import job again

SQL> create public database link devdb_link connect to system identified by ****** using 'devdb';

Database link created.

SQL> set pages 999

SQL> select * from dba_db_links;

OWNER

--------------------------------------------------------------------------------

DB_LINK

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

HOST

--------------------------------------------------------------------------------

CREATED

---------

SYS

DEVDB àold db link

SYSTEM

devdb

03-MAR-17

PUBLIC

DEVDB_LINK à New db link

SYSTEM

devdb

03-MAR-17

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

oradev.domain.com:DEVDB:/opt/oracle/scripts $ impdp nologfile=y network_link=devdb_link parallel=8 content=metadata_only directory=SCRIPTS_DIR schemas=FMUSER

Import: Release 12.1.0.2.0 - Production on Fri Mar 3 19:19:20 2017

Username: system

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** nologfile=y network_link=devdb_link parallel=8 content=metadata_only directory=SCRIPTS_DIR schemas=FMUSER

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/DB_LINK

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Now my import job runs fine….

NOTE: We cannot import objects containing LONG columns from remote DB over network_link

Please check here:

DataPumpImport Of Tables Containing LONG Columns Using NETWORK_LINK

Reference:

ORA-39113 When Running A DataPump Job Through NETWORK_LINK (Doc ID 2100177.1)

Friday, March 3, 2017

ORA-31679: Table data object has long columns, and longs cannot be loaded/unloaded using a network link

While doing IMPDP using NETWORK_LINK parameter for a 12c database and I see some of the objects failed to import due to error below

impdp nologfile=y network_link=DEVDB_link parallel=8 content=metadata_only directory=EXP_DIR schemas=DEMO

ORA-31679: Table data object "SYSTEM"."STG_DEVDB_SQLPROF" has long columns, and longs cannot be loaded/unloaded using a network link

ORA-31679: Table data object "SYSTEM"."TOAD_PLAN_TABLE" has long columns, and longs cannot be loaded/unloaded using a network link

Reason:

This is Expected behavior when using import over db link for objects containing LONG columns

Solution:

Rather than doing over db link import use regular method (exp dumpfiles and import using same dump files)

Reference:

DataPump Import Of Tables Containing LONG Columns Using NETWORK_LINK Raises The Error ORA-31679 (Doc ID 1470331.1)

Wednesday, March 1, 2017

Logmining server does not exist on this Oracle database.

My Goldengate Integrated extract got ABENDED because of below error

2017-02-28 16:27:56 ERROR OGG-02022 Oracle GoldenGate Capture for Oracle, ext.prm: Logmining server does not exist on this Oracle database.

2017-02-28 16:27:56 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext.prm: PROCESS ABENDING.

Solution:

set the correct DB env and register goldengate extract

GGSCI (oracledev.domain.com) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT 00:00:00 00:33:03

EXTRACT STOPPED PUMP 00:00:00 00:56:48

GGSCI (oracledev.domain.com) 5> register Extract EXT database

2017-02-28 16:32:46 ERROR OGG-08216 ERROR: Cannot register or unregister EXTRACT EXT because no database login was provided. Use DBLOGIN to establish a connection.

GGSCI (oracledev.domain.com) 6> dblogin useridalias ggadmin

Successfully logged into database.

GGSCI (oracledev.domain.com as GGADMIN@demodb) 7> register Extract EXT database

2017-02-28 16:37:40 INFO OGG-02003 Extract EXT successfully registered with database at SCN 324129978306.

GGSCI (oracledev.domain.com as GGADMIN@demodb) 16> start EXT

Sending START request to MANAGER ...

EXTRACT EXT starting

GGSCI (oracledev.domain.com) 8> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT 00:00:00 00:00:02

EXTRACT STOPPED PUMP 00:00:00 00:59:18

Reference:

ERROR OGG-02022 Logmining server does not exist on this Oracle database. (Doc ID 1525272.1)

RMAN waiting for snapshot control file enqueue

oracledev.domain.com:DEMODB:/home/oracle $ . dbaenv

ORACLE_SID = [DEMODB] ? demodb

The Oracle base remains unchanged with value /opt/oracle

oracledev.domain.com:demodb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 28 08:54:29 2017

connected to target database: DEMODB (DBID=77281235)

RMAN> connect catalog demodb_rman/*******@rcv

connected to recovery catalog database

RMAN> show all for db_unique_name all;

waiting for snapshot control file enqueue

user interrupt received

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of show command at 02/28/2017 09:13:52

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03099: job cancelled at user request

RMAN> exit

Check ANY RMAN jobs running/hanging in the Database:

SQL> SELECT SID, SERIAL#,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE",OPNAME,to_char(START_TIME, 'dd-mm-yy hh24:mi:ss') START_TIME FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

no rows selected

SQL> select sid, CLIENT_INFO ch, seq#, event, state from v$session where program like '%rman%';

SID CH SEQ# EVENT STATE

---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- -------------------

172 rman channel=dev_0 61014 imm op WAITING

433 rman channel=dev_1 46223 SQL*Net message from client WAITING

446 16546 SQL*Net message from client WAITING

471 28962 SQL*Net message from client WAITING

509 rman channel=dev_2 17665 SQL*Net message from client WAITING

536 rman channel=dev_3 16948 SQL*Net message from client WAITING

563 rman channel=dev_4 8144 SQL*Net message from client WAITING

588 rman channel=dev_5 54329 SQL*Net message from client WAITING

615 rman channel=dev_6 50208 SQL*Net message from client WAITING

641 rman channel=dev_7 54811 SQL*Net message from client WAITING

10 rows selected.

Need to kill all above SID's .......................

SQL> select SESSION_KEY,SESSION_RECID,START_TIME,END_TIME,STATUS from V$RMAN_BACKUP_JOB_DETAILS where STATUS='RUNNING';

SESSION_KEY SESSION_RECID START_TIM END_TIME STATUS

--------------- ------------- --------- --------- -----------------------

313 313 19-FEB-17 28-FEB-17 RUNNING

SQL> col TIME_TAKEN_DISPLAY for a33

SQL> select session_key, command_id, status, time_taken_display from v$rman_backup_job_details where STATUS='RUNNING';

SESSION_KEY COMMAND_ID STATUS TIME_TAKEN_DISPLAY

----------- --------------------------------- ----------------------- ---------------------------------

313 2017-02-19T18:00:16 RUNNING ###:19:04

SQL> select sid from v$rman_status where COMMAND_ID='2017-02-19T18:00:16';

SID

----------

446

SQL> select SID,serial#,username,osuser,status,machine,program from v$session where sid=446 ;

SID SERIAL# USERNAME OSUSER STATUS MACHINE PROGRAM

---------- ---------- ------------------------------ ------------------------------ -------- ------------------------------ ---------------------------------------

446 35822 SYS oracle INACTIVE oracledev.domain.com rman@oracledev.domain.com (TNS V1-V3)

SQL> alter system kill session '446,35822' immediate;

System altered.

SQL> select session_key, command_id, status, time_taken_display from v$rman_backup_job_details where STATUS='RUNNING';

no rows selected

OEM report ORA-01031: insufficient privileges

Today one of my 12c database OEM report Failed with below error:

Error rendering element. Exception: ORA-01031: insufficient privileges ORA-02063: preceding line from MYPROD

Sql executing from OEM report is ........

SELECT distinct du.username, du.created, du.lock_date, du.account_status, du.expiry_date, u.ptime PW_LAST_CHANGE_DATE, dr.granted_role

FROM dba_users@MYPROD du, sys.user$@MYPROD u, sys.dba_role_privs@MYPROD dr

WHERE du.username=u.name and dr.grantee=du.username order by username

Went to OEM repo database and tried to test below sql

Check the DBLINK DDL:

CREATE PUBLIC DATABASE LINK MYPROD CONNECT TO SYSTEM IDENTIFIED BY <Password> USING 'myprod';

OMSSERVER: REPODB:/home/oracle $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 1 17:25:30 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(*) from dba_users@MYPROD; --> Worked....

COUNT(*)

----------

SQL> select count(*) from sys.dba_role_privs@MYPROD; --> Worked....

COUNT(*)

----------

SQL> select count(*) from sys.user$@MYPROD; --> Didnt Work, so here is the issue

select count(*) from sys.user$@MYPROD

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-02063: preceding line from MYPROD

SQL> select count(*) from sys.user$@MYPROD;

COUNT(*)

----------

Reason:

From 12c, "SELECT ANY DICTIONARY" Privilege No Longer Accesses Some SYS Data Dictionary Tables

Oracle Doc says

“For better security, the SELECT ANY DICTIONARY system privilege no longer permits you to query the SYS schema system tables such as DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Only user SYS has access to these tables, but user SYS can grant object privileges (such as GRANT SELECT ON USER$ TO sec_admin) to other users. “

Solution:

grant select on user$ to SYSTEM;

Now run the report from OEM and it Works !!!

Reference:

EM 12c : Reports Metric Evaluation Errors "ORA-01031: insufficient privileges" (Doc ID 2159658.1)

https://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG000