Wednesday, 7 June 2017

Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 on 12c PDB

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB.
In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially
created and can usually no longer be changed, at least not easily or without involving quite a lot of work  (export, recreate database, import).
Except when creating the database, where the national character set is defined explicitly,


You require SYSDBA authorization to change the national character set.
Changing the national character set means changing an Oracle Dictionary entry, but no data is changed.

Connect PDB as sysdba

[oracle@CDB ~]$ sqlplus sys/password@SID as sysdba

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0
NLS_NCHAR_CHARACTERSET
AL16UTF16                                                                 0

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                   AL16UTF16



Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> STARTUP RESTRICT;
Pluggable Database opened.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> update props$
set value$='UTF8'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 

2 rows updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Pluggable Database closed.

SQL> ALTER DATABASE OPEN ;
Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                    UTF8

Monday, 5 June 2017

ORA-28374


When I am trying to create tablespace on pluggable databases (PDB) facing below issue.

SQL Error: ORA-28374: typed master key not found in wallet
28374. 0000 -  "typed master key not found in wallet"

*Cause:    You attempted to access encrypted tablespace or redo logs with
           a typed master key not existing in the wallet.

*Action:   Copy the correct Oracle Wallet from the instance where the tablespace
           was created.


Solution

Login with CDB and follow below steps.

[oracle@JCDB ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 5 15:53:48 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c EE High Perf Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options


SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
OPEN                           AUTOLOGIN                     0

SQL> select '!rm ' || wrl_parameter || 'cwallet.sso' from v$encryption_wallet;

'!RM'||WRL_PARAMETER||'CWALLET.SSO'
--------------------------------------------------------------------------------
!rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> !rm /u01/app/oracle/admin/CDB/tde_wallet/cwallet.sso

SQL> administer key management set keystore close;

keystore altered.

SQL> select status, wallet_type, con_id from v$encryption_wallet;

STATUS                         WALLET_TYPE              CON_ID
------------------------------ -------------------- ----------
CLOSED                         UNKNOWN                       0


SQL> administer key management set keystore open identified by sys_password container=all;

keystore altered.

SQL> administer key management set key identified by sys_password with backup;

keystore altered.

SQL> administer key management set key identified by sys_password with backup container=all;

keystore altered.


Retest issue.


SQL> alter session set container=PDB;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB

SQL> CREATE TABLESPACE tablespace-name DATAFILE '/u02/app/oracle/oradata/CDB/data01.dbf' size 1024M;

Tablespace created.


issue resolved.

ORA-29874: warning in the execution of ODCIINDEXALTER routine

R12 E-Business Suite Autoconfig Fails On Script jtfictx.sh With Errors ORA-29874: warning in the execution of ODCIINDEXALTER routine And DRG-100595 and DRG-11000 After An Upgrade To 12.1.3 And RDBMS 12.1.0.1 (Doc ID 2223844.1) To BottomTo Bottom


APPLIES TO:

Oracle Applications DBA - Version 12.1.3 and later
Information in this document applies to any platform.
SYMPTOMS

E-Business Suite R12 Applications DBA, Upgrade Related issues

 After an upgrade from R12.0.6 to R12.1.3 including a database migration from 11.2.0.3 to 12.1.0.1,  AutoConfig fails with the following error:

ERROR
-----------------------
jtfictx.sh started at Mon Jan 16 09:00:00 2017


SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 16 09:00:00 2017

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

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
DECLARE
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed
DRG-11000: invalid keyword SYNC
ORA-06512: at "SYS.DBMS_SQL", line 1120
ORA-06512: at line 96


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
jtfictx.sh exiting with status 1
ERRORCODE = 1 ERRORCODE_END
.end std out.
...
...
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
 

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
  Directory: /prodapps/PROD/inst/apps/UPDB8_oupapp/admin/install
  jtfictx.sh INSTE8_PRF 1


AutoConfig is exiting with status 1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Upgrade R12.0.6 to R12.1.3
2. Upgrade database from 11.2.0.3 to 12.1.0.1.
3. Run AutoConfig on DB Tier and then run AutoConfig on AP Tier.



CHANGES

 Upgraded EBS from R12.0.6 to R12.1.3 and Database from 11.2.0.3 to 12.1.0.1

CAUSE

One of the mandatory patches for the 12.1.3 upgrade was not applied:

Patch 16289505 - INTEROPERABILITY PATCH FOR EBS 12.1.3 ON RDBMS 12C (12.1.0.1)

The case is discussed in the following bug:

BUG 22235648 AFTER 12C DATABASE UPGRADE ISSUE WITH AUTOCONFIG - JTFICTX.SH EXITING WITH STATUS,

Per the above bug the following SQL statement should have been replaced with call to ad_ctx_ddl.sync_index in jtfirip_ux.sql 120.4.12010000.4:

  ALTER INDEX REBUILD ON LINE PARAMETERS('sync')

This file should have been provided with patch 16289505 when upgrading DB to 12.1.0.1 as stated in the following document:
Note 1524398.1 - Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1


SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:

1. Download Patch 16289505 along with any required prerequisite patches.

2. Apply Patch 16289505 along with any required prerequisite patches per the readme instructions.

3. Restart the instance to ensure the changes are successful.

4. Retest AutoConfig and confirm the error is resolved.

Saturday, 3 June 2017

ORA-06598: insufficient INHERIT PRIVILEGES privilege

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges.

12c introduced this new piece of functionality that initially looks a little odd but actually does make sense when you follow it through, i would imagine there are limited cases though where this specific functionality is required.

What it essentially allows only is relevant  for invokers rights procedures where you might want a certain piece of the code block to have rights that the invoker might not have and you don't want to give them that right directly - you only want it to be valid when they run that code block. This new feature allows you to do that - with me so far?

Ok lets do a quick demo, we'll create a c##test user which will own the plsql procedure and a c##rich user who we will let invoke it but we don't actually want that user to have any additional rights - just be able to run the few lines of plsql with elevated rights. Now you could argue here - why bother with invokers rights then just run with definers rights - and in the simple case below that is true - but I'm sure there are more complex other cases where this new feature is useful.

Anyway lets set a few things up - starting with the user

Re-create grants and synonyms for Apps Schema using adadmin utility

Facing below error
sqlplus -s APPS/***** @/apps/ebs/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adgrnctx.sql &systempwd &un_fnd CTXSYS &un_jtf JTF

Connected.
Connected.
User altered.
declare
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SYSTEM.AD_DDL", line 165
ORA-06512: at line 160

Solution: -

12c prevents this with this new feature:
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges
But if SYS trusts x, the following can be done:

SQL> grant inherit privileges on user sys to system;
Grant succeeded.

This will allow x to inherit the privileges of SYS when running code like the above.

SQL> grant INHERIT PRIVILEGES ON USER sys to public;
Grant succeeded.

SQL> grant INHERIT PRIVILEGES ON USER system to apps;
Grant succeeded.

SQL> grant dba to system;
Grant succeeded.

SQL> grant dba to apps;

Grant succeeded.

ORA-01804

Autoconfig Failed On 12c Database With "ORA-01804: failure to initialize timezone information"
(Doc ID 1908836.1)

Oracle Applications DBA - Version 12.1.0.2 to 12.1.3 Information in this document applies to any platform.

SYMPTOMS

On Oracle Applications 11.5.10.CU2, OR 12.0.6 OR 12.1.3 version, when attempting to execute autoconfig on the database tier,

the following error occurs:

ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly


The issue can be reproduced at will with the following steps:
1. Autoconfig run on the database tier.
2. The error occurs.

CHANGES

Newly upgraded 12c Database.

CAUSE

The issue is caused by 12c Timezone Specification file code was not added in following files:

ad8idbux.env
ad8idbnt.env
adlnkoh.sh

---------------------In ad8idbux.env file---------------------

if test "%s_database%" = "db112" -o "%s_database%" = "db121" ; then
     ORA_TZFILE=""
else
     ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"
fi
export ORA_TZFILE
---------------------In ad8idbux.env file---------------------



---------------------In ad8idbnt.env file---------------------

IF "%s_database%" == "db121" (
     set ORA_TZFILE=
)
---------------------In ad8idbnt.env file---------------------

This issue has been fixed in ad8idbux.env 120.8.12010000.3 or Higher, and in ad8idbnt.env 120.4.12010000.5 or
Higher.

SOLUTION

To implement the solution, please execute the following steps:

1. Download and review the read me and pre-requisites for Patch 16289505.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions(database node):
       $ORACLE_HOME/appsutil/template/ad8idbux.env     120.8.12010000.3
       $ORACLE_HOME/appsutil/template/ad8idbnt.env     120.4.12010000.5
       $ORACLE_HOME/appsutil/template/adlnkoh.sh         120.16.12010000.11
You can use the commands like the following:
       strings -a $ORACLE_HOME/appsutil/template/ad8idbux.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/ad8idbnt.env | grep Header
       strings -a $ORACLE_HOME/appsutil/template/adlnkoh.sh | grep Header

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

OR try below steps also issue resolved.

Database:- 12.1.0.2 or Apps:- R12.1.3

SQL> conn / as sysdba
Connected.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18



Symptoms

 Running AutoConfig on a database tier of E-Business Suite 12.1.3 results in the following error:
ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

Cause

 This issue is caused by the current AutoConfig template file that points to the wrong file.

Solution


1. This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:

From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat

To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_18.dat

2. Running AutoConfig after this change means the environment file is created with the ORA_TZFILE value as timezlrg_14.dat and AutoConfig completes successfully.


Issue resolved.

Friday, 2 June 2017

UnsatisfiedLinkError exception loading native library: njni11

When running Autoconfig for Oracle database 12.1.0.2.0 with R12.1.3 i get the 

error:
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /lib/libnjni11.so: ld.so.1: java: fatal: /lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)
/lib/libnjni11.so: wrong ELF class: ELFCLASS64 (Possible cause: architecture word width mismatch)


Solution 

1. Apply patch latest TXK patch. For R12.1.3 Patch 7651166 

2. Migrate the code from the APPS-Tier to the DB-Tier via the appsutil.zip 

3. Check that appsutil has all the required subdirectories (for example scripts, if not copy them from the old appsutil!) 

4. Create the Database Context File again
perl $ORACLE_HOME/appsutil/bin/adbldxml.pl
template=$ORACLE_HOME/appsutil/template/adxdbctx.tmp
out=/u01/clone/db/tech_st/11.2.0/appsutil/CLONE_ebs6.xml

5. Run Database Autoconfig

Oracle Support says that ORACLE_HOME 11.2 does not contain 32-bit libraries, but Autoconfig still uses a 32-bit Java version. You must use 64-bit Java. 

Friday, 19 May 2017

Oracle row count for all tables in schema

Count each number of table rows in specific schema.



Oracle ACE Laurent Schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single SQL query list:

SQL> set pages 999;
SQL> col count format 999,999,999;
SQL> spool /home/oracle/row_count.txt
SQL> select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from
   user_tables
order by
   table_name;

SQL> spool off;

Output like this

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5

Thursday, 20 April 2017

UNDO tablespace keeps growing in Oracle

The UNDO tablespace size is governed by the the undo_retention parameter.  f its non-default and is set to a high value in order to enable a large flash recover area.  Undo table-space will tend to keep the old data till that time period. Also check out that whether you have any transactions which are not getting committed/rolled back as if this is not happening than your table-space will keep on growing.

Undo data is managed within the UNDO table-space using UNDO segments that are automatically created and maintained by Oracle. Every Oracle Database must have a method of maintaining "before images" of rows that is used to roll back changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed.  UNDO rows are use for several purposes.  We also see these reasons for UNDO:
  • Transaction rollback:   UNDO is the mechanism by which you can undo your changes before a commit with the ROLLBACK command. Also, if a process is abnormally terminated UNDO will be used to clean up any uncommitted transactions.

  • Flashback. Oracle 10g and beyond has Flashback Database. Flashback Query and Flashback Table (but not dropped table) utilize UNDO.

  • Read consistency:  If you start a query at 9AM and it takes 3 hours, are the results you see from 9AM or noon? The answer is 9AM, the moment you pressed the enter key. Oracle accomplishes this through read consistency, which pulls the data you require from current data blocks or consistent read blocks, which is UNDO. If you do not have enough UNDO to satisfy your long running queries, you might get a ORA-01555 error.

  • Transaction Recovery: If your instance is abnormally terminated (kill -9 pmon, shutdown abort), SMON will handle recovery on instance startup. This involves four phases: mount the database, roll forward all redo since the last checkpoint to the data-files, open database, and rollback all non-committed transactions.
Hence, if the UNDO tablespace has a high undo_retention then it will eat up all of your disk space in the UNDO tablespace. I suggest that you take a look at your UNDO adviser within OEM and see what it recommends for an UNDO table-space size for your database.
There are some views that show information related to UNDO activity:
  • V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
  • V$TRANSACTION: present time view providing information on current transactions.
  • V$SESSTAT: individual session statistics, which includes one for undo usage.
You can query the v$undostat and the v$transaction view to see the actual usage of your UNDO table-space.

You can also run this query to see UNDO usage:

select
   a.sid,
   b.name,
   a.value

from
   v$sesstat  a,
   v$statname b

where
   a.statistic# = b.statistic#

and
   a.statistic# = 176
order by
   a.value DESC;


We also have this query to measure growing UNDO tablespace usage:

colusername FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;


In a nutshell, the undo_retention parameter is used to throttle a growing UNDO tablespace.

Managing the UNDO TABLESPACE

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features
Earlier releases of Oracle Database used rollback segments to store undo. Oracle 9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.

Switching to Automatic Management of Undo Space

To go for automatic management of undo space set the following parameter.

Steps:-

1    If you have not created an undo tablespace at the time of creating a database then, create an undo tablespace by typing the following command

SQL>create undo tablespace myundo datafile
         ‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
                        autoextend ON next 5M ;

When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed

2.    Shutdown the Database and set the following parameters in parameter file.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

3.    Start the Database.
Now Oracle Database will use Automatic Undo Space Management.

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)
As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;



Altering UNDO Tablespace

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it

The following example extends an existing datafile

SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M

The following example adds a new datafile to undo tablespace

SQL> ALTER TABLESPACE myundo ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE 200M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

SQL> DROP TABLESPACE myundo;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;

Assuming myundo is the current undo tablespace, after this command successfully executes, the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace

To view statistics for tuning undo tablespace query the following dictionary

SQL>select * from v$undostat;

To see how many active Transactions are there and to see undo segment information give the following command

SQL>select * from v$transaction;

To see the sizes of extents in the undo tablespace give the following query

SQL>select * from DBA_UNDO_EXTENTS;

Friday, 14 April 2017

ORA-28

I am getting the following ORA-28 error in my alert log:
**********************************************************************
Following errors written to the Alert log file. Please verify
ORA-28 : opiodr aborting process unknown ospid (18941_47665083519440)

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

ORA 28:  Your session has been killed.

Cause:

 A privileged user killed the session and it is no longer logged in to the database.

Action: 

Contact the DBA.   The administrator may be attempting to perform an operation that requires users to be logged out.  When the database administrator announces that the database is available, log in and resume work.
Here is a breakdown of the sample ORA-28 message:

      "opiodr aborting process unknown ospid (24380) as a result of ORA-28"
  • "unknown" => means it is not a background or shadow process that is killed.
  • "ospid (24380)" => this is the OS pid of the process which opiodr is aborting.
  • "as a result of" => this precedes the error message which is the reason for opiodr to kill the process.
  • "ORA-28" => this is the reason that opiodr killed the process. In this case, it is ora-28, that the session has been killed by Oracle.
On MOSC see "opiodr aborting process" Messages in Alert.Log [ID 1230858.1].  Also note this bug on the ORA-28 error, bug 6377339 titled "Misleading alert log message for "opiodr aborting" [ID 6377339.8]"

The solution for the ORA-28 error message may be to apply patch ID 14737386

How to create read-only user for oracle schema

User hrqa_ro will be globally read only user.

SQL> conn / as sysdba
SQL> create user hrqa_ro identified by hrqa_ro123;
User created.

SQL> grant create session,select any dictionary,select any table to hrqa_ro;
Grant succeeded.

*************************************************************************
Question:  I need to create a read-only user within my schema.  
How can you grant read-only access for a single user without granting read to every table?

Answer:  You can make any user read-only with the grant select any table privilege:

SQL> connect scott/tiger

SQL>create user scott_read_only_user identified by readonly;

SQL>grant create session to scott_read_only_user;

SQL>grant select any table to scott_read_only_user;

This will only grant read-only to scott tables, you would need to connect to another schema owner
to grant them read-only access.  Optionally, you can add read-only dictionary acces:

SQL>grant select any dictionary to scott_read_only_user;

**************************************************************************
[oracle@testsrv]$ sqlplus / as sysdba

SQL> create user juser_read_only identified by test;
SQL> grant create session to user_read_only;
SQL> grant select any table to user_read_only;

Once you have granted these privileges you can connect as the new user.

SQL> conn user_ready_only/test
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

If you wish to grant select on dictionary views then:

SQL> conn / as sysdba
SQL> grant select any dictionary to user_read_only;
If you wish the read_only user could select ddl of any objects belongs to any schema then:

SQL> grant SELECT_CATALOG_ROLE to user_read_only;
SQL> conn user_read_only

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c

Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB)
 in Oracle Database 12c Release 1 (12.1)
The multitenant option introduced in Oracle Database 12c allows a single container database (CDB)
to host multiple separate pluggable databases (PDB).

Container Database (CDB)

Startup and shutdown of the container database is the same as it has always been for regular instances.
The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user.

Some typical values are shown below.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

Pluggable Database (PDB)

Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

SQL*Plus Commands

The following SQL*Plus commands are available to start and stop a pluggable database, when connected to
that pluggable database as a privileged user.
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
Some examples are shown below.

STARTUP FORCE;
SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;
SHUTDOWN;

STARTUP;
SHUTDOWN IMMEDIATE;
ALTER PLUGGABLE DATABASE

The ALTER PLUGGABLE DATABASE command can be used from the CDB or the PDB.

The following commands are available to open and close the current PDB when
connected to the PDB as a privileged user.

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
Some examples are shown below.

ALTER PLUGGABLE DATABASE OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
The following commands are available to open and close one or more PDBs when connected to the CDB as a privileged user.

ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
The <pdb-name-clause> 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 pdb1, pdb2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE IMMEDIATE;

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

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;
Pluggable Database (PDB) Automatic Startup

The 12.1.0.2 patchset has introduced the ability to preserve the startup state of PDBs,
 so you probably shouldn't be implementing a trigger in the manner discussed in this section.

Prior to 12.1.0.2, when the CDB is started, all PDBs remain in mounted mode.
There is no default mechanism to automatically start them when the CDB is started.
The way to achieve this is to use a system trigger on the CDB to start some or all of the PDBs.

CREATE OR REPLACE TRIGGER open_pdbs
  AFTER STARTUP ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_pdbs;
/

You can customise the trigger if you don't want all of your PDBs to start.

Preserve PDB Startup State (12.1.0.2 onward)

The 12.1.0.2 patchset introduced the ability to preserve the startup state of PDBs through a CDB restart. 

This is done using the ALTER PLUGGABLE DATABASE command.

We will start off by looking at the normal result of a CDB restart.
Notice the PDBs are in READ WRITE mode before the restart, but in MOUNTED mode after it.

SQL>  SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

SQL>


SHUTDOWN IMMEDIATE;
STARTUP;


SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED

SQL>
Next, we open both pluggable databases, but only save the state of PDB1.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
SQL> ALTER PLUGGABLE DATABASE pdb2 OPEN;
SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

The DBA_PDB_SAVED_STATES view displays information about the saved state of containers.

SQL> COLUMN con_name FORMAT A20
SQL> COLUMN instance_name FORMAT A20

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

CON_NAME             INSTANCE_NAME        STATE
-------------------- -------------------- --------------
PDB1                 cdb1                 OPEN

SQL>
Restarting the CDB now gives us a different result.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE




SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;


SQL> SELECT name, open_mode FROM v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED


The saved state can be discarded using the following statement.

SQL> ALTER PLUGGABLE DATABASE pdb1 DISCARD STATE;

SQL> COLUMN con_name FORMAT A20
SQL> COLUMN instance_name FORMAT A20

SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

no rows selected


To connect to a PDB using the SQL*Plus CONNECT command:

Configure your environment so that you can open SQL*Plus.


Start SQL*Plus with the /NOLOG argument:

[oracle@sujeet ~]$ sqlplus /nolog
Issue a CONNECT command using easy connect or a net service name to connect to the PDB.

To connect to a PDB, connect to a service with a PDB property.

Connecting to a PDB in SQL*Plus Using the PDB's Net Service Name

The following command connects to the hr user using the PDB service.
The PDB service has a PDB property for the hrpdb PDB. This example assumes that the client is
configured to have a net service name for the hrapp service.

[oracle@sujeet ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 14 08:51:55 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> CONNECT hr@PDB
Enter password:
Connected.


[oracle@sujeet~]$ sqlplus /nolog
SQL> CONNECT sonu@PDB
Enter password:
Connected.

SQL> show user
USER is "sonu"


SQL> SELECT name, open_mode FROM v$pdbs;
NAME                           OPEN_MODE
------------------------------ ----------
PDB                        READ WRITE

SQL> CONNECT system@PDB
Enter password:
Connected.


Here is a brief list of some of the usage notes explained in the documentation.

The state is only saved and visible in the DBA_PDB_SAVED_STATES view if the container is in READ ONLY or READ WRITE mode.
The ALTER PLUGGABLE DATABASE ... SAVE STATE command does not error when run against a container in MOUNTED mode,
but nothing is recorded, as this is the default state after a CDB restart.
Like other examples of the ALTER PLUGGABLE DATABASE command, PDBs can be identified individually,
 as a comma separated list,
 using the ALL or ALL EXCEPT keywords.
The INSTANCES clause can be added when used in RAC environments. The clause can identify instances individually,
 as a comma separated list, using the ALL or ALL EXCEPT keywords. Regardless of the INSTANCES clause,
 the SAVE/DISCARD STATE commands only affect the current instance.


How to Stop and Start a Pluggable Database

The following explains how to stop and start a the container database and the containers (pluggable databases).

1. Shutdown a container database (CDB) and all pluggable databases (PDBs)

sqlplus '/ as sysdba'
SQL> show connection
NB Make sure you are on the root CDB$ROOT
SQL> shutdown immediate
2. Startup the CDB

sqlplus '/ as sysdba'
SQL> startup
Note: When you start a CDB it does not automatically start the PDBs

3. Check the status of the PDBs

sqlplus '/ as sysdba'
SQL> select name, open_mode from v$pdbs;
Note: Any PDBs are in mounted status.

4. Start a PDB

sqlplus '/ as sysbda'
SQL> alter pluggable database myplugdb3 open;
NB This will open pluggable database myplugdb3.
SQL> alter pluggable database all open;
NB This will open all pluggable databases.
5. Stop a PDB

sqlplus '/ as sysdba'
SQL> alter pluggable database myplugdb3 close immediate;
NB This will close pluggable database myplugdb3
SQL> alter pluggable database all close immediate;
NB This will close all pluggable databases
6. Using a trigger to open all pluggable databases.

sqlplus '/ as sysdba'
SQL> CREATE OR REPLACE TRIGGER pdb_startup AFTER STARTUP ON DATABASE
SQL> BEGIN
SQL> EXECUTE IMMEDIATE 'alter pluggable database all open';
SQL> END pdb_startup;
SQL> /


Start and Shutdown Pluggable Database

I will tell about start and shutdown pluggable database in this my post.This is as you know that tradational
start and shutdown process. But there is a little difference when you use container database.
If you have container database which have pluggable database and when you start container database at the same
time you should all pluggable database manually after starting container database. Let’s look this process.

Start and Shutdown Pluggable Database: This operation can be made in two ways.
First one is you can start or shutdown direct from inside pluggable database.
The second one is “alter pluggable database” command from root container.
You know first way. You set container parameter and do it what you want.

1)You can start or shutdown direct from inside pluggable database.

SQL> alter session set container=PDB2;
Session altered.

SQL> shutdown immediate;
Pluggable Database closed.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED

You can start with same way.

SQL> startup
Pluggable Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

2) The another way you can use “alter pluggable database” command from root container to start and
shutdown pluggable database.You connect to container database.

SQL> alter pluggable database PDB1 close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           READ WRITE
You can start pluggable database with same way.

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

Maybe you have a lot of pluggable database in the container database and these shutdown operation would
be disturbed.We can shutdown all pluggable database with one command from root container.

SQL> alter pluggable database all close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
To open all PDB’s

SQL> alter pluggable database all open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

You may want close all pluggable database except one pluggable database. You can do this except command as following.

SQL> alter pluggable database all except PDB2 close;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           READ WRITE

When you open all pluggable database you can do same thing  “open” command  instead “close” command .

Or you can specify pdb list to perform operation.

SQL> alter pluggable database pdb1,pdb2 close;
Pluggable database altered.

NOTE:When you shutdown container database all PDB will shutdown too. But when you start container
database any PDB is not start automaticly.
To start PDB we should do manually intervention or we can create a trigger as following.

SQL> CREATE OR REPLACE TRIGGER open_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END open_pdbs;
/