12c Database is it any different when Install ????

Hi All,

12c database is out now from many months and today thought to install the 12c database , just to see how different it look from installation point of view but observed the following so far.

* Installation is exactly same as 11g (only Logo changed to 12c) , Last page got option of clicking Edit to change any configuration you don't like (new )
* New OS group introduced , to make DBA's life more complicated until we all understand what to use them for.
* QOPatch directory scared me, thinking patching i have to learn again from scratch but no Patching is same as before ( Same OPath, here you need 12.0.1.x.x version) , still need to figure out what this QOPatch is used for.


I know there is Long list of changes(New Features) in 12c specially Plugable database etc, i will be reading them and will share with everyone.

Operating system user Groups in 12c Plugable database

Oracle 11g  we saw new OS group called SYSASM , now to add more flexibility (and also complexity ) Oracle introduced few moreOS group for Oracle OS user when working with 12C database.

New Groups for 12c database Installation : OSBACKUPDBA, OSDGDBA , OSKMDBA

The OSDBA group (typically, dba) 

You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege). The name used for this group in Oracle code examples is dba.

The OSOPER group for Oracle Database (typically, oper) 

This is an optional group. Create this group if you want a separate group of operating system users to have a limited set of database administrative privileges (the SYSOPER privilege). This group cannot directly connect as SYSOPER, unless explicitly granted. However, they have the privileges granted by the SYSOPER privilege. By default, members of the OSDBA group have all privileges granted by the SYSOPER privilege.

The usual name for this group is oper.

The OSBACKUPDBA group for Oracle Database (typically, backupdba) 

Create this group if you want a separate group of operating system users to have a limited set of database backup and recovery related administrative privileges (the SYSBACKUP privilege). The usual name for this group is backupdba.

The OSDGDBA group for Oracle Data Guard (typically, dgdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges to administer and monitor Oracle Data Guard (the SYSDG privilege). The usual name for this group is dgdba.

The OSKMDBA group for encyption key management (typically, kmdba) 

Create this group if you want a separate group of operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management (the SYSKM privilege). The usual name for this group is kmdba.

APEX LOGIN

http://apex.oracle.com/pls/apex/f?p=6368811

How to Flush Single SQL Plan out of Shared Pool

select ADDRESS, HASH_VALUE from V$sqlarea where SQL_ID ='sqlid';



BEGIN
DBMS_SHARED_POOL.PURGE('C0000008F304C620',2219163920,'C');
END;
/



Set linesize 120

select 'on Inst '||inst_id||'   execute DBMS_SHARED_POOL.PURGE('||''''||ADDRESS||','|| HASH_VALUE ||''''||','||''''||'C'||''''||');' "Execute Below to flush PLAN" from GV$SQLAREA where SQL_ID like 'adpk6gy24bh8h'
/

Execute Below to flush PLAN
----------------------------------------------------------------------------------
on Inst 1   execute DBMS_SHARED_POOL.PURGE('C0000008F304C620,2219163920','C');
on Inst 2   execute DBMS_SHARED_POOL.PURGE('C0000008E71CE550,2219163920','C');

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

SQL> drop tablespace  TBS_EMPTY including contents and datafiles;
drop tablespace  TBS_EMPTY including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

When checked dba_segments there isn't any segment in the tablespaces, then tried clearing the recylebin to ensure all object clearebut that also didn't help. Finally Find out Purge recylebin using Sys user doesn't actually clear anything, neither error out,

You have to use purge dba_recyclebin to clear the contents.


select count(1) from dba_segments where tablespace_name='TBS_EMPTY';

 COUNT(1)

----------
         0

 SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
group by ts.name
/
Tablespace Name                  COUNT(1)
------------------------------ ----------
TBS_EMPTY                             56
TABS001      23
............
..............
...................


SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
group by ts.name
/
Tablespace Name                  COUNT(1)
------------------------------ ----------
TBS_EMPTY                             56              <=== still same
TABS001      23
............
..............
...................

Solution :

If you want to clean the recyclebin for all users.

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

OR

if you don't want to purge the recyclebin for all users, use tablespace clause.

SQL > purge TABLESPACE  tablespace_name  ;



SELECT ts.name "Tablespace Name", count(1) from recyclebin$ rs, ts$ ts
where ts.ts#=rs.ts#
 group by ts.name
  /
no rows selected

Once recycle bin is empty , Tablespace can be dropped.

SQL> drop tablespace  TBS_EMPTY including contents and datafiles;
Tablespace dropped.