./ggsci: error while loading shared libraries: libnnz11.so

Following error might encountered while initializing ggsci prompt for Oracle Golden Gate

[oracle@oraprm ogg_1]$ ./ggsci
./ggsci: error while loading shared libraries: libnnz11.so:
cannot open shared object file: No such file or directory

This error is encountered as ggsci is unable to locate lib files which are available under database ORACLE_HOME. This error can be avoided by

Set LD_LIBRARY_PATH to point to "lib" directory under database ORACLE_HOME

[oracle@orapm ogg_1]$ cd $HOME
[oracle@orapm ogg_1]$ gedit .bash_profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[oracle@oraprm ogg_1]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oraprm.localdomain) 1>

INS-10002 Inventory Initialization failed


This error occurs when installing Oracle Client_32 bit on Windows 64 bit Server

To resolve this error, delete Registry Entry "inst_loc"

Type Regedit in "Run" Dialog Box from Start Menu

Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

and Delete "inst_loc"

Close the Registry and install Oracle Client 12c again

Try deleting repository before Creating Repository


This error might occur if there is an existing repository

To solve this error

Delete the Repository

or

Drop Oracle user who has created the Repository and again recreate it

Failure I/O Error: SSO failed:Native SSPI not loaded. Check the java.library.path system property

This Failure is due to connection fail between Oracle SQL Developer to MS SQL Server Database

Copy
JDIST file ntlmauth.dll to Java path in your System

D:\jtds-1.3.1-dist\x64\SSO\ntlmauth.dll

To

C:\java\jdk1.8.0_112\bin

After this Check connecting SQL Developer to SQL Server Database

ORA-01031 Insufficient privilege while creating Oracle user

login as "SYS" user

SQL> create user test identified by test;
ORA-01031: insufficient privilege

If you have "Enabled" Oracle Database Vault in your Database this could cause a problem

Solution is Disable Oracle Database Vault

SQL>exec dbms_macadam.disable_dv;

Restart the Database

SQL>create user test identified by test;
User created

SQL>

Oracle Database Vault

Introduction

Oracle Database Vault is part of Oracle Database Security feature that provides powerful security controls to protect application data from unauthorized access

It can restrict access to areas in Oracle Database for any user and also who have administrative access such as DBA or SYSDBA

With Oracle Database Vault, one can address the most difficult security problems prevailing today: protecting against insider threats, meeting regulatory compliance requirements and enforcing separation of duty

It enables data users to apply fine-grained access control to their sensitive data in a variety of ways.
It hardens ones Oracle Database instance and enforces industry standard best practices in terms of separating duties from traditionally powerful users

For example, one can restrict administrative access to salaries, financial data of employees, medical records of employees or customer or other such sensitive information

DBA's can configure Oracle Database Vault to manage security of an individual Oracle Database instance

Installation of Oracle Database Vault can be done on standalone Database, multiple Oracle Homes and also in Oracle RAC environments

Privileges

Oracle Database Vault (ODV) provides set of database roles which enable Oracle Users to perform specific tasks

Most commonly roles are "DV_ADMIN" and "DV_OWNER" which enables one to create and manage policies of ODV

"DV_ACCTMGR" enables one to manage Oracle User accounts

Oracle Database Vault Access Control Components

Oracle Database Vault enables you to create a set of components to manage security for your database instance.

These components are as follows:

Realms
A realm is a protection zone inside the database where database schemas, objects and roles can be secured
For example: One can secure a set of schemas, objects and roles that are related to accounting, sales or human resources.
After one have secured these into a realm, one can use the realm to control the use of system and object privileges to specific accounts or roles.
This enables one to provide fine-grained access controls for anyone who wants to use these schemas, objects and roles.

Command rules
A command rule is a special security policy that you can create to control how users can execute almost any SQL statement, including SELECT, ALTER SYSTEM, Database Definition Language (DDL) and Data Manipulation Language (DML) statements.
Command rules must work with rule sets to determine whether the statement is allowed.

Factors
A factor is a named variable or attribute, such as a user location, database IP address or session user which Oracle Database Vault can recognize and use.
One can use factors in rules to control activities such as authorizing database accounts to connect to the database or execution of a specific database command to restrict the visibility and manageability of data.
Each factor can have one or more identities.
An identity is the actual value of a factor. A factor can have several identities depending on the factor retrieval method or its identity mapping logic

Rule sets
A rule set is a collection of one or more rules that one can associate with a realm authorization, command rule, factor assignment or secure application role.
The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type (All True or Any True)
The rule within a rule set is a PL/SQL expression that evaluates to true or false. One can have the same rule in multiple rule sets

Secure application roles
A secure application role is a special Oracle Database role that can be enabled based on the evaluation of an Oracle Database Vault rule set.

Error: Views\Synonyms required for XA transaction support are missing in this Database 12c

To resolve the above issue, follow below steps:-

As SYS user, enable Oracle database for XA by performing the following steps:

sqlplus / as sysdba

SQL>@$ORACLE_HOME/javavm/install/initxa.sql 
PL/SQL procedure successfully completed.
Execute $ORACLE_HOME/rdbms/admin/xaview.sql

SQL> @xaview.sql
DROP VIEW v$xatrans$
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW v$pending_xatrans$
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
View created.
SQL>

RCU-6083:Failed - Check prerequisites requirement for selected component:OIM

Error: XDB is installed on the database with some errors. Objects in XDB schema are in unusable \ invalid state

1) Check for Invalid objects of XDB Schema

Login to Oracle Database as sysdba

SQL> select * from dba_objects where status='INVALID' and owner='XDB';

It will return Invalid Objects

Then we need to compile those invalid objects

SQL> exec dbms_utility.compile_schema(schema=>'XDB');

Again check for Invalid Objects

SQL> select * from dba_objects where status='INVALID' and owner='XDB';

If there is no Data return by this Select Statement then all Invalid objects are compiled

Error: Oracle TEXT is installed on the database with some errors. Objects in CTXSYS schema are in unusable\invalid state.

1) Check for Invalid objects of CTXSYS Schema

Login to Oracle Database as sysdba

SQL> select * from dba_objects where status='INVALID' and owner='CTXSYS';

It will return Invalid Objects

Then we need to compile those invalid objects

SQL> exec dbms_utility.compile_schema(schema=>'CTXSYS');

Again check for Invalid Objects

SQL> select * from dba_objects where status='INVALID' and owner='CTXSYS';

If there is no Data return by this Select Statement then all Invalid objects are compiled

Uninstall JAVA / JDK in Linux

Login as "root" user

Check the JAVA version

java -version

Query JDK / Java

rpm -qa | grep jdk

or 

rpm -qa | grep java

Uninstall / remove JDK / JAVA

yum -y remove jdk*

or

yum -y remove java<version name>

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

I’ve recently installed an Oracle 12c Release 2 database on Linux and when I bounce the database (using the Shutdown and Startup commands), the database is unable to start

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_LAB11'

This is due to the lack of a LOCAL_LISTENER, that must be defined in the TNSNAMES.ORA, according to the Oracle Database Reference 12cR2:

LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners
(that is, listeners that are running on the same machine as this instance)

The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.

So, in your TNSNAMES.ORA (not in LISTENER.ORA) you must define this entry for the local listener:

LISTENER_LAB11.WORLD =
 (ADDRESS = (PROTOCOL = TCP)(HOST = toralin1)(PORT = 1521))

And now you can startup the database. Be sure to set the .WORLD if you have the parameter NAMES.DEFAULT_DOMAIN set at SQLNET.ORA

You can learn more about the LOCAL_LISTENER in this excellent post of Ed Stevens: Exploring the LOCAL_LISTENER parameter

ORA - 39087 Directory Name is invalid

This error occurs because of Directory permissions in Oracle Data Pump during Export / Import

Resolution

SQL> grant read, write on directory <Directory_Name> to <Schema_Name>;

Move Datafile from Location to Another

Prior to Oracle 12c, moving datafiles has always been an offline task

Oracle 12c includes an enhancement to ALTER DATABASE command to allow datafiles to be moved online

Connect as SYSDBA

SQL>SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf'
TO '/tmp/system01.dbf';

Database altered.

SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

PLS - 00201 Identifier 'dbms_reputil' must be declared

Solution:

sqlplus / as sysdba

SQL>grant execute on dbms_reputil to public;

SQL>exit

PRVG - 1561 Setting ORA_CRS_HOME variable is not supported

Resolution:

unset ORA_CRS_HOME

INS - 30507 Empty ASM diskgroup

Resolution:

Check permission for "sd* in /dev

PRVF - 0002 - Could not retrieve Local Node name

Resolution:

Edit /etc/hosts

Add IP Address and Host name

INS - 40922 Invalid SCAN - unresolveable to an IP Address

Cause:

SCAN provided does not resolve to an IP Address

Resolution:

Check SCAN name, during installation of GRID Infrastructure

ORA - 1653 Unable to Save Records in a table OR Unable to Extend Tablespace

Unable to Save Records in a table OR Unable to Extend Tablespace

Solution:

Check Oracle's Alert.log file for the problem

Reason may be tablespace for that table may be full

So you need to extend the tablespace OR

Create another datafile for that tablespace

Configure MS Outlook Mail for Oracle 12c Database

First install Oracle Packages to Configure Outlook Mail

[host@oracle]$sqlplus / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin\utlmail.sql

SQL> @$ORACLE_HOME/rdbms/admin\utlsmtp.sql

SQL> @$ORACLE_HOME/rdbms/admin\prvtmail.plb

SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;

SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;

SQL> alter system set smtp_out_server='mail.server.com or IP of Mail Server' scope=both;

ACL: Access Control List

Check for current existing ACL’s
Let’s check out all currently present Access Control Lists in the database;

SQL> SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
no rows selected

SQL>

All right none present – let’s create a new Network Access List using

DBMS_NETWORK_ACL_ADMIN — privilege

Use ‘connect’ for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access.
Use ‘resolve’ for UTL_INADDR name/IP resolution.
The text for the privilege is case sensitive.
A list of all available network access privileges is in the
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE reference

Create ACL and Privileges

Now first create an ACL as SYS
(or any other user with DBMS_NETWORK_ACL_ADMIN execute granted)
This will hold the privileges.
You can add as many privileges as you like to this file, but I would recommend to split
privileges in the ACL to specific tasks or users.
You must create an ACL with at least one privilege, so lets start with the ‘connect’
privilege for user SCOTT (also a role can be added as principal)

SQL>BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'acl_test_for_scott.xml',
    description  => 'A test of the ACL functionality',
    principal    => 'SCOTT', */ schema (user) name/*
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/

Add Privilege

Great, now that the ACL is created
You can add more privileges like the ‘resolve’ privilege:

SQL>begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT', */ schema (user) name/*
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

Assign Access Control List (ACL)

Cool, you granted SCOTT to connect and resolve
But you have not defined to which resources/port he is allowed to connect

== ADD PORT 80

SQL>begin
  dbms_network_acl_admin.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.<domain>.nl',
    lower_port => 80,
    upper_port => NULL);
END;
/

== ADD PORT 25

SQL>begin
  dbms_network_acl_admin.assign_acl (
    acl => 'acl_test_for_scott.xml',
    host => 'mail.********.nl',
    lower_port => 25,
    upper_port => NULL);
  COMMIT;

SQL> select * from dba_network_acls

HOST       LOWER_PORT UPPER_PORT ACL
---------- ---------- ---------- ------------------------------
mail.**.nl         80         80 /sys/acls/acl_test_for_scott.xml
mail.**.nl         25         25 /sys/acls/acl_test_for_scott.xml
SQL>

Add another Database User (SCHEMA) to ACL list

SQL>BEGIN
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => 'acl_test_for_scott.xml',
    principal => 'PANNENKOEK', */ schema (user) name/*
    is_grant => TRUE,
    privilege => 'connect');
END;
/

SQL> SELECT acl,
          principal,
           privilege,
           is_grant,
           TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
          TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
  FROM   dba_network_acl_privileges;
SQL> /

ACL                                 PRINCIPAL  PRIVILE IS_GR START_DATE  END_DATE
------------------------------      ---------- ------- ----- ----------- -----------
/sys/acls/acl_test_for_scott.xml    SCOTT      connect true  30-JAN-2013
/sys/acls/acl_test_for_scott.xml    PANNENKOEK connect true  30-JAN-2013

SQLNet.Allowed_Logon_Version is deprecated

Resolution

In Oracle Database 12c parameter Allowed_Logon_Version is deprecated in SQLNet.ora

This parameter is been replaced by 2 new parameters

SQLNET.Allowed_Logon_Version_Server
SQLNET.Allowed_Logon_Version_Client

How to fix: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Open your terminal and type mysql -u root -p Enter your password. Hopefully your MySQL is logged in now.