Privileges Required for the DBMS_REDEFINITION Package

Sometimes  Oracle Developers face Ora-1031 error for insufficient privileges and execute privileges on dbms_redefinition is already given to them, So what is missing ??  Oracle Developers need to redefine the table online in oracle and its not possible to give them DBA privileges straight away, In this case only specific privileges should be given which are mentioned below :-

[node4.example.com ]$ oerr ora 1031

01031, 00000, "insufficient privileges"

// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.

// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

Privileges Required for the DBMS_REDEFINITION Package

Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

CREATE ANY TRIGGER
CREATE ANY INDEX

Session specific Optimizer Environment in Oracle

Yesterday there was a need for me to look into what optimizer parameters are set for my particular session only not for the entire database, so below view with the query was really helpful. Try it :-

SQL> select NAME,value  from V$SES_OPTIMIZER_ENV where lower(name) like '%statistics%' and SID=5363;

NAME                                     VALUE
---------------------------------------- -------------------------
statistics_level                         typical
optimizer_use_pending_statistics         false


As per Oracle Doc V$SES_OPTIMIZER_ENV displays the contents of the optimizer environment used by each session. When a new session is first created, it automatically inherits its optimizer environment from the optimizer environment defined at the instance level by V$SYS_OPTIMIZER_ENV. The value of certain parameters can be dynamically modified by issuing an ALTER SESSION statement.

LNS wait on SENDREQ Wait Event

LNS wait on SENDREQ means that redo data has not been written to all ASYNC and SYNC redo transport destinations and that the processes on primary databases are waiting. This is basically a network related wait event which means that tuning the network may be beneficial to remove this wait event. The LNS wait on SENDREQ can happen with either the SYNC or ASYNC LGWR attributes.  When using ASYNC transport mode in Oracle 10g r2 and beyond, Oracle recommends allowing for sufficient I/O bandwidth for LNS read I/Os to the online redo logs of the production database.

As per Oracle Doc LNS wait on SENDREQ means :- Total time spent waiting for redo data to be written to all ASYNC and SYNC redo transport destinations.

There can be multiple ways to tune it :-

1) The very first way to tune is to optimize the network, You can set SDU (SESSION DATA UNIT) parameter within an Oracle Net connect descriptor or globally within the sqlnet.ora. For Data Guard broker configurations configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:

DEFAULT_SDU_SIZE=32767

2) Tuning tNS layer for the tcp.nodelay parameter in the protocol.ora file

3) Get a faster network transport such as dark fibre.

4) Change the size of the online redo logs to make the archived redo log smaller, thereby creating smaller archived redo logs.  This will create more frequent, smaller redo log transports that will complete faster.

Monitoring Synchronous Redo Transport Response Time

The V$REDO_DEST_RESP_HISTOGRAM view contains response time data for each redo transport destination. This response time data is maintained for redo transport messages sent via the synchronous redo transport mode.

The data for each destination consists of a series of rows, with one row for each response time. To simplify record keeping, response times are rounded up to the nearest whole second for response times less than 300 seconds. Response times greater than 300 seconds are round up to 600, 1200, 2400, 4800, or 9600 seconds.

Each row contains four columns: FREQUENCY, DURATION, DEST_ID, and TIME.

The FREQUENCY column contains the number of times that a given response time has been observed. The DURATION column corresponds to the response time. The DEST_ID column identifies the destination. The TIME column contains a timestamp taken when the row was last updated.

The response time data in this view is useful for identifying synchronous redo transport mode performance issues that can affect transaction throughput on a redo source database. It is also useful for tuning the NET_TIMEOUT attribute.

Wait Event "resmgr:cpu quantum"

Wait Event "resmgr:cpu quantum"

The wait event resmgr:cpu quantum is a normal wait event used by the Oracle Resource Manager to control CPU distribution. The resmgr:cpu quantum only occurs when the resource manager is enabled and the resource manage is "throttling" CPU consumption.

You can also detect an overloaded CPU when you see the “resmgr:cpu quantum” event in a top-5 timed event on a AWR or STATSPACK report.

The "resmgr:cpu quantum" only applies when the Oracle resource manager is deployed, and there are other ways to detect an overloaded CPU:

1. - UNIX/Linux - vmstat when the runqueue column (r) exceed the cpu_count for the database.

2. - Windows:  When the processor queue length is greater than zero.

If there 100% CPU Utilization then its important to check Processor Queue Length in the system monitor and task manager. In Windows, it's the "Processor Queue Length", and it's displayed in the system monitor and task manager.

Microsoft notes:

"Processor Queue Length (System) This is the instantaneous length of the processor queue in units of threads. All processors use a single queue in which threads wait for processor cycles.

After a processor is available for a thread waiting in the processor queue, the thread can be switched onto a processor for execution. A processor can execute only a single thread at a time. Note that faster CPUs can handle longer queue lengths than slower CPUs."

"The number of threads in the processor queue. Shows ready threads only, not threads that are running. Even multiprocessor computers have a single queue for processor time; thus, for multiprocessors, you need to divide this value by the number of processors servicing the workload. A sustained processor queue of less than two threads per processor is normally acceptable, depending upon the workload."

Webinar of Oracle Goldengate Architecture with Setup (Karan Dodwal)

Recently  i gave a webinar on Oracle Goldengate Architecture with Setup (Karan Dodwal) and thanks for the audience to attend it and yes the webinar is available at youtube the link is Goldengate_Webinar , the reason for putting it on youtube is that everyone in todays's world watches youtube so why not learn from youtube, so click here  for the link.

Thanks everyone,
Happy Learning
Karan Dodwal (OCM)

Webinar of Installing Oracle Linux Server

Recently  i gave a Webinar of Installing Oracle Linux Server (Karan Dodwal) and thanks for the audience to attend it and yes the webinar is available at youtube the link is Install_Linux_Oracle the reason for putting it on youtube is that everyone in todays's world watches youtube so why not learn from youtube, so click here  for the link.

Thanks everyone,
Happy Learning
Karan Dodwal (OCM