All 102 entries tagged Oracle

View all 121 entries tagged Oracle on Warwick Blogs | View entries tagged Oracle at Technorati | There are no images tagged Oracle on this blog

November 01, 2010

Restore point test

set db_flashback_retention_target = 120

create restore point, then two days later the flashback log is not deleted.

Some archive log is not deleted since
“RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point”

Drop restore point, the flashback log was dropped as well in this command.

August 02, 2010

oracle listener config

I puppetized Oracle listener.ora, then suddenly I cannot connect to Oracle

sqlplus system/xxx@db_service

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Solaris-AMD64 Error: 2: No such file or directory

I checked the configuration, everything seems fine.

After some fruitless effort, I notice the value $oracle_home in of /var/opt/oracle/oratab is /usr/local/oracle. /usr/local/oracle is the symlink of /package/oracle. I changed the value of /var/opt/oracle/oratab to /package/oracle and reset the environment variable ORACLE_HOME to /package/oracle. Restarted the database and I can now connect to database.

February 26, 2008

Wake up Oracle RFS

Follow-up to Force Oracle primary DB to reset connection to logical standby from Oracle/Java/Others

After another short period network outage, the primary database y wont sent archivelog to standby server.

set log_archive_dest_state_n = defer| enable cannot wake up oracle this time.

Fixed one server using the following:

alter system set log_archive_dest_2='service=boston valid_for=(online_logfiles,primary_role) db_unique_name=boston';

alter system set log_archive_dest_2='service=boston lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=boston';

The preceding method only works slight better. During nigthtly rman backup, several logs missing (Omit 100 words …). Eventaully set log_archive_max_processes to a higher value fixed the problem

July 13, 2007

11G features


  • Incident packaging service: automatically pack all diagnosistic data into zip file. Raise a service request is easy
  • Autmatica Diagnostic repository replaces all XX_DUMP_DEST directories
  • Flashback Data ARchive can keep UNDO data as long as required
  • 112g network-based duplication will duplicate source db to clone dbwithout requiring source db to have existing backups.
  • Data recovery advisor will identify which recovery option is feasible

For Developer

  • Create index as Invisible attribute cause CBO to ginore the presence of the index
  • Add a column with a defult value to a table with many rows could take a significant amount of time and holad a lock on the table. This restriction has been removed
  • SecureFiles : store/read files into db, performance is 10X fater than LOB and has silmilar speed as Linux NFS

July 11, 2007

Oracle metric – User Calls, Recursive Calls, Execute Count

According to Oracle doc

  • User calls: Number of user calls such as login, parse, fetch, or execute. This metic probabaly incorporates all the activities we are interested from a Java developer’s perspective. It includes Select/update/delete/insert
  • recursive calls: Number of recursive calls generated at both the user and system level. An example of call at user level: a SQL call a store procedure which contains a chunk of SQLs. System recursive call are additional calls for housekeeping such as space management, etc.
  • Execute count: total number of calls (user and recursive) that execute SQL statements. Does that includes “select” statement? I assume not.


To varify my undstanding of these concepts I wrote a program doing DML against a test database. Result is here


based on Java 3 tier app using JDBC :

  • Execute Count = No. of select/insert/delete/update. For easier understanding. do not think it includes recursive calls as doc indicated.
  • Recursive Calls = No. of update/insert/delete + Ceiling(Hard parse)
  • User Commit = min(No. of update/insert/delete)
  • User Calls = No. of select/insert/delete/update + N * Hard Parse (where N =2 when select and N =1 otherwise.)
With function,
  • All SQLs in a function will includes in EC, UC.
  • A function call will have one RC , one EC and UC


  • A sample took from live server : User Calls = 509; Execute Count = 174, Recursive Call = 57 User Commit = 5
  • The app does not use function, so Execute Count is the number of DMLs send from hibernate to database.
  • User Calls is high, it is from hard parse. So the app should use bind variable.(might not true?)
  • Recursive Calls are from hard parse and update/delete/insert. Since user commit is low, so most of RC are hard parse.

Another sample: User Calls = 3872; Exeucte : 1237; Recursive: 28
Recusive is low, so function call and hard parse is low. Why User Calls is so higher than Execute Count??

Looking at v$sql, I find out hundred of SQL like

delete from user_preferences WHERE 
forumid=550 and userid=30387 and name='mailPriority' and value='0'

apparantly cached hard parse cause User Calls high. Cached hard parse wont incure many recursive calls since they are cached. however, one cached hard parse will always generate one user calls.

Action: Ask our developer to use bind variable for this SQL
Expectation: User calls will dropped to EC value.

June 07, 2007

RMAN vs OS backup on NetApp

Before RMAN emerged, OS backup is the main way to backup a database. It has shortcomings:
  • Cannot do incremental backup. If datafiles are big, the backup time is long
  • It takes more disk space
  • You cannot really know whether your backup will come to rescue in disaster. RMNA offer the ability to validate the backup

RMAN offers the following bonous beyond the normal backup

  • RMAN can compress the backup
  • RMAN can check the corrupted data block during backup.
  • RMAN can delete obsolete backup set

One shortcoming of RMAN is that you have to maintain a repository server to make full use of RMAN.

The pendulum has swayed so far in favour to RMAN that OS backup seems to be dead.

NetApp’s Snapshot change the balance a bit, if not completed reverse the trend.

With Snapshot,
  • the backup time is long. The backup process almost take no tiime and the size of DB is irrelevant. I now take backup every hour.
  • It takes more disk space Snapshot do incremental backup at OS level.
  • RMAN can compress backup With OS level incremental backup, this is not important
  • MAN can check the corrupted data block during backup Still an advantage
  • _RMAN can delete obsolete backup set Snapshots can be deleted by a scripts
Tremendous advantages of Sanpshot are:
  • The backup is superfast
  • Restore is easy. Just normal OS copy. It should be a little faster than RMAN restore since RMAN need to restore the backupsets to OS files.
  • Get rid of repository server.

I have not got a chance to test “SnapManager for Oracle”, which combine Snapshot with RMAN. Maybe after I do that, I will have different opinion.
So far, Snapshot + OS backup will supersede RMAN.

May 30, 2007

NetApp and Oracle backup and restore

Oracle OS Oracle backup:

  1. SQL>alter tablespace test begin backup
  2. Within netapp command line: snap create volume_name snap_name
  3. SQL> alter tablespace test end backup
  4. insert some data into table belonging this tablespace test
  5. rm one datafile test.dbf
  6. SQL>alter system checkpoint; alert log will report missing datafile 9
  7. cp ../.snapshot/../test.dbf dest_location
  8. alter database recover datafile 9;
  9. alter database datafile 9 online;

RMAN backup with SnapManager for Oracle:

May 25, 2007

Easy connect naming method

Easy connect name method is very convenient to use. So you do not need to modify tnsnames.ora any more.

The format copied from Oracle doc is as follows:
CONNECT username/password@host[:port][/service_name][/instance_name]

However, the instance_name one did not work during my test.
The service name works. The service name = sid + doman name.(e.g.

The sqlnet.ora need to be changed as well.


May 07, 2007

Oracle Listener Error

06-MAY-2007 23:52:30 * (CONNECT_DATA=(SID=wblogs)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST
= * establish * wblogs * 0
06-MAY-2007 23:52:33 * ping * 0
WARNING: Subscription for node down event still pending

07-MAY-2007 20:20:06 * (CONNECT_DATA=(SID=wblogs)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=137.2
05.243.25)(PORT=37842)) * establish * wblogs * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Solaris Error: 32: Broken pipe
07-MAY-2007 20:20:06 * 12560
TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error
  Solaris Error: 130: Software caused connection abort

According to metalink Note 284602.1,Set SUBSCRIBE_FOR_NODE_DOWN_EVENT_ =OFF will remove the “Subscription for node down event still pending” message and possiblely fix the problem

April 27, 2007

Solaris 10 kernal parameters for Oracle software

Oracle doc is inaccurate and misleading.
Metalink doc 317257.1 is worth looking at.

Since Solaris default values are higher than Oracle recommended values, the only resource control that might need to be set is project.max-shm-memory. !!!

shmsys:shminfo_shmmax (max value of shared memory segment) is replaced by project.max-shm-memory(Total amount of System V shared memory allowed for this project. ), The default value 1/4 physical RAM.

To change the value: prctl -n project.max-shm-memory -v 10gb -r -i project default

shmsys:shminfo_shmmni (max number of shared memory identifiers on system) is replaced by project.max-shm-ids (Maximum number of shared memory IDs allowed for this project) default 128
To change the value: prctl -n project.max-shm-ids -v 100 -r -i project

seminfo_semmni (the maximum # of semaphores sets in the system) is replaced by project.max-sem-ids default 128
To change the value: prctl -n project.max-sem-ids -v 100 -r -i project

seminfo_semmsl=256 (# of semaphores in a semaphore set) is replaced by project.max-sem-nsems default: 512 ,
To change the value: prctl -n process.max-sem-nsems -v 256 -r -i project [project name]

The doc here give very good explanation about shared memory and semaphores

Use ipcs -b to display the above info.

You can get all project name from /etc/projects
To list the process in the project : prstat -j [project name]
Oracle is in project “default”, Use prstat -j default to find the process

Search this blog

Search Warwick Blogs



Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder