Monday, March 28, 2016

Restricting database access to Hostname or IP addeess

**************************************************************
                      FROM MY CLIENT MACHINE:
**************************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************
NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :
**************************************************************

ON DB SERVER:

oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tcp.validnode_checking = yes  ## I have just added this line to test if it works

[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
 TNS-12560: TNS:protocol adapter error

As expected it doesn't work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03)  ## I only want to accept connections from my localhost i.e Linux03

RESTART THE LISTENER NOW.

[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

 (OR)

[oracle@Linux03 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41

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

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-MAR-2016 15:28:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

****************************************************
NOW I TRY TO CONNECT FROM CLIENT AGAIN :
****************************************************

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed

C:\Windows\System32>


Nope it doesn't allow me to connect.

*************************************************************
Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see
**************************************************************

[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully

C:\Windows\System32>tnsping pdborcl1

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)

**************************************************************************
SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER
*************************************************************************


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)

YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :

tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)







Thursday, March 24, 2016

PDB ADMIN account created while Pluggable database creation doesn't have DBA level privs

PDB ADMIN ACCOUNT DOESN'T HAVE PERMISSIONS TO CREATE/DROP by DEFAULT unless explicitly granted:

********************* ******************************
pdborcl1 is the user created while creating PDB via DBCA:
********************* ******************************

[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1/oracle@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:36:23 2016

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

Last Successful login time: Mon Mar 07 2016 11:21:22 -06:00

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 table_name from tabs;

no rows selected

SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> exit

***************************** ******************************
NOW LOGIN AS USER pdborcl1_usr2 who has a table created in his user
***************************** ******************************

[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016

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

Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00

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 table_name from tabs;

TABLE_NAME
--------------------------------------------------------------------------------
TEST

CREATE LOCAL USERS IN 12C DATABASE

******************** ********************************** **************
CREATE LOCAL USERS IN 12C PDB DATABASE:
******************** ********************************** **************

When connected to a multi-tenant database the management of users and privileges is a little different to traditional Oracle environments. In multi-tenant environments there are two types of user.

Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.


******************** **************
Create Local Users
******************** **************

When creating a local user the following requirements must all be met.

You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.

CONN / AS SYSDBA

-- Switch container while connected to a common user.

ALTER SESSION SET CONTAINER = pdborcl1;

-- Create the local user using the CONTAINER clause.

CREATE USER pdborcl1_usr1 IDENTIFIED BY pdborcl1_usr1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO pdborcl1_usr1 CONTAINER=CURRENT;

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr1/pdborcl1_usr1@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:52:50 2016

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 option

SQL> sho user
USER is "PDBORCL1_USR1"

******************** ------ ******************** ----- **************
-- Connect to a privileged user in the PDB.
******************** ------ ******************** ----- **************

CONN system/password@pdb1

-- Create the local user using the default CONTAINER setting.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
SQL> GRANT CREATE SESSION TO pdborcl1_usr2;

SQL> exit

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:56:29 2016

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> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
This dba role will be only specific to prdborcl1 local PDB database.

SQL> grant pdb_dba to PDBORCL1_USR2;

Grant succeeded.


SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges

As per above doc (https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414) PDB_DBA Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role.

IF I try to use a dba user in pdborcl1 and try to connect onto pdborcl2. It wont work

oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:13:09 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied

NOW I LOGGED IN AS SUPER USER AND GRANTED DBA TO PDBORCL1_USR2;

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:32:01 2016

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> ALTER SESSION SET CONTAINER = pdborcl1 ;

Session altered.

SQL> grant dba to PDBORCL1_USR2;

connect back as PDBORCL1_USR2 user and tried to create table :

SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

SQL> show user
USER is "PDBORCL1_USR2"

SQL> create table test (name varchar2(20));

Table created.

SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST

SQL> insert into test values ('arvind') ;

1 row created.

SQL> commit;

SQL> select * from test;

NAME
--------------------
arvind

SQL> create view test1 as select * from test;

View created.

Note: I was able to have a great deal of priv as dba but only till "pdborcl1". I still do not have any priv on "prdorcl2" or any other DB's in CDB.

~~~~~~~~~~~~~
NOW LETS TRY LOGGEING INTO PDBORCL2 (Nope still doest work), as this dba role has been granted to pdborcl1_usr2 only for PDB "pdborcl1"
~~~~~~~~~~~~~

[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:35:11 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied



*************************** **********************************
Now lets create same user in OTHER PDB "PDBORCL2"
*************************** **********************************

[oracle@Linux03 ~]$ sqlplus /"as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:48:56 2016

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> ALTER SESSION SET CONTAINER = pdborcl2;

Session altered.

SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;

User created.

SQL> grant CREATE SESSION,dba to pdborcl1_usr2;

Grant succeeded.

SQL> conn pdborcl1_usr2/pdborcl1_usr2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:50:33 2016

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> show con_name

CON_NAME
------------------------------
PDBORCL2
SQL> show user
USER is "PDBORCL1_USR2"
SQL> select username from dba_users where username like 'PDB%';

USERNAME
--------------------------------------------------------------------------------
PDBORCL2
PDBORCL1_USR2

Conclusion :

1) You can have same users with same username and pass in two diff PDB's as local user.Yet they have no permissions on each other.
2)  Youe will need CDB_DBA -- it provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS,   and SELECT ON CDB_LOCAL_ADMIN_PRIVS

ORACLE 12C STILL SUPPORTS TRADITIONAL EXPORT EXP

************************************
SCHEMA EXPORT
************************************
[oracle@Linux03 dpump]$ exp pdborcl1_usr2/XXXXXXXXXX@pdborcl1  file=/u01/app/oracle/dpump/exp_test.dmp log=/u01/app/oracle/dpump/exp_test.log

Export: Release 12.1.0.2.0 - Production on Wed Mar 23 09:02:30 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PDBORCL1_USR2
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user PDBORCL1_USR2
About to export PDBORCL1_USR2's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PDBORCL1_USR2's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                        TEST_ID          2 rows exported
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
.
.
.
.
.
. exporting statistics
Export terminated successfully with warnings.

************************************
EXPORT TABLE :
************************************

[oracle@Linux03 dpump]$ exp pdborcl1_usr2/XXXXXXXX@pdborcl1  file=/u01/app/oracle/dpump/exp_test.dmp log=/u01/app/oracle/dpump/exp_test.log tables=PDBORCL1_USR2.TEST

Export: Release 12.1.0.2.0 - Production on Wed Mar 23 09:25:04 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

NEW feature Last login timestamp in 12c Database

*********************************   **********************************

         12c Database now shows the last login time stamp.

*********************************   **********************************

[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/XXXXXXX@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016

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

Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00

SQL> show user

USER is "PDBORCL1_USR2"

SQL> select table_name from tabs;

TABLE_NAME
--------------------------------------------------------------------------------

TEST


SQL> col last_login FOR a25
SQL> col username FOR a15
SQL> SELECT username,last_login FROM dba_users WHERE username='PDBORCL1_USR2';

USERNAME LAST_LOGIN
--------------- -------------------------
PDBORCL1_USR2 07.03.2016 14:39:21



  *********************** NOTE ******************************

NON DBA USERS CAN'T QUERY DBA_USERS TABLE. TRY THIS AS DBA USER


SQL> SELECT username,last_login FROM dba_users WHERE username='PDBORCL1_USR2';
SELECT username,last_login FROM dba_users WHERE username='PDBORCL1_USR2'
                                *
ERROR at line 1:
ORA-00942: table or view does not exit

*********************************   **********************************

You can disable times tamp at run time as below

[oracle@Linux03 pdborcl3]$ sqlplus -nologintime pdborcl1_usr2/pdborcl1_usr2@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:51:40 2016

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

However even if you can disable times tamp at run time , it will be audit in dba_user table:

SQL> SELECT username,last_login FROM dba_users WHERE username='PDBORCL1_USR2';

USERNAME                      LAST_LOGIN
---------------                      -------------------------
PDBORCL1_USR2        07.03.2016 14:51:40



Points to remember:


  • However even if you can disable times tamp at run time , it will be audit in dba_user table.
  • This Time stamp wont be recorded for SYS user.
  • This Time stamp wont be recorded for OS authenticated users.
  • No login time recording for administrative users respectively password file users


Tuesday, March 22, 2016

TNS-12560: TNS:protocol adapter error TNS-00584: Valid node checking configuration error

[oracle@Linux04 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-MAR-2016 16:00:27

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

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux04/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux04)(PORT=1521)))

TNS-12560: TNS:protocol adapter error
 TNS-00584: Valid node checking configuration error

Listener failed to start. See the error message(s) above...


[oracle@Linux04 ~]$ cd $ORACLE_HOME/network/admin
[oracle@Linux04 admin]$ vi sqlnet.ora 

[oracle@Linux04 admin]$ cat sqlnet.ora 
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03)    <<< Here is the problem. I have wrong local hostname in                                                                                    tcp.invited_nodes


Correcting the validnode_checking. I got this issue resolved.


[oracle@Linux04 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-MAR-2016 16:08:26

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

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux04/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux04)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux04)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-MAR-2016 16:08:26
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/Linux04/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux04)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

OPEN ALL PLUGGABLE DATABASES AT ONCE


[oracle@Linux03 Desktop]$ sqlplus /"As sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 22 14:47:02 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.
Database opened.


SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE

**************************************************************************************
The pluggable database <pdb> clause can be any of the following:
**************************************************************************************

One or more PDB names, specified as a comma-separated list.
The ALL keyword to indicate all PDBs.
The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.
Some examples are shown below.

ALTER PLUGGABLE DATABASE pdborcl1, pdborcl2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdborcl1, pdborcl2 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdborcl1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdborcl1 CLOSE IMMEDIATE;



Monday, March 14, 2016

ORACLE GENERATED ALWAYS AS IDENTITY

Lets create a new table with Oracle 12c New feature Identity column.

CREATE TABLE TEST_ID
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL
, FNAME VARCHAR2(20)
, LNAME VARCHAR2(20)
, CONSTRAINT TEST_ID_PK PRIMARY KEY
  (
    ID
  )
  ENABLE
);

Table TEST_ID created.


INSERT INTO "PDBORCL1_USR2"."TEST_ID" (FNAME, LNAME) VALUES ('test', 'me');
INSERT INTO "PDBORCL1_USR2"."TEST_ID" (FNAME, LNAME) VALUES ('and', 'me');

Commit Successful;

Oracle generate the PK values for us. We no longer have to worry about auto increment.Leaving everything in oracle hands.

select id,FNAME,LNAME from TEST_ID;

        ID FNAME                LNAME            
---------- -------------------- --------------------
         1 test                 me                
         2 and                  me  


In the Background Oracle creates the a sequence & uses it to generate the id for this column and auto populates these id's.

select sequence_name from user_sequences;

SEQUENCE_NAME                                                                
--------------------------------------------------------------------------------
ISEQ$$_91816  

Create Common Users in 12c container database

********************************** Create Common Users**********************************


When creating a common user the following requirements must all be met.

You must be connected to a common user with the CREATE USER privilege.
The current container must be the root container.
The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
The following example shows how to create common users with and without the CONTAINER clause from the root container.

oracle@Linux03 echo $ORACLE_SID
ORCL

CONN / AS SYSDBA

SQL> show con_name
con_name CDB$ROOT

Lets query some local users by filtering con_id >2. ususally CON_ID greater that 2 is user created PDB's.

SQL> set echo on
SQL> show con_name
con_name CDB$ROOT
SQL> column USERNAME format a40
SQL> column CON_ID format 99
SQL> select username,CON_ID,common from CDB_USERS where CON_ID >2 and common='NO';

USERNAME                                 CON_ID COM
---------------------------------------- ------ ---
PDBORCL1_USR2                                 5 NO
PDBORCL1                                      5 NO
PDBORCL1_USR1                                 5 NO
PDBORCL1_USR2                                 4 NO
PDBORCL2                                      4 NO
IX                                            3 NO
SH                                            3 NO
PDBADMIN                                      3 NO
BI                                            3 NO
OE                                            3 NO
SCOTT                                         3 NO
HR                                            3 NO
PM                                            3 NO

13 rows selected

******************************************************
Now I want to create user C##CDBADMIN1
******************************************************


SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected


-- Create the common user using the CONTAINER clause.
CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDBADMIN1 CONTAINER=ALL;

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      4 YES

******************************************************
-- Create the common user using the default CONTAINER setting.
******************************************************

SQL> CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle;

User created.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      3 YES
C##CDBADMIN1      4 YES

SQL> alter session set container=pdborcl1;

Session altered.

***************************************************************
We can grant permission only on current pdb to common user.
****************************************************************

SQL> grant create session TO C##CDBADMIN1 CONTAINER=CURRENT;

Grant succeeded.

**********************************
Lets test connecting to current pdb:
**********************************
SQL> conn C##CDBADMIN1/oracle@pdborcl1
Connected.
SQL> show user
USER is "C##CDBADMIN1"
SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

*************************************
Lets test connecting to other pdb now:
**************************************

SQL> conn C##CDBADMIN1/oracle@pdborcl2
ERROR:
ORA-01045: user C##CDBADMIN1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.


*****************************************************************
YOU can revoke the grants on CDBADMIN11 using REVOKE cmd:
*****************************************************************

SQL> revoke CREATE SESSION from C##CDBADMIN1 CONTAINER=ALL;

Revoke succeeded.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      4 YES

*******************************************************
You can drop COMMON USER as below connecting to sys:
*******************************************************

SQL> drop user C##CDBADMIN1 ;

User dropped.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected



Friday, March 4, 2016

Enabling archive log mode -12c

Since the Redologs are created at container database level in 12c and not at pluggable database level. (Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB's.

You can check archive log mode either by querying v$database or archivelog list 

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13

*************** ***************
To enable the Archvielog mode
*************** ***************

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       ARCHIVELOG


SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13

open all pluggable databases -12c

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ ONLY
PDBORCL3       READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED
PDBORCL3       MOUNTED


SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE

Clone the pluggable database - 12c

********************* *********************
   Select a pluggable database to clone
********************* *********************

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE

SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open read only;

Pluggable database altered.


********************* *********************
  Lets clone the pluggable database now
********************* *********************

SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');  

Pluggable database created.

SQL> alter pluggable database pdborcl3 open;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ WRITE
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************

DROPPING-PLUGGABLE-DATABASE








Dropping a pluggable database -12c

********************* *********************
Dropping a pluggable database :
********************* *********************

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE



SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES
*
ERROR at line 1:
ORA-65025: Pluggable database PDBORCL3 is not closed on all instances.

You need to close the database before dropping.

SQL> alter pluggable database PDBORCL3 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       MOUNTED

SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE


Lets make sure by looking at datafile location

[oracle@Linux03 pdborcl3]$ pwd
/u01/app/oracle/oradata/orcl/pdborcl3

[oracle@Linux03 pdborcl3]$ ls -ll
total 0


[oracle@Linux03 pdborcl3]$ 


Thursday, March 3, 2016

Connecting to a pluggable database -12c

******* ********** *********
USING EZCONNECT:
******* ********** *********

[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016

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> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

******** ********* *********
USING TNSNAMES:
******** ********* *********

Create a tnsnames entry for the pdb that you want to connect

[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

pdborcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
 



[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016

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> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1