Oraconsultancy - Your Multi Cloud Partner

Tuesday, 19 November 2013

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.

Monday, 15 July 2013

APEX LOGIN

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

Monday, 20 May 2013

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');

Thursday, 3 January 2013

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.

Friday, 28 December 2012

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

knudwdbs1> rman auxiliary username/password@TNSALIAS

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 28 15:52:05 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

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

checking Listener Service using lsnrctl status, also shows All the service with instance with status 'BLOCKED'.

lsnrctl status
===============

Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DBSiteA.qvcuk.local" has 1 instance(s).
Instance "DBUAT1", status BLOCKED, has 1 handler(s) for this service...
Service "standby.qvcuk.local" has 1 instance(s).
Instance "DBUAT1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

Solution

This is because instance is STARTED status, To fix the update the listener.ora file with static registration for the instance.

-------- listner.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBUAT_SiteA)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1)
(SID_NAME = DBUAT1)
)
)

lsnrctl reload
lsnrctl status

Service "DBUAT_SiteA" has 1 instance(s).
Instance "DBUAT1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Reload listener and check status, as its status registration , Service DBUAT_siteA with instance in unknown status added to listener.

Once the status is UNKNOWN for the instance you should be able to connect.

Tuesday, 2 October 2012

NFS Mount Issue ORA-27054

Error/Issue:

Database unable to use/create file on NFS mount point faild with following Error:
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 5
Tue Oct 02 11:20:42 2012
WARNING: NFS file system /oracle_mnt not mounted with correct options
WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,
WARNING: NFS file system /oracle_mnt not mounted with correct options
WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,
WARNING: NFS file system /oracle_mnt not mounted with correct options
WARNING:Expected NFS mount options: rsize>=32768,wsize>=32768,hard,
ORA-205 signalled during: ALTER DATABASE MOUNT...

Problem:

In the above case , NFS was mounted with default rsize and wsize option and oracle doesn't like any NFS mount write/read size values (wsize) less than 32k.

AIX /etc/filesystems etntry :

/oracle_mnt:

dev = /DBA

mount = true

vfs = nfs

nodename = server_name or IP

options = rw,sort

type = nfs_mount

which is clearly written to alert.log file with Warning message.

Solution :

Mount the NFS with correct option , high lighted in the Warning message. Entry updated to following fixed the problem.