Reuse statements, cursor_sharing=force

Reuse statements

select * from emp where ename = :EMPNAME


select * from v$parameter where name like '%cursor_sharing%';

cursor_sharing=force

The kernel parameter cursor_sharing defaults to EXACT, but can be set to FORCE or SIMILAR in order to have the database convert literals to bind variables before parsing the statement.

Setting cursor_sharing =force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

Our queries use a lot of bind variables, by design. In recent benchmarks, CURSOR_SHARING=FORCE was helpful for benchmark runs reducing query execution time by several times.
However, for a specific query involving bind variables, using CURSOR_SHARING=FORCE was very much slower than if executed while CURSOR_SHARING=EXACT.


This is probably a bug. Cursor_sharing = force is not really reliable. We hash the value of the statement and then go looking for a matching hash, regardless of how many users are on the system or how many times the statement has been issued or even what the statement looks like. Force means force, regardless of what is going on, we are going to force the
sharing of cursors. This leads to problems like what you are seeing and the use of suboptimal plans. You can attempt to tune around the sub-optimal plan but you may find that there are a number of queries with that problem. The other possibility is to set cursor_sharing = exact. That will prevent this problem from occuring.

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

http://www.remote-dba.cc/oracle_tips_flushing_secrets.htm
1. Pinning all commonly used PL/SQL packages.

The initial call to a package causes the whole package to be loaded into the shared pool. For large packages this may represent an unacceptable delay for two reasons. First the size of the package causes a delay in load time. Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it. In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list. If the shared pool is full and a new object must be loaded, items on the LRU list are aged out. Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage. The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

http://www.dba-oracle.com/plsql/t_plsql_pinning_pkgs.htm
http://www.oracle-training.cc/oracle_tips_pinning_packages.htm



SQL> conn sys/password as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql


Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

PROCEDURE keep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

PROCEDURE unkeep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

P - Package, Procedure or Function. This is the default value.
T - Type.
R - Trigger.
Q - Sequence.
C – Cursor.


SQL> conn sys/password as sysdba
Connected.
SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1;



SQL> EXEC DBMS_SHARED_POOL.keep(‘

,2. FLUSH SHARED POOL

Periodically issuing the--
ALTER SYSTEM FLUSH SHARED POOL;
alter system flush buffer_pool;
----------
This will "flush" out, or clear, all SQL statements that are in the Shared Pool Area. Oracle keeps track of each SQL statement that users execute.
It is stored parsed in memory so that if a SQL statement already has been executed then Oracle does not need to re-parse it. The exception is if the shared pool area is not large enough, then the least recently used SQL statements (except for pinned packages) will be removed from memory.

By flushing the shared pool, all SQL statements are removed from memory.

----------

RMAN Block Corruption Detection & Recovery

Block Media Recovery with RMAN


sql>select file# block# from v$database_block_corruption; --corrupted block



Rman>> block recover datafile block ; ------recover corrupted block


Rman>blockrecover corruption list;

RMAN> backup validate check logical database;

RMAN recover & open database if archive log missing

RMAN recover and open the database if the archive log required for recovery is missing


1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) recover database until cancel using backup controlfile;
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”

RMAN recovery from loss of all online redo log files

RMAN recovery from loss of all online redo log files


SQL> select thread#,sequence#,status from v$Log;


set oracle_sid=sns6
rman Target SYS/linux@sns1011srv

RUN
{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
set until sequence 2021 thread 1;
ALLOCATE CHANNEL ch1 TYPE Disk;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

Uninstall Oracle from Server Completely

Uninstall Oracle

# Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with ‘Oracle’ and stop them.
* Uninstall all Oracle components using the Oracle Universal Installer (OUI).
* Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key. This contains registry entires for all Oracle products.
* Delete any references to Oracle services left behind in the following part of the registry:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
It should be pretty obvious which ones relate to Oracle.
* Reboot your machine.
* Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
* Delete the "C:\Program Files\Oracle" directory.
* Delete the C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
* Empty the contents of your "c:\temp" directory.
* Empty your recycle bin.

# Delete the Oracle Start Menu shortcuts directory:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
# Remove Oracle refereces from the path. To edit your path go to:
# Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\

Create a Oracle Database Server Using RMAN backup from another Server

Create a Oracle Database Server Using RMAN backup from another Server


1) install Oracle server 10.2.0.3 without create startup database

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

2) Apply patch

Before you apply the patchset you have to check whether the Oracle Version is 32 bit or 64 bit Version, because
the patchsets are different.
For 32 Bit Version, Patch no is p6810189_10204_Win32_patchset
For 64 Bit Version, Patch no is p6810189_10204_MSWIN-x86-64.

1. Shut down the database:
SQL> SHUTDOWN IMMEDIATE.
Stop all the Oracle Related Services.
Ex:- Oracle Listener, Oracle DB Console,Oracle JobScheduler,
Distrubed Transaction Co-ordinator.
Run the Patch Setup.exe in the same Oracle Home.
For Example: If Existing Oracle is installed in c:\Oracle\product\10.2.0\db_1 then you
have to select the same path When you run the Setup.exe. After Successful installation
start the Listener & Db Console etc.,
Enter the following SQL*Plus commands:
SQL> SET ORACLE_SID=sns6
SQL> SQLPLUS/NOLOG
SQL> CONNECT SYS/LINUX@sns1011SRV AS SYSDBA
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
Review the patch.log file for errors and inspect the list of components that is displayed at the end of
catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.


SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are
accessed for the first time. This step is optional but recommended.

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

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

3)

mkdir c:\oracle\product\10.2.0\admin\sns1011\adump
mkdir c:\oracle\product\10.2.0\admin\sns1011\bdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\cdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\dpdump
mkdir c:\oracle\product\10.2.0\admin\sns1011\pfile
mkdir c:\oracle\product\10.2.0\admin\sns1011\udump
mkdir c:\oracle\product\10.2.0\db_1\cfgtoollogs\dbca\sns1011
mkdir c:\oracle\product\10.2.0\db_1\database
mkdir c:\oracle\product\10.2.0\oradata
mkdir e:\snsd1011
mkdir e:\archive1011\sns1011

copy /y M:\backup\tnsnames.ora C:\oracle\product\10.2.0\client_1\network\ADMIN\
copy /y M:\backup\initsns6.ora C:\oracle\product\10.2.0\db_1\database\
copy /y M:\backup\SNCFSNS6.ORA C:\oracle\product\10.2.0\db_1\database\


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

4)

Step 1: Create Database Instance (SID)

C:\>set ORACLE_SID=sns6

Step 2: Establish the Database Administrator Authentication Method

c:\>oradim -delete -sid sns6 --if already exist, delete it

C:\>oradim -new -sid sns6 -SRVC OracleServicesns6 -intpwd linux -MAXUSERS 5 -STARTMODE auto -PFILE C:\oracle\product\10.2.0\db_1\database\initsns6.ora

----------not neccessary
C:\>oradim -new -sid sns6 -SYSPWD linux -STARTMODE auto -PFILE C:\oracle\product\10.2.0\db_1\database\initsns6.ora

C:\oracle\product\10.2.0\db_1\database\PWDsns6.ora created (orapwd FILE=PWDsns6.ora ENTRIES=5)
-------------


Step 3: Create the Initialization Parameter File or place the backup on C:\oracle\product\10.2.0\db_1\database

create initsns6.ora file(C:\oracle\product\10.2.0\db_1\database)

SQL> create spfile from pfile;

Step 4: Connect to the Instance

sqlplus /nolog

SQL> connect sys/linux@sns1011srv as sysdba

SQL>startup nomount pfile='C:\oracle\product\10.2.0\db_1\database\initsns6.ora';

Step 5: Create a Server Parameter File (Recommended)

CREATE SPFILE='C:\ORACLE\PRODUCT\10.2.0\db_1\database\spfilesns6.ora’ from

Pfile=’C:\ORACLE\PRODUCT\10.2.0\ADMIN\orcl\pfile\initsns6.ora’;

SHUTDOWN

Step 6

STARTUP NOMOUNT

Step 7: Create database using RMAN


C:\>SET ORACLE_SID=sns6
C:\>RMAN TARGET SYS@SNS1011SRV

c:\>RMAN>SHOW ALL;

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\archive1011\sns1011\F%';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'E:\archive1011\sns1011\%F';
RMAN> BACKUP DATABASE FORMAT 'E:\archive1011\sns1011\F%';
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\archive1011\sns1011\U%';

RMAN> RESTORE CONTROLFILE FROM 'E:\archieve1011\SNS1011\C-1560435174-20081003-00';
SQL> alter database MOUNT;


RMAN> list backup of database;
RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;

------not neccessary----------------------
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;
RMAN> delete force obsolete;
RMAN> delete expired archivelog all;

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

RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';

RMAN> list backup of database;



RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> recover database using backup controlfile until cancel;

RECOVER THE DATABASE IN DIFFERENT SERVER ON DIFFERENT DIRECTORY FROM ACTUAL BACKUP LOCATION DIRECTORY

SCENIRIO IS I HAVE TAKEN RMAN BACKUP ON A SERVER IN D:\ARCHIVE0910\SNS1011\ LOCATION AND COPY THE BACKUP SET TO B SERVER IN DIFF. DIRECTORY E:\archive1011\sns1011\ LOCATION AND WANT TO RECOVER THE DATABASE ON B SERVER BUT IT IS GIVING ERROR "ORA-19505 failed to identify file "


RMAN> restore database;

Starting restore at 09-APR-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:\SNSD1011\SYSTEM01.ORA
restoring datafile 00002 to E:\SNSD1011\UNDOTBS01.ORA
restoring datafile 00003 to E:\SNSD1011\SYSAUX01.ORA
restoring datafile 00004 to E:\SNSD1011\INDX01.ORA
restoring datafile 00005 to E:\SNSD1011\USERS01.ORA
channel ORA_DISK_1: reading from backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_
1
ORA-19870: error reading backup piece D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1
ORA-19505: failed to identify file "D:\ARCHIVE0910\SNS1011\0OLAHIRK_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
failover to previous backup

creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/09/2010 11:33:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'



Steps: check directory path in init.ora and control.ora but every path is fine as per new server


solution:

if recovery catalog exist then

crosscheck copy of archivelog all;
crosscheck archivelog all;
resync catalog;
delete force obsolete;
delete expired archivelog all;

if not recovery catalog

delete force obsolete;
delete expired archivelog all;


change archivelog all crosscheck;

crosscheck backup of database;
delete expired backup;


----NOW TRY TO RESTORE DATABASE BUT FOLLOWING ERROR----


RMAN> restore database;

Starting restore at 10-APR-10
using channel ORA_DISK_1

creating datafile fno=1 name=E:\SNSD1011\SYSTEM01.ORA
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/10/2010 13:12:43
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'E:\SNSD1011\SYSTEM01.ORA'





===========================FINAL SOLUTION========



RMAN> list backup of database;

-----NOW DELETE ALL EXPIRED BACKUP

RMAN> CROSSCHECK backup of database;
RMAN> delete expired backup;


----NOT NECCESSARY----
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

RMAN> delete force obsolete;
RMAN> delete expired archivelog all;

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


RMAN> list backup of database;


SHOWING NO BACKUP

RMAN> catalog backuppiece 'E:\archive1011\sns1011\0ULAP4RC_1_1';


RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 4.72G DISK 00:20:28 10-APR-10
BP Key: 30 Status: AVAILABLE Compressed: YES Tag: TAG20100410T115004

Piece Name: E:\ARCHIVE1011\SNS1011\0ULAP4RC_1_1
List of Datafiles in backup set 29
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 385085943 10-APR-10 E:\SNSD1011\SYSTEM01.ORA
2 Full 385085943 10-APR-10 E:\SNSD1011\UNDOTBS01.ORA
3 Full 385085943 10-APR-10 E:\SNSD1011\SYSAUX01.ORA
4 Full 385085943 10-APR-10 E:\SNSD1011\INDX01.ORA
5 Full 385085943 10-APR-10 E:\SNSD1011\USERS01.ORA

RMAN> restore database;
RMAN> recover database;
SQL> alter database open resetlogs;