Administering Database Users - Oracle Database

Creating Database User:

§    Database users are created using the CREATE USER statement.

§   To Create the User, one must have the CREATE USER system privilege (Query DBA_SYS_PRIVS to know whether a USER has CREATE USER system privilege. Please note I am using SYS user to create/alter/drop users in this post)

SQL> select grantee, privilege from dba_sys_privs where grantee = 'SYS' and privilege like '%USER%';

GRANTEE    PRIVILEGE

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

SYS            DROP USER

SYS            CREATE USER

SYS            ALTER USER

SYS            BECOME USER

§  After new user is created user should be granted with CREATE SESSION system privilege, else the user cannot be able to connect to DB.

A typical user creation statement looks as below

SQL> CREATE USER TestUser IDENTIFIED BY Test

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMPTS1

QUOTA 50M ON USERS;

User created.

Granting CREATE SESSION System privilege to user created above

SQL> GRANT Create Session TO TestUser;

Grant succeeded.

§  One need to mention the default TBs, TEMP TBs with the quotas while creating the user else the default tablespaces which are mentioned during database creation are considered and mapped to the user created.

If quota clause is not mentioned, then unlimited TBs quota is allotted

Note:

a.       Default TBs which are mentioned during database creation (or Alter database set default tablespace statement) can be found by querying the DATABASE_PROPERTIES data dictionary view.

SQL> select property_name, property_value from database_properties where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                                     PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE                TEMPTS1

DEFAULT_PERMANENT_TABLESPACE    USERS

b.      To determine the default TBs & Temp TBs of a user created earlier in a database, query DBA_USERS table as below

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'SYSTEM';

DEFAULT_TABLESPACE                TEMPORARY_TABLESPACE

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

SYSTEM                                            TEMPTS1

c.       To know the usage of the tablespace quota allotted to a user, query the DBA_TS_QUOTAS

select USERNAME, TABLESPACE_NAME,BYTES,(MAX_BYTES/1024)/1024 as MaxBytes_IN_MB from dba_ts_quotas where username = 'TESTUSER';

USERNAME   TABLESPACE_NAME  BYTES       MAXBYTES_IN_MB

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

TESTUSER      USERS                                    0                          50

§  You can revoke the ability of a user to create objects in a tablespace by changing the current quota of the user to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.

§  Assigning a Temporary Tablespace

You can set the temporary tablespace for a user at user creation, and change it later using the ALTER USER statement. Do not set a quota for temporary tablespaces.

SQL>

CREATE USER TestUser1 IDENTIFIED BY Test

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMPTS1

  4  QUOTA 50M ON USERS QUOTA 50M ON TEMPTS1;

CREATE USER TestUser1 IDENTIFIED BY Test

*

ERROR at line 1:

ORA-30041: Cannot grant quota on the tablespace

§  While creating the USER profile also need to be mentioned to limit database resources and password access to the database. If no profile is specified, then the user is assigned a default profile(You can see from above user TestUser is created without mentioning the profile, so default profile called DEFAULT is assigned)

SQL> select profile from dba_users where username = 'TESTUSER';

PROFILE

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

DEFAULT

The different resource limits set on the profile – DEFAULT can be known by querying the dictionary table DBA_PROFILES

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';

RESOURCE_NAME                                           LIMIT

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

COMPOSITE_LIMIT                                        UNLIMITED

SESSIONS_PER_USER                                    UNLIMITED

CPU_PER_SESSION                                        UNLIMITED

CPU_PER_CALL                                               UNLIMITED

LOGICAL_READS_PER_SESSION                 UNLIMITED

LOGICAL_READS_PER_CALL                        UNLIMITED

IDLE_TIME                                                       UNLIMITED

CONNECT_TIME                                              UNLIMITED

PRIVATE_SGA                                                 UNLIMITED

FAILED_LOGIN_ATTEMPTS                          10

PASSWORD_LIFE_TIME                                180

PASSWORD_REUSE_TIME                            UNLIMITED

PASSWORD_REUSE_MAX                             UNLIMITED

PASSWORD_VERIFY_FUNCTION                 NULL

PASSWORD_LOCK_TIME                              1

PASSWORD_GRACE_TIME                           7

16 rows selected.

Note: To know the details of different dictionary tables query DICT

SQL> select * from DICT where Table_name like '%PROFILE%';

TABLE_NAME                                                 COMMENTS

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

DBA_PROFILES                                             Display all profiles and their limits

DBA_SQL_PROFILES                                    set of sql profiles

DBA_SQL_TRANSLATION_PROFILES        Describes all SQL translation profiles in the database

ALL_SQL_TRANSLATION_PROFILES         Describes all SQL translation profiles accessible to the user

USER_SQL_TRANSLATION_PROFILES      Describes all SQL translation profiles owned by the user



Altering Database User:

 §  Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege.

Let’s try to change the password of the TestUser created above. Note that the user security changes will come into effect only from the future sessions (not the current session)

[oracle@ol6-12c ~]$ rlsqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 21 08:57:51 2013

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

SQL> connect TESTUSER

Enter password:

Connected.

SQL> show user

USER is "TESTUSER"

SQL> alter user TESTUSER identified by password1;

User altered.

No special privileges (other than those to connect to the database) are required for a user to change passwords.

Dropping Database User:

§  If a user schema and associated objects (Tables, Indexes..) must remain but the user must be denied access to the database, then revoke the CREATE SESSION privilege from the user.

§  When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped. One should be very careful while dropping a user to make sure that implication are clear as in whether any table created by the user is been referenced as foreign key, used in stored procs etc.

§  To drop a connected user, first we need to terminate the user sessions using ALTER SYSTEM with the KILL SESSION clause. A connected user cannot be dropped

Demonstration to show that the connected user cannot be dropped:

To terminal sessions are opened and one connected as SYS & other as TESTUSER. 

While TESTUSER is still connected to database, couldn’t able to drop the user from the SYS user connection. 

Demonstration of using kill session clause in alter system statement to kill the user session which is currently connected to database.

ALTER SYSTEM KILL SESSION 'sid,serial#';

KILL SESSION command just asks the session to kill itself, it doesn’t kill the user session right away.

§  If the user's schema contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. 

For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table

Oracle - Tablespace Management

In this post I will guide you through the steps on creating the tablespace in Oracle 12c Database (the steps are equally applicable for 11g as well)

To create the tablespace and manage it, we need the first the Database be created (Find the detailed steps for creating a database here).

First let’s start with understanding the Tablespaces & Datafiles.

Oracle database stores the data logically in tablespaces and physically in filesystem Datafiles associated with the tablespaces. Below screenshot (courtesy: oracle docs) depicts the relation between the Tablespace (Logical Structure) to DataFiles (Physical Structure) in Oracle Database.

Few important points to be noted about Tablespaces & DataFiles

-    An Oracle Database consists of at least two tablespaces (SYSTEM & SYSAUX explained below), and optional tablespaces – TEMP & UNDOTBS(it is always good practice to have these Tablespaces as well)

-    A Tablespace consists of one or more datafiles and the datafiles are not shared across by multiple tablespaces.

-    Objects (Tables, Indexes etc.,) created in the Tablespaces can span across multiple datafile associated with a Tablespace.

Below are the Tablespaces created in my database (MyDB) and the Datafiles associated with it.

Let’s try to add a datafile to Tablespace USERS and try to add the same again to UNDOTBS, to demonstrate that one datafile can be associated with ONLY one tablespace. Trying to add the Datafile associated already to a tablespace to other Tablespace will error out – ‘ORA-01537 -  ... file already part of database’

You can create multiple no. of tablespaces and most important ones which are required to be mentioned during the database creation are

-    SYSTEM Tablespace: This is the primary tablespace, which contains information basic for functioning of the database server, such as Data Dictionary & System rollback segments.

This is the first tablespace created during the database creation. We cannot rename, drop or take offline.

-    SYSAUX (Auxiliary to SYSTEM) Tablespace: It is the default tablespace for many database features or products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace. Similar to SYSTEM Tablespace, we cannot drop or rename the SYSAUX Tablespace. Based on the initial sizes of these components, the SYSAUX tablespace needs to be at least 240 MB at the time of database creation.

-    UNDO Tablespace: Undo Tablespaces are special tablespaces used solely for storing the undo information (which is used for – database recovery, read consistency etc.,) and we cannot create any other database objects (tables, indexes etc.,) in these tablespaces. Undo Tablespaces are used only when database is in automatic undo management – Init Parameter “undo_management” (this is default mode though)

SQL> show parameter undo_management

NAME                                   TYPE                 VALUE

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

undo_management                 string                AUTO

Let’s create a new Tablespace in Database MyDB created in my Virtual Linux server.

To create/alter a tablespace, the user should have the CREATE/ALTER TABLESPACE system privileges (To know different system privileges granted to a user – query the DBA_SYS_PRIVS table. As I connected to database as SYS, the user is SYS)

Guidelines for managing Tablespaces:

-          - Create multiple tablespaces one for each application, that makes the availability of other applications when a tablespace associated with other application is made offline for maintenance activities.

-          - Store datafiles associated with different tablespaces on different disk drives, that way I/O contention on the drive is reduced

-          - Backup Individual tablespaces separately that way restore/recovery process will be simple as per our need.

-          - Assign tablespace quotas to users to hold the intended object segments.

Creating a Locally Managed Tablespace:

To create the tablespace with extents managed locally, we need to mention the clause – ‘Extent Management’. If you want the database manage the extents automatically, mention – ‘AUTOALLOCATE’ (If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes) or ‘UNIFORM’ (If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents) if you want to manage the tablespace with uniform extent size.

Below is the query to create a locally managed Tablespace with 100MB datafile size, extent management local & autoallocate.

CREATE TABLESPACE EXAMPLE DATAFILE ‘/u01/app/oracle/oradata/MyDB/MyDBDataFiles/example.dbf' SIZE 100M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

The alternative to AUTOALLOCATE is UNIFORM, which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.

Specifying Segment Space Management in Locally Managed Tablespaces:

Segment space management clause of a create tablespace has two options – MANUAL or AUTO

MANUAL - Manual segment space management uses linked lists called "freelists" to manage free space in the segment

AUTO- Automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces

Let’s drop example1 Tbs created above and recreate the same using the CREATE TABLESPACE statement with explicitly mentioning the Segment Management as AUTO

Bigfile Tablespaces

-          - If Database is created by mentioning Bigfile as default for TBs creation, then CREATE TABLESPACE.. statement creates the tablespace as Bigfile Tablespace

-          - Bigfile tablespaces are by default EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO.

       - If you specify EXTENT MANAGEMENT DICTIONARY and SEGMENT SPACE MANAGEMENT MANUAL, then the TBs creation will error out

-                    -  A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

-                    - To find the default TBs type using which the database is created can be found by querying DATABASE_PROPERTIES table

Encrypted Tablespaces

-          - TBs encryption is applicable to Permanent TBs ONLY

-          - Any user who is granted privileges on objects stored in an encrypted tablespace can access those objects without providing any kind of additional password or key

-          - Data from an encrypted tablespace is automatically encrypted when written to the undo tablespace, to the redo logs, and to any temporary tablespace. There is no need to explicitly create encrypted undo or temporary tablespaces, and in fact, you cannot specify encryption for those tablespace types.

-          - Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:

§  3DES168

§  AES128(default when USING keyword is not mentioned)

§  AES192

§  AES256

-          - You cannot encrypt an existing tablespace with an ALTER TABLESPACE statement. However, you can use Data Pump or SQL statements such as CREATE TABLE AS SELECT or ALTER TABLE MOVE to move existing table data into an encrypted tablespace.

-          - Encryption algorithm implemented for a TBs can be determined by querying - v$encrypted_tablespaces

-          - Tablespace encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create the encrypted tablespace and before you can store or retrieve encrypted data.

When we try to create the encrypted TBs without create/open the oracle wallet, then – ‘ORA-28365: wallet is not open’ will be thrown

To correct the above error, create a directory named – ‘wallet’ as in here $ORACLE_HOME/admin/$ORACLE_SID/wallet.

And mention the same in sqlnet.ora file as below

Shutdown/Restart the instance and open the oracle wallet using the ALTER SYSTEM… and then create the encrypted TBs

CREATE TABLESPACE EncryptedTBs

DATAFILE ' /u01/app/oracle/oradata/MyDB/MyDBDataFiles/Encrypted.dbf ' SIZE 100M

ENCRYPTION  <as USING clause is not mentioned, by default AES128 encryption is implemented>

DEFAULT STORAGE(ENCRYPT);

Temporary Tablespaces

-          - TEMP TBs are used to the sorting result set,  Temporary Tables/Indexes created etc.,

-          - Default TEMP TBs is shared by multiple users logged into the database. Default TEMP TBs currently in use can be queries from DATABASE_PROPERTIES table

-          - While creating the TEMP TBs, we should mention the TEMPFILE(not DATAFILE clause which we mention for permanent TBs creation) – in TEMPFILE created oracle just writes to the header & last block of the file, that’s why they are very quick to get created.

-          - For details about TEMPFILE use - V$TEMPFILE, V$TEMP_SPACE_HEADER and DBA_TEMP_FILES

-          - Monitor temporary segments using - V$SORT_SEGMENT, V$SORT_USAGE

Oracle Database Creation using 'Create Statement' from SQLPLUS

In this post I will guide you through the steps on how to create a new database on a new instance created (In my virtual Linux machine I already have a instance & database (orcl) created).

The below details explain the steps involved in creating a second Instance & Database on the standalone server

Details of my Virtual server:

Virtual machine used – Oracle Virtual Box 4.2.18

Operating System – Oracle Linux version 2.6.39

Oracle Server Installed – Oracle 12c r1

All the steps are performed by logging into my virtual linux server using Oracle user connected using sqlplus as sysdba by OS authentication (sqlplus / as sysdba)

Step 1: Specify an Instance Identifier (SID) & Set Environment Variables:

Instance Identifier is a unique name to identify the instance(when there are multiple instances already created in the host server).

Open any text editor in Linux operating(say gedit) and have the SID defined, mention the below commands in gedit and save it with name as .MyDB_Profile (Instance & DB Name we going to create is MyDB). Please find the terminal screenshot, creating a profile file .MyDB_Profile in /home/oracle and setting the environment variables.

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

  . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol6-12c.localdomain

export ORACLE_UNQNAME=MyDB

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1

export ORACLE_SID=MyDB

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

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

Note: .Profile file in Linux - Is the first file which gets executed when you login to a shell. It has some initializations, aliases, exports etc to make you go easy while working on command line. It is present in the user's home directory and it’s a hidden file as it begins with a dot(.) To have the environment variable set to work on creating our database, we need to manually execute .MyDB_Profile file as   . ./.MyDB_Profile

An environment variable is a named object that contains data used by one or more applications. In simple terms, it is a variable with a name and a value. The value of an environmental variable can for example be the location of all executable files in the filesystem, the default editor that should be used, or the system locale settings.

Step 2: Create the Initialization Parameter File:

To start an instance, the database must read instance configuration parameters (the initialization parameters) from either a server parameter file (SPFILE – Binary file) or a text initialization parameter file (init<SID>.ora)

By default Initialization parameter file is located at - $ORACLE_HOME/dbs (in Unix OS)

Create the Initialization parameter file named initMyDB.ora as below and save the same as initMyDB.ora file in $ORACLE_HOME/dbs

db_name='MyDB'

memory_target=1G

processes = 150

audit_file_dest='/u01/app/oracle/admin/MyDB/MyDB_adump'

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/app/oracle/MyDB_fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/app/oracle/MyDB'

dispatchers='(PROTOCOL=TCP) (SERVICE=MyDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

UNDO_RETENTION = 1800

# You may want to ensure that control files are created on separate physical

# devices

control_files = (/u01/app/oracle/oradata/MyDB/ora_control1, /u01/app/oracle/oradata/MyDB/ora_control2)

compatible ='11.2.0'

After connecting to instance(authenticated by OS authentication /) using the initMyDB.ora file, create the spfile from the pfile using the below statement

create spfile from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initMyDB.ora';

nNote: I have installed “readline wrapper”(rlsqlplus alias for sqlplus) utility is my Linux OS to provide a command history and editing of keyboard input commands.

                          

Step 3: Start the Instance:

Nomount clause is used whenever a database need to be created or when performance maintenance need to be performed.

When you try to Startup instance without NOMOUNT clause below error will be displayed

Now start the instance in nomount state and check whether the instance has started using the spfile created as below to create the new database.

Step 4: Create Database using CREATE DATABASE Statement:

Using the below create statement, Database can be created which has the below specifications

§     Database name considered is “MyDB”

§     Directory locations for redo log files & datafiles created before execution the Create Database statement (CREATE DATABASE statement cannot create the directories mentioned if they are not existing already)

/u01/app/oracle/oradata/MyDB/MyDBRedoFiles  - Redo Log Files Location(redo01/02/03.log)

/u01/app/oracle/oradata/MyDB/MyDBDataFiles  - DataFiles Location(system01.dbf, sysaux01.dbf, users01.dbf, temp01.dbf, undotbs01.dbf)

§     Tablespace Extent Management is Locally Managed - A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle Database changes the bitmap values to show the new status of the blocks.

§     Character set considered - AL32UTF8 - Unicode 4.0 UTF-8 Universal character set

§     National Character Set considered - AL16UTF16 - used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2

§     MAXLOGFILES – 5 - Maximum number of redo log file groups that can ever be created for the database. Rt now defined 3 groups(Group 1/2/3) and eventually in the future the can allow to create two more log groups.

§     MAXLOGMEMBERS - 5 - Maximum number of members, or copies, for a redo log file group. Rt now one file is defined in each file group(redo1/2/3.log)

§     MAXDATAFILES – 50 – This defines to add a new data file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES initialization parameter, causes the Oracle Database control file to expand automatically so that the datafiles section can accommodate more files.

CREATE DATABASE MyDB

   USER SYS IDENTIFIED BY <Mention SYS Password>

   USER SYSTEM IDENTIFIED BY < Mention SYSTEM Password >

   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo01.log') SIZE 50M,

           GROUP 2 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo02.log') SIZE 50M,

           GROUP 3 ('/u01/app/oracle/oradata/MyDB/MyDBRedoFiles/redo03.log') SIZE 50M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXDATAFILES 50

   MAXINSTANCES 1

   CHARACTER SET AL32UTF8

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users

      DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/users01.dbf'

      SIZE 300M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/temp01.dbf'

      SIZE 50M REUSE

   UNDO TABLESPACE undotbs

      DATAFILE '/u01/app/oracle/oradata/MyDB/MyDBDataFiles/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; 

Executing the above CREATE DATABASE statement, MyDB Database is successfully created.

Note: In the process of creating the Database whenever as error is faced, one need to shut down the instance(in nomount using shutdown immediate or shutdown) and clear all the files created by the Create Database statement prior to encountering the error.(clear control files created, delete diag folder present in /u01/app/oracle/MyDB, delete datafiles present in /u01/app/oracle/oradata/MyDBDataFiles, delete redologfile present in /u01/app/oracle/oradata/MyDBRedoFiles, delete files present in /u01/app/oracle/admin/MyDB/MyDB_adump, delete folder xdb_wallet  present in /u01/app/oracle/admin/MyDB/xdb_wallet)

In the process I have come across few errors as below (as I forgot to delete the control files which got created because bases on the Initialization file and a mismatch in the UNDO_TABLESPACE initialization configuration error). 

After deleting control files after instance shutdown and then when tried the Create Database statement it got successfully created.

Step 5: Run Scripts to Build Data Dictionary Views:

Be in the NOMOUNT instance state and run the scripts(catalog.sql, catproc.sql, pupbld.sql) to build data dictionary views(v$ views), synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus

catalog.sql - create data dictionary views

catproc.sql - run all sql scripts for the procedural option

pupbld.sql – The PRODUCT_USER_PROFILE (PUP) table provides product-level security that supplements the user-level security provided by the SQL GRANT and REVOKE commands and user roles. 

Run above scripts at the SQL prompt as below with SYSDBA login

1.      @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/catalog.sql (this gonna take 15 – 20 mins to create the complete data dictionary views)

2.      @/u01/app/oracle/product/12.1.0/db_1/rdbms/admin/catproc.sql (this gonna take 20– 30 mins to complete the execution)

3.      To run the pupbld.sql script we should first connect to the database instance as SYSTEM user.To have a successful connection using SYSTEM user make sure you

a.      Stop the listener service as below

b.      Contents of the LISTENER.ora & TNSNAMES.ora files modified as below

Listener.ora file content:

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

            (DESCRIPTION =

                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

                        (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-12c.localdomain)(PORT = 1521)) ) )

SID_LIST_LISTENER =

  (SID_LIST =

      (SID_DESC =

             (GLOBAL_DBNAME=MyDB)

             (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)

             (SID_NAME = MyDB)

      )

  )

 Tnsnames.ora file content

Please note that as I have two instance/databases in my virtual box, tnsnames.ora file should have the descriptions for both as below. Please note that service name value is derived from the initialization parameter set in spfile

 # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-12c.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl.localdomain)

    )

  )

MyDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-12c.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = MyDB.localdomain)

    )

  )

and stop/start the listener service and then connect as SYSTEM user and run the pupbld.sql script(located in /u01/app/oracle/product/12.1.0/db_1/sqlplus/admin)

c.      Start the listener service as below

d. Now connect as SYSTEM user a below

e.      Finally run the pupbld.sql script below in the sql prompt after connecting s SYSTEM user @/u01/app/oracle/product/12.1.0/db_1/sqlplus/admin/pupbld.sql(this gonna take few secs to finish execution)

  

Thanks to my Wife who has supported me in making this post happen. Appreciate her patience, effort & understanding me.

References:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/create003.htm#i1008816

http://www.linuxquestions.org/questions/linux-general-1/how-to-get-os-name-from-command-line-557339/

http://www.youtube.com/watch?v=9CVlcebCERY

Snagit software which helped in capturing the above screenshots.