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 and reference and now it become a hobby of writing every experience i'm having in this oracle DBA world.

My Cordial thanks to each and every one visiting my blog and i keep blogging.......:)

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

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

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

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

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:

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

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

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
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
^C
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
       446
       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';

Login to sqlplus and check the same views using db links

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

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

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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(*)
----------
        20

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

  COUNT(*)
----------
        99
                               
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(*)
----------
        72

                               
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)



Wednesday, February 22, 2017

ADD CREDENTIALSTORE goldengate 12c

The ADD CREDENTIALSTORE is a new command in Oracle GoldenGate 12c and the default location of the credential store is “$GG_HOME/dircrd” directory of the GoldenGate software home. Before 12c to hide the passwords from param files we need to encrypt the passwords and place them in all param files


Let’s add a user to credential store

GGSCI (oradev01) 3> DBLOGIN USERID GGADMIN, PASSWORD xxxxx
Successfully logged into database.

GGSCI (oradev01 as GGADMIN@testdb) 4> ADD CREDENTIALSTORE

Credential store created in ./dircrd/.

GGSCI (oradev01 as GGADMIN@testdb) 5> ALTER CREDENTIALSTORE ADD USER GGADMIN ALIAS ggadmin
Password:

Credential store in ./dircrd/ altered.

Now check the login with newly created alias name……..

GGSCI (oradev01 as GGADMIN@testdb) 6> dblogin useridalias ggadmin
Successfully logged into database.

oradev01:testdb:/opt/oracle/GG/home $ cd dircrd
oradev01:testdb:/opt/oracle/GG/home/dircrd $ ls -lrth
total 4.0K
-rw-r----- 1 oracle dba 517 Feb 22 22:55 cwallet.sso

From above we see that Auto Login wallet has been created

To verify:

GGSCI (oradev01) 1>  INFO CREDENTIALSTORE

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggadmin
  Userid: GGADMIN


GGSCI (oradev01 as GGADMIN@testdb) 1> edit params mgr
PORT 7809
USERIDALIAS ggadmin
PURGEOLDEXTRACTS /opt/oracle/GG/RT, USECHECKPOINTS

NOTE: "ggadmin" in param file is case sensitive


Thursday, February 9, 2017

OGG-00369 Oracle GoldenGate Capture for Oracle: Error in token clause for TK_HOST.

GGSCI (sourceserver) 3>  view params

SETENV (ORACLE_SID=mydev)
userid ggadmin, password ****
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
EXTTRAIL /migrate/source/GG/home/dirdat/lt
DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE
--DDL INCLUDE MAPPED OBJNAME *.*;
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE  MYDEV.TEST,
TOKENS ( TK_HOST = @GETENV ("GGENVIRONMENT" , "HOSTNAME"),
TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),
TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ),
TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"),
TK_COMMIT_TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"),
TK_RBA = @GETENV ("GGHEADER", "LOGRBA"),
TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"),
TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"),
TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"));


GGSCI (sourceserver) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     ABENDED     EDEV      00:42:32      00:01:14
EXTRACT     RUNNING     PHRDEV      00:00:00      00:00:02


Error:

2017-02-09 16:48:53  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params EDEV.
2017-02-09 16:51:14  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, edev.prm:  MAP (TABLE) resolved (entry MYDEV.TEST): TABLE  "MYDEV"."TEST", TOKENS ( TK_HOST = @GETENV("GGENVIRONMENT" , "HOSTNAME"), TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"), TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ), TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"), TK_COMMIT_TS =@GETENV ("GGHEADER", "COMMITTIMESTAMP"), TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"), TK_RBA = @GETENV ("GGHEADER", "LOGRBA"), TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"), TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR")).
2017-02-09 16:51:14  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, edev.prm:  No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-02-09 16:51:14  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, edev.prm:  Using the following key columns for source table MYDEV.TEST: ID, NAME.
2017-02-09 16:51:14  INFO    OGG-01298  Oracle GoldenGate Capture for Oracle, edev.prm:  Column function diagnostic message: could not find column "GGENVIRONMENT".
2017-02-09 16:51:14  ERROR   OGG-00369  Oracle GoldenGate Capture for Oracle, edev.prm:  Error in token clause for TK_HOST.
2017-02-09 16:51:14  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, edev.prm:  PROCESS ABENDING.


Solution:

The code in the 12c version is now changed the text enclosed within double quotes is treated as column name and the text enclosed within single quotes is treated as literal text, which used to be double quoted text pre 12c versions.

Now, change Double quotes to Single and start extract

GGSCI (sourceserver) 3> view params

SETENV (ORACLE_SID=mydev)
userid ggadmin, password ****
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)
TRANLOGOPTIONS EXCLUDEUSER GGADMIN
EXTTRAIL /migrate/source/GG/home/dirdat/lt
DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE
--DDL INCLUDE MAPPED OBJNAME *.*;
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TABLE  MYDEV.TEST,
TOKENS ( TK_HOST = @GETENV ('GGENVIRONMENT' , 'HOSTNAME'),
TK_OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'),
TK_DBNAME = @GETENV('DBENVIRONMENT' , 'DBNAME' ),
TK_GROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),
TK_COMMIT_TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
TK_POS = @GETENV ('GGHEADER', 'LOGPOSITION'),
TK_RBA = @GETENV ('GGHEADER', 'LOGRBA'),
TK_TABLE = @GETENV ('GGHEADER', 'TABLENAME'),
TK_OPTYPE = @GETENV ('GGHEADER', 'OPTYPE'),
TK_BA = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'));


Reference:
token with getenv bugs in 11.2.1 and 12.1 (Doc ID 1948440.1)


Auto Scroll Stop Scroll