Oracle in World: 10/12/08

Saturday, October 18, 2008

How to generate fibonacci series in Oracle.

Way 1:


with data as (select level levels from dual
connect by level <= &how_may_rows) 
select f from data 
model dimension by (levels) 
measures ( 0 f) 
rules (   f[1] = 0 , f[2] = 1 , f[levels>2]=f[cv(levels)-2]+f[cv(levels)-1]
);
Enter value for how_may_rows: 10
old   2:                       connect by level <= &how_may_rows)
new   2:                       connect by level <= 10)

F
----------
0
1
1
2
3
5
8
13
21
34

10 rows selected.

Way 2: Just a variant of way 1,

SQL> select s seq from dual
model return all rows
dimension by ( 0 d ) measures ( 0 s )
rules iterate (&n) (
s[iteration_number ] = decode(
iteration_number, 0, 0, 1, 1, s[iteration_number-2]
) + nvl(s[iteration_number-1],0)
)
/
Enter value for n: 8
old   4:     rules iterate (&n) (
new   4:     rules iterate (8) (

SEQ
----------
0
1
1
2
3
5
8
13

8 rows selected.

Way 3: Using Math Formula

SQL> select round ((power ((1 + sqrt (5)) / 2, level - 1) - power ((1 - sqrt (5)) / 2, level - 1)) / sqrt (5)) fib
from dual
connect by level <=&n;
Enter value for n: 8
old   3: connect by level <=&n
new   3: connect by level <=8

FIB
----------
0
1
1
2
3
5
8
13

8 rows selected.

Thursday, October 16, 2008

RMAN Incremental database backup in Oracle

I will demonstrate RMAN incremental database backup in this post.
My requirement is as follows.

1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode.

2)I will use flash recovery area to take backup for better management of backup data.

3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative.

4)As a repository I will use recovery catalog in order to store backup information.

5)I need to setup rman configuration like,
-autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) ,

-backup optimization on (in order to skip logs that it has already backed up to the specified device). and,

-recovery window to 7 days. (to ensure that you can recover the database to any point within the last week)

6)My recovery catalog database name is neptune, listener port 1522 and serice name ARJU.NEPTUNE.ARJUBD.COM.

Below is the step by step solution.

Step 01: Connect to source database as sysdba.
oracle@neptune ~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:22:04 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Step 02: See the log mode of database.
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

Step 03: Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 04: Start the database in mount state.
SQL> startup mount
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 109055528 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.

Step 05: Enable archive log.
SQL> alter database archivelog;
Database altered.

Step 06: Open the database.
SQL> alter database open;
Database altered.

Step 07: Check the archived destination and flash recovery area size and location.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/app/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 50G
If you see after giving archive log list Archive destination is not shown as USE_DB_RECOVERY_FILE_DEST then set it by,
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Step 08: Connect to rman of source database.
SQL> host rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:24:16 2008

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

connected to target database: ARJU (DBID=259530685)

Step 09: Change some configuration settings of source database.
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

Step 10: Connect to the recovery catalog database.
oracle@neptune ~$ sqlplus sys/sistem@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:35:21 2008

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Step 11: Create recovery catalog user and tablespace and assign permission.
SQL> create user rman identified by rman;

User created.

SQL> create tablespace catalog_spc datafile '/oradata1/catalog.dbf' size 10M autoextend on maxsize unlimited;

Tablespace created.

SQL> alter user rman default tablespace catalog_spc temporary tablespace temp quota unlimited on catalog_spc;

User altered.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

Step 12: Connect to recovery catalog database.
bash-3.00$ rman catalog rman/rman@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:39:43 2008

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

connected to recovery catalog database

Step 13: Create recovery catalog schema objects.
RMAN> create catalog tablespace catalog_spc;

recovery catalog created

Step 14: Be sure whether recovery catalog objects successfully created.
SQL> select table_name from tabs;

Step 15: Connect to rman on source and recovery catalog database.
bash-3.00$ rman target / catalog rman/rman@neptune:1522/ARJU.NEPTUNE.ARJUBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:41:53 2008

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

connected to target database: ARJU (DBID=259530685)
connected to recovery catalog database

Step 16: Register database with recovery catalog.
RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Step 17: Check whether registration was successful.
RMAN> report schema;

If this return database structure then registration is successful.

Step 18: Create scripts for incremental database backups.
This script is for full database backup level 0. This one will be run on weekly basis.
CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
This script is for incremental cumulative database backup level 0. This one will be run on daily basis.
CREATE GLOBAL SCRIPT global_incr_backup
{
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

This one is for RMAN incrementally updated backups. I just made this and will not use it. For huge database we may consider it.
CREATE GLOBAL SCRIPT global_incr_updated_backup
{
RECOVER COPY OF DATABASE WITH TAG 'inc_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'inc_update'
DATABASE;
}

A sample output after creating global_full_backup,
RMAN> CREATE GLOBAL SCRIPT global_full_backup;
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

starting full resync of recovery catalog
full resync complete
created global script global_full_backup

Step 19: Run these scripts and take backups.
Weekly,
RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_full_backup;
}

Daily,
RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_incr_backup;
}

You may consider also inside script like,
RMAN> backup database plus archivelog delete all input;
in order to delete archived log from all locations that already taken backup.

Or, you may issue,
RMAN> delete archivelog all backed up 1 times to disk;
in order to delete archive log that have at least 1 times backed up to disk.
Related Documents
What will be the Archived Redo Log Destination in Oracle

Archive Destination Settings fails with ORA-32017 and ORA-16179
Backup Database in Noarchivelog mode

how to take backup of oracle database from remote system How to exclude a tablespace from whole database backup User Managed hot backup of oracle database

Tuesday, October 14, 2008

Same sounded words in oracle

Monday, October 13, 2008

ORA-28547 connection to server failed, probable Net8 admin error

Error Description
This problem occurs on windows machine. Whenever you login as a user of windows domain group then in the database all connections both local and remote, including SYSDBA, fail with an ORA-28547.

ORA-28547 connection to server failed, probable Net8 admin error

Cause of the Problem
Oracle Native Authentication Services is failing.

Solution of the Problem
Way 1:
Disable Oracle Native Authentication Services. In the server machine inside sqlnet.ora change the parameter value of SQLNET.AUTHENTICATION_SERVICES from (NTS) to (NONE).

Way 2:
Change the database service to run as a user who is a member of the Domain Admin group. Because in order to connect using native authentication service oracle Log on user must be a privileged user to allow it to correctly authenticate the client user for Native Authentication.

To do so,
1.Go to Services. Right click on My computer>Select Manage>Select Services and Application> Select Services> On the right side From a list Scroll to your OracleService$ORACLE_SID
You will search for your database name instead of $ORACLE_SID of OracleService$ORACLE_SID.

2. Right click on the service and click the properties of the database service, select the LogOn tab.

3. Select "This Account" and add you domain admin account details and Ok

4. Shutdown the database

5. Stop and restart the service

Automatic startup and shutdown oracle on linux

Oracle database server provides two scripts to configure automatic database startup and shutdown process.

The scripts are,
$ORACLE_HOME/bin/dbstart
$ORACLE_HOME/bin/dbshut

Now let's look at unix level script. When a unix machine boots it runs scripts beginning with Snnname in /etc/rc3.d.

-Here the number nn indicates the order in which these scripts will be run. The name just indicates the function of the script.

In the same way shutdown scripts are named as Knnname which are run from /etc/rc0.d.

If we want that Oracle is the last program that is automatically started, and it is the first to be shutdown then we will name the startup and shutdown scripts on OS like /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

The database script dbstart and dbora will be called from OS script /etc/rc3.d/S99oracle and /etc/rc0.d/K01oracle respectively.

Note that dbstart and dbshut take each SID, in turn, from the /etc/oratab file and
startup or shutdown the database.

Automate Startup/Shutdown of Oracle Database on Linux
Step 01: Be sure that oratab file is correct and complete.
Check for oratab file either in /etc/oratab or in /var/opt/oracle/oratab.
Database entries in the oratab file have the following format:
$ORACLE_SID:$ORACLE_HOME:[Y|N]
Here Y indicates that the database can be started up and shutdown using dbstart/dbshut script.

If in my database there is two database named arju and arjudup then my oratab file will contain the entry like,
arju:/var/opt/oracle/product/10.2.0/db_1:Y
arjudup:/var/opt/oracle/product/10.2.0/db_1:Y
where /var/opt/oracle/product/10.2.0/db_1 is the $ORACLE_HOME of my database.

Step 02: Create a script to call dbstart and dbshut.
In this example I will create one script that will do both startup and shutdown operation. I will name this script as dbora and will be placed in '/etc/init.d'.

a) Login as root.
b) Change directories to /etc/init.d
c) Create a file called dbora and chmod it to 750.

# touch dbora
# chmod 750 dbora
d)Edit the dbora file and make the contents of it like below.

#!/bin/bash
#
# chkconfig: 35 99 10   
# description: Starts and stops Oracle processes
#
ORA_HOME=/var/opt/oracle/product/10.2.0/db_1
ORA_OWNER=oracle

case "$1" in
'start')

# Start the TNS Listener
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
# Start the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
# Start the Intelligent Agent
if [ -f $ORA_HOME/bin/emctl ];
then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start agent"
elif [ -f $ORA_HOME/bin/agentctl ]; then

su - $ORA_OWNER -c "$ORA_HOME/bin/agentctl start"
else
su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl dbsnmp_start"
fi
# Start Management Server
if [ -f $ORA_HOME/bin/emctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/emctl start dbconsole"
elif [ -f $ORA_HOME/bin/oemctl ]; then
su - $ORA_OWNER -c "$ORA_HOME/bin/oemctl start oms"
fi
# Start HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl]; then
su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl start"
fi
touch /var/lock/subsys/dbora
;;
'stop')
# Stop HTTP Server
if [ -f $ORA_HOME/Apache/Apache/bin/apachectl ]; then

su - $ORA_OWNER -c "$ORA_HOME/Apache/Apache/bin/apachectl stop"
fi
# Stop the TNS Listener

su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
# Stop the Oracle databases:
su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
rm -f /var/lock/subsys/dbora
;;
esac
# End of script dbora

3.As root perform the following to create symbolic links:

# ln -s /etc/init.d/dbora /etc/rc3.d/S99oracle
# ln -s /etc/init.d/dbora /etc/rc0.d/K01oracle

Alternatively you can register the Service using
/sbin/chkconfig --add dbora

This action registers the service to the Linux service mechanism.

4. Test the script to see if it works.

The real test is to reboot unix box and then see whether oracle is started up automatically or not.

However to test the script created in step 2, without rebooting, do the following:

Login as root and then,
# /etc/init.d/dbora start (for startup)
# /etc/init.d/dbora stop (for shutdown)

If you restart start and stop oracle database is successful then you are almost done.

Related Documents
How to Find out or Check Linux Version Information
How to change the hostname in Linux
How to Identify OS or Oracle 64 bit or 32 bit on Unix

Backup to remote location fails with ORA-19504 andORA-27054

Error Description
I mounted remote location jupiter:/export/home/oracle on my local server as /export/home/oracle/remote and then I wanted to take RMAN backup on the mapped drive but it fails with ORA-19504: and ORA-27054:.
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=136 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata1/arju/ARJU/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/13/2008 04:02:40
ORA-19504: failed to create file "/export/home/oracle/remote/03jt0tav_1_1"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
Cause of the Problem
From Oracle 10.2g , Oracle checks by which options NFS mount is mounted on local filesystem. This checking is done to ensure that no corruption of the database can happen as incorrectly mounted NFS volumes can result in data corruption.

Generally the following checks are done on Solaris and AIX systems.

1) In order to check the mount option whether mount table can be read. Issue,
# cat /etc/mnttab

2) While NFS mount is "hard" option provided.
3) Whether the mount options include rsize>=32768 and wsize>=32768
4) If RAC environments, where NFS disks are supported, whether the "noac" mount option is used.

If above requirements are full and still above error appear then possibly oracle bug.
Solution of the Problem
Solution 1: Setting the mount option explicitly.
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote

The default rsize and wsize for NFS is 4096 Blocks

So if you have rsize=32k and wsize=32k then NFS would be able to read and write large datagram as
compared to deafult one.

TCP option will make sure that your client are getting the data or not

Hard & INTR - The program accessing a file on a NFS mounted file system will hang when the
server crashes. The process cannot be interrupted or killed unless you also specify intr. When
the NFS server is back online the program will continue undisturbed from where it was.

actimeo is for access timeout and it should be 0 .

After setting above parameters in NFS mount clause you will be able to take the backup
RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;
And see it would be successful.

Solution 2: Set the Event 10298
SQL> alter system set event="10298 trace name context forever, level 32" scope=spfile;
System altered.

Now restart server and take backup.
SQL> startup force
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 125832744 bytes
Database Buffers 100663296 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string 10298 trace name context forev
er, level 32

RMAN> backup format '/export/home/oracle/remote/%U' datafile 4;

Starting backup at 13-OCT-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oradata2/arjudba/arjudba/arju/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-08
channel ORA_DISK_1: finished piece 1 at 13-OCT-08
piece handle=/export/home/oracle/remote/08jt0voi_1_1 tag=TAG20081013T044402 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-OCT-08

Solution 3: If Oracle bug
If still fails then possibly an oracle bug for Oracle 10.2.0.1. In order to solve bug apply 5146667 one off patch for 10.2.0.1.

Related Documents
How to take RMAN backup on a remote disk

How to share and Access file on Solaris machine

In this example I will share a file from jupiter machine and then access it on neptune machine. Both machine uses Solaris platform.
1)On jupiter machine,

bash-3.00$ hostname
jupiter

Log on as a root user,
bash-3.00$ su
Password:

Change shell to bash
# bash

To make share persistence (after reboot is will also show) edit /etc/dfs/dfstab entry. Here I want to share directory /export/home/oracle and sharing option is read write.
# vi /etc/dfs/dfstab
share -F nfs -o rw /export/home/oracle

rw means read write permission You can also want to give ro (read only) instead of read write.

If you want it temporary you can do,
# share -F nfs -o rw /export/home/oracle
Restart your nfs server.
# /etc/init.d/nfs.server stop
# /etc/init.d/nfs.server start

Have a look at whether nfs server is running or not.
# ps -ef | grep nfs
daemon 317 1 0 Oct 10 ? 0:00 /usr/lib/nfs/lockd
daemon 311 1 0 Oct 10 ? 0:00 /usr/lib/nfs/statd
daemon 313 1 0 Oct 10 ? 0:03 /usr/lib/nfs/nfsmapid
daemon 291 1 0 Oct 10 ? 0:00 /usr/lib/nfs/nfs4cbd
root 12741 12424 0 03:20:34 pts/4 0:00 grep nfs

This must show statd and lockd running. In fact The statd and lockd must be running on server and
client.

Have a look at which file is shared and it's mode.
# share
- /export/home/oracle rw ""

2)On Saturn machine log on as a root user
oracle@neptune ~$ su -
Password:

Create a directory where you mount the network location.
root@neptune /# mkdir /export/home/oracle/remote

Mount the network drive
root@neptune /# mount -F nfs jupiter:/export/home/oracle /export/home/oracle/remote

Or, with more options issue,
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 jupiter:/export/home/oracle /export/home/oracle/remote
See whether it is mounted.
root@neptune /# df -h
.
.

jupiter:/export/home/oracle
20G 7.6G 12G 39% /export/home/oracle/remote

Now do work is it /export/home/oracle/remote is your local disk location.
Related Documents
Memory Usage of Solaris Operating System

Sunday, October 12, 2008

How to take RMAN backup on a remote disk/ location

It is common to hear that for backup there is not sufficient space on my server or my server disk is about to full. And now I want to take my RMAN backup directly to remote location. How I can achieve that?

Now before taking backup let think in which type of storage on remote location you want to take backup? Is it a disk or to the tape. Below is the procedure for both types.

Taking Remote RMAN backup to Tape
1)This is of no different as you access remote tape devices.

2)First install and configures the drivers of the tape device on the local machine box.

3)Make sure that you can access to tape by making copies file to tape.

4)If step3 is successful then RMAN will be able to take backup to tape directly.

Taking Remote RMAN backup to disk
1)Remember that direct disk backup to remote location using RMAN is not possible as we can do it on local disk by using FORMAT parameter specifying local disk.

2)As RMAN backups to DISK can only be taken to some 'part' of the target database server. So, remote location will not work.

3)The thing you can achieve goal is to share remote location, then mount it on the target database where you want to take backup. An example of how to share folder and access it on other machine is discussed on How to share and Access file on Solaris Machine

4)Take RMAN backup to this mapped location using FORMAT clause. Based on this a remote backup is taken in http://arjudba.blogspot.com/2008/10/backup-to-remote-location-fails-with.html after sharing remote location and map it to location /export/home/oracle/remote/.
Related Documents
RMAN Backup format specification
Set up a Database for RMAN Backup
Create Multiple Duplex backup by one Backup Command

New features of oracle perfomance in 10.2g

In oracle database 10.2g release a lots of performance feature are added in it.

1)Active Session History Reports (ASH): ASH can be used to identify blocking session and waiting session and associated transaction identifiers and SQL for a specified duration.

2)Automatic PGA Memory Management: PGA memory can be managed dynamically and new view V$PROCESS_MEMORY has been added.

3)Automatic Shared Memory Management: Many of the SGA memory components can be dynamically sized by using automatic memory management.

4)Automatic Tuning of Multiblock Read Count: The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is now automatically tuned to use a default value when this parameter is not set explicitly.

5)Automatic Workload Repository(AWR)Reports: Various AWR reports help us to get statistics between two snapshot id.

6)Automatic Workload Repository SQL Collection can be configured: The AWR collects, process and maintain statistics of Top SQLs. Moreover the collection criteria can be configured.

7)Database Replay: With this feature database workload of a production database can be captured and then it can be replayed/tested on a test system as the same way as recorded was in production system with the same timing, concurrency, and transaction dependencies of the production system. On 10.2g only workload can be captured and it can be replayed on 11.1g.

8)End to End Application Tracing: End to end application tracing diagnosis performance problem in a multitier environment. With this feature you can identify the source of an excessive workload, such as a high load SQL statement, and allow you to contact the specific user responsible.

9)Improved System Statistics: The V$SYSSTAT view has added rows to capture the total number of physical I/O's performed by any Oracle process.

10)SQL Access Advisor: The DBMS_ADVISOR package and SQL Access Adviser advised different recommendation based on query.

11)SQL Performance Analyzer: It enables you to forecast the impact of system changes on SQL performance .

12)SQL Profiles:

13)SQL Tuning Advisor:

14)SQL Tuning Sets: With DBMS_SQLTUNE package SQL Tuning sets can be export and imported to another system.

15)V$SQLSTATS View: V$SQLSTATS returns performance statistics.

Related Documents

Improving Index creation speed in Oracle
New features of oracle perfomance in 10.2g
How to find Top Sql through Queries
Difference or Advantage between AWR and STATSPACK report
Ways of Identify High Load ot top SQL
Oracle INSERT Performance
Optimize Data Access Path in Oracle
When you would make Index and when not.

ORA-13600, QSM-00775, ORA-06512 when running DBMS_ADVISOR

Error Description
When using DBMS_ADVISOR on a table in SYS or SYSTEM schema, the following errors returned.

ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter

Below is an example.
SQL> conn system/s
Connected.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM emp WHERE EMPNO = 7788');
END;
/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00775: the specified SQL statement cannot be stored in the workload due to invalid table references
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1501
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 176
ORA-06512: at "SYS.PRVT_ADVISOR", line 2594
ORA-06512: at "SYS.DBMS_ADVISOR", line 726
ORA-06512: at line 2

SQL> create table emp(empno number);

Table created.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM emp WHERE EMPNO = 7788');
END;
/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1501
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 176
ORA-06512: at "SYS.PRVT_ADVISOR", line 2594
ORA-06512: at "SYS.DBMS_ADVISOR", line 726
ORA-06512: at line 2

Cause of the Problem
The quick_tune procedure performs an analysis and generates recommendations for a single SQL statement.
SQL Access Advisor maintains an internal list of non-tunable tables regardless of the contents of the INVALID_TABLE_LIST parameter.
The table owned by SYS, SYSTEM or any other pre-defined Oracle schema can be tuned and hence will return error.

Solution of The problem
Create the table in other schama rather than SYS, SYSTEM or any other pre-defined Oracle schema and run quick_tune procedure.

SQL> create table arju.emp(empno number);
Table created.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM arju.emp WHERE EMPNO = 7788');
END;
/

PL/SQL procedure successfully completed.

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle

Saturday, October 18, 2008

How to generate fibonacci series in Oracle.

Thursday, October 16, 2008

RMAN Incremental database backup in Oracle

Tuesday, October 14, 2008

Same sounded words in oracle

Monday, October 13, 2008

ORA-28547 connection to server failed, probable Net8 admin error

Automatic startup and shutdown oracle on linux

Backup to remote location fails with ORA-19504 andORA-27054

How to share and Access file on Solaris machine

Sunday, October 12, 2008

How to take RMAN backup on a remote disk/ location

New features of oracle perfomance in 10.2g

Improving Index creation speed in Oracle

New features of oracle perfomance in 10.2g

How to find Top Sql through Queries

Difference or Advantage between AWR and STATSPACK report

Ways of Identify High Load ot top SQL

Oracle INSERT Performance

Optimize Data Access Path in Oracle

When you would make Index and when not.

ORA-13600, QSM-00775, ORA-06512 when running DBMS_ADVISOR

Tag Cloud

Blog Archive

Which Oracle database version you use?