Oracle Blog

July 9, 2010

How much Recovery is enough Recovery

Filed under: Clone,RMAN — Vikram @ 4:56 pm
Tags: , ,

Long ago in one of the non-technical group discussions we had to decide ‘How much money is enough money?’ Obviously no answer is enough answer. Fortunately in Oracle, we know how much recovery is just sufficient to OPEN the DB.

During the recovery of the DB, when the archives are being applied one after other and another, is it possible to definitively confirm that the most recently applied archive was the last one required to be applied and it is safe thereafter to open the DB in RESETLOGS?

There should be several ways of identifying where to stop, and here are three that I can think of:

  1. Alert log – If the backup was hot, finding out the last occurrence of END BACKUP and its corresponding SEQ# should tell us where to stop. Usually we stop after applying the above identified SEQ# and the DB should then be good to OPEN RESETLOGS.
  2. RMAN log – If the backup was taken using RMAN, its log also shows the SEQ# where the backup ends. We can stop applying archives once we reach the max SEQ#
  3. Query the Data Dictionary – Query the FHSTA column in X$KCVFH table during recovery to know if the recovery was sufficient or does it need more archives.

As I said, there must be other ways too.
I prefer option (3) because the information is right there in the DB to be queried for, anytime.

Consider a typical scenario where a user, say Lucy, is recovering the DB from the hot backup of the source database. She has already applied 30 archives and swears that she hasn’t seen another hungrier database. She has no access to the alert log of the source database to check how many more archives are to be applied. After each archive log has been applied, she CANCELs the recovery and gets the below error:

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u11/data/PRD/PRD_SYSTEM_01.DBF’

The better way to confirm if the recovery was sufficient or not is to query the data dictionary. The SQL is:

SELECT hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE FROM x$kcvfh;

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh

The output will look like above.

The STATUS (or fhsta) column has the value of 1 which means the datafile with FILENUMBER needs more recovery. This is the time to apply more archives.

The STATUS is 0 when the recovery is sufficient and it is safe to OPEN the database.

The fhsta can have one of the below values:

0     – DB is consistent. No more recovery required.
1     – DB needs more recovery. It’s time to apply more archives.
4     – DB is in a FUZZY state. Was the backup good?
8192  –
8196  –

I have come across 8192 and 8196 statuses too, but I don’t know their significance yet.

When the archives have been sufficiently applied, the fhsta column will be zero and looks like below:

At this stage, it is possible to cancel the recovery and bring up the DB in RESETLOGS.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.

The table X$KCVFH is a fixed table in Oracle. It stores the file headers along with their statuses. The name is derived from:

K     – Kernel layer
C     – Cache layer
V     – RecoVery component
FH    – File Header

Hope this post was useful.


June 28, 2010

Oracle Forms Crash during launch – Browser disappears

This happened to me a week ago. After successful login into the 11i Oracle Applications e-business suite, when I click on the menu to launch the forms, the browser crashes. We were at the end of a major upgrade and the first thought was whether the upgrade had changed anything. But when the issue was pinned to its cause, there was no mystery left.


After clicking on any menu link to launch the forms like below, the browser disappears.


There is no error reported anywhere. I was using Internet Explorer 7 (IE7) when I had this issue. But I found out later that this issue would repeat for any version of IE. The error also accompanies with a message sometimes:

Internet Explorer has encountered a problem and needs to close. We are sorry for the inconvenience.


The MOS note 430359.1 says:

The problem can have various causes but generally is caused by incompatibilities between shared DLL’s

The likely cause of the issue is an incompatibility between Oracle Jinitiator and a 3rd-party browser extension. You can test this by disabling 3rd-party browser extensions as follows:

Navigate IE – Tools – Internet Options – Advanced – untick ‘enable third-party browser extensions


Or we can find out which third-party is actually causing this issue too. Per Metalink note: 430359.1 (Internet Explorer Crashes When Forms Is Launched), there are a few 3rd party browser extensions that conflicts with Jinitiator. For ex., Windows Live Toolbar, Windows Live Toolbar Helper, Windows Live Messenger, McAfee Site Advisor.

Tools -> Internet Options -> Programs


In my case, I had to disable Windows Live Sign-in Control and Windows Live Sign-in Helper.

These add-ons had come with the installation of Windows Live Writer on my laptop.

This issue does not occur if the application environment has Sun JRE installed.

June 9, 2010

GPG – A Quick Howto

Filed under: Encryption,GPG,Tools — Vikram @ 8:45 pm
Tags: , ,

Without delving deep into concepts, let’s take a quick approach to encryption using GnuPG (also GPG). Encryption is becoming more and more essential today. We will check how GPG can be used to secure the communication between two parties. The scenario here is to share a file between two users on a public network line.

The basic requirement for encryption is that the user has a pair of public/private key using which the data is encrypted/decrypted. The user generates the key pair and distributes the public key to the general world. The private key is retained by the user and is used to decrypt the data. The public key is used by the rest of the world to encrypt and send data to the user.

In brief, the steps involving encryption are below. Assume user B wants to send a file to user A. The user B should encrypt the file with user A’s public key. User A will receive the file in an encrypted form. The file can be decrypted using only A’s private key. Since the file was exchanged in the encrypted format, there is no way for an eavesdropper to read the contents of the file. It is A’s responsibility to publish the public key for everyone to see and to secure the private key from being misused. The strength of this encryption lies in the keys. Hence the bigger the keys, more secure is the encryption. The steps can be summarized as follows:

  • User A publishes the public key
  • User B encrypts the file using A’s public key
  • The file is shared
  • User A decrypts the file using the private key
  • User A now has the file (and its contents)

The only weakest link in the entire procedure is the possibility that user B gets the wrong public key. It becomes B’s responsibility to confirm if the public key belongs to user A or not. There are ways to do that too. In this post, we will follow the basic encryption/decryption cycle from a beginner’s perspective. The major steps we will focus on are below:

  1. Generating key pair
  2. Listing the keys
  3. Sharing the public key
  4. Importing the public key
  5. Validating the public key
  6. Encrypting the document
  7. Decrypting the document

The following commands were executed on Linux.

1. Generating key pair
The public/private key is first generated by the user A for use. The command to generate the keys is below:

$ gpg –-gen-key

This command prompts for information. Our responses are typed in Red.

Please select what kind of key you want:
(1) DSA and ElGamal (default)
(2) DSA (sign only)
(4) RSA (sign only)
Your selection? 1

The explanation for each option can be found in the documentation. Mostly the default values suffice.

About to generate a new ELG-E keypair.
minimum keysize is  768 bits
default keysize is 1024 bits
highest suggested keysize is 2048 bits
What keysize do you want? (1024)1024
Requested keysize is 1024 bits

The longer keysizes enhance security.

Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (0) 0

The above questions prompts for the expiry date of the key. We chose default.

Real name: Vikram
Email address:
Comment: Vikram’s GPG key pair
You selected this USER-ID:
“Vikram (Vikram’s GPG key pair) <>”

The prompts can again be checked when we list the key generated. We will see how in the next step. Meanwhile we need a passphrase to secure our key. The prompt is shown below

Enter passphrase:
Repeat passphrase:

You need a Passphrase to protect your secret key. Remember the key used.

The keys are now generated in the user’s $HOME/.gnupg directory.

2. Listing the keys
The keys you currently have in your key-ring can be listed using the below command:

$ gpg –list-keys
pub  1024D/BA079D00 2010-05-25 Vikram (Vikram’s GPG key pair) <>
sub  1024g/2351CA0A 2010-05-25

Notice the above listing shows the Name, Email-ID and the comment we supplied in our previous step where we generated the keys. This information is stored in the user’s $HOME directory under hidden directory .gnupg. Notice there’s a dot (.) at the beginning of the directory name. In Unix, directories can be made hidden by beginning the name of that directory with a dot.

Let’s now share the public key with user B now.

3. Sharing the public key
The key information is stored in the $HOME/.gnupg directory of a user. The following command lets us export the public key into a readable format that we can share with others.

$ gpg –armor –export > pubkey.gpg

The above command exports the public key associated with into a file pubkey.gpg. This is the public key that will be shared with the world.

4. Importing the public key
Once the public key is received, user B can import the key into his key ring. The following command lets user B import user A’s public key.

$ gpg –import pubkey.gpg
gpg: /home/sadba/.gnupg: directory created
gpg: new configuration file `/home/sadba/.gnupg/gpg.conf’ created
gpg: WARNING: options in `/home/sadba/.gnupg/gpg.conf’ are not yet active during this run
gpg: keyring `/home/sadba/.gnupg/secring.gpg’ created
gpg: keyring `/home/sadba/.gnupg/pubring.gpg’ created
gpg: /home/sadba/.gnupg/trustdb.gpg: trustdb created
gpg: key 814B92F9: public key “Vikram (Vikram’s GPG key pair) <>” imported
gpg: Total number processed: 1
gpg:               imported: 1

After the A’s public key is imported into user B’s key ring, B can encrypt the files using A’s key. The file can then be decrypted only using A’s private key.

5. Validating the public key
Once a key is imported, it should be validated. Here is how you do that:

$ gpg –edit-key pubkey.gpg
command> fpr
command> sign
Really sign? y
command> check

More on validating can be found in the documentation.

6. Encrypting the document
Let’s get to work now. We will encrypt a sample file as shown below:

$ gpg –output hello.gpg –encrypt –recipient hello.txt

The file hello.txt will now be encrypted under hello.gpg. This is the encrypted file that will be sent to user A. No eavesdropper will be able to decrypt this file unless (s)he obtains the private key of the user A. It’s A’s responsibility to keep the private key safe. Let’s mail the file hello.gpg to user A.

7. Decrypting the document
User A now receives the file hello.gpg in an encrypted format. A can read the file only after the file is decrypted. The file can be decrypted only with A’s private key. The command to decrypt is below:

$ gpg –output hello.txt –decrypt hello.gpg
Enter passphrase:

Isn’t it simple? The file hello.gpg is now decrypted into hello.txt. (User A can name the file anything). The command asks for the user’s passphrase. This is the most crucial part of decryption. Only user A should know the passphrase. This is the same passphrase that was given in our step (1) when we generated our keys. We laid emphasis on the word remember in that step.

In this post we saw the basic steps how to encrypt and decrypt the files using GnuPG. Our post only discusses the basics of the matter. For those interested in reading more, the GNU Privacy Handbook documentation is the right place.

April 28, 2010

GZIP or COMPRESS – An Observation

Filed under: BattleAgainstAnyGuess,Clone,Oracle,Script,Uncategorized — Vikram @ 6:47 pm

Recently when investigating a very slow database backup operation, I had to spend some time deciding which command to use: gzip or compress. My observations are below.


The Database hot backup of 350G is taking 9 hours to complete. The shortest time the previous vendor could manage a hot backup was in 4.5 hours. It fell upon me to investigate what is causing the delay in the backup of the datafiles. One of the aspects we reviewed as part of determining the cause was to decide which command should be used: gzip or compress. The command gzip shrinked the files to smaller sizes when compared to compress. However, compress took less time to shrink the file than gzip.

In the below snapshot, there are files listed along with their actual sizes and sizes they have been shrunk using compress and gzip command. Almost always gzip shrunk the files to smaller sizes than compress. Almost always, compress took less time than gzip.


The above snapshot randomly selected seven files for the test.

GZIP took 9 hours but took 50G of space for entire database

COMPRESS took 5.30 hours but took 91G of space for entire database.

Command compress takes less time to shrink files. If we have time constraints but we can do with slightly bigger compressed files, then compress is the command.

Command gzip takes more time to shrink files. If we have space constraints but we can do with slightly bigger compression times, then gzip is the command.

The observation, though, that compress is right or gzip is right cannot be made generic. One should exercise one’s own discretion when deciding which command to be used. In our case, we decided to go with compress command to save on time.

March 25, 2009

RMAN: Recovering from a Missing or Corrupt Datafile

Filed under: Oracle,RMAN — Vikram @ 6:07 pm
Tags: , , , ,

While DB Backup management is a very important task for a DBA, it is the Recovery that calls for the crisis management. Almost always at times of DB recovery, the DBA is in pressure to bring the DB back to normal with minimum resource loss. The client expects minimum data loss, minimum time loss. It is advisable for the DBAs to be well-equipped with the most common recovery scenarios in such times. Let’s use RMAN for various recovery scenarios over a few subsequent posts. Let’s also review a few good practices on our way to recovery.

Let’s begin with recovering from a lost or corrupted datafile when the backup is in tact and the DB is open.

RMAN: Recovering from a lost datafile

Any kind of recovery needs a few very basic steps:

1) Checking the availability of valid backup
2) Deciding upon the recovery procedure
3) Actual recovery

The Scenario
Before we begin on the recovery procedure, let’s appraise the scenario. One of the datafile in a DB is lost or corrupt. The database is in OPEN state and, fortunately, the backups are in place. Of course, the first thing we will do is to confirm that the backups are indeed available. The backups were taken using RMAN.

TIP: A good backup plan is always very essential for the Database. Every DBA has to give enough thought to the backup plan and decide what is good for his organization.

In our case the datafile users01.dbf was missing from the database. The tablespace was USERS and this had a single datafile. The queries on the objects in that datafile were erroring out with the below ORA-01110 error. This error was accompanied by ORA-00376.

SQL> select owner, segment_name from dba_segments where tablespace_name='USERS' and segment_name='BONUS';
OWNER                          SEGMENT_NAME
------------------------------ ---------------------------------------------------------------------------------
SCOTT                          BONUS 

SQL> select count(1) from scott.bonus;
select count(1) from scott.bonus
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\VIKRAM\ORADATA\TEST2\USERS01.DBF'

Moreover, when I queried the V$DATAFILE_HEADER view the error seen was:

FILE#      STATUS  ERROR                     REC TABLESPACE_NAME                NAME
---------- ------- ------------------------- --- ------------------------------ ----------

The ERROR column shows file not found. Additionally, we may also run dbv utility on all the datafiles to uncover underlying datafile corruption.

So, this was the situation when it was decided to recover this datafile from backup. The DB was, as already mentioned, in OPEN state. The DB can as well be in the MOUNT state to perform this procedure. But, it isn’t necessary. Making the Datafile offline will be sufficient to carry out the recovery procedure.

Checking the Availability of the Backup
The first step in any recovery procedure is to confirm the availability of the complete set of backup. By complete set I mean all the files required for the recovery. This is called the Redundancy Set. The set of files needed to recover an Oracle database from the failure of any of its files – a datafile, a control file, or online redo log – is called the redundancy set.

After connecting to RMAN we can confirm if our backup is good enough to restore the missing datafile. The below command lists out the backup piece required to restore the datafile (in our case datafile number 4 is the missing datafile). We should immediately look for this backup piece’s availability.

RMAN> restore datafile 4 preview;
Starting restore at 24-MAR-09
using channel ORA_DISK_1
List of Backup Sets
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    591.91M    DISK        00:01:51     24-MAR-09
BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20090324T143340
List of Datafiles in backup set 4
File LV Type Ckp SCN    Ckp Time  Name
---- -- ---- ---------- --------- ----
4       Full 578065     24-MAR-09 D:\VIKRAM\ORADATA\TEST2\USERS01.DBF
archive logs generated after SCN 578065 not found in repository
Media recovery start SCN is 578065
Recovery must be done beyond SCN 578065 to clear data files fuzziness
Finished restore at 24-MAR-09

From the above snapshot it is evident that the backup-piece required is O1_MF_NNNDF_TAG20090324T143340_4WL9W61M_.BKP. It also tells us that the archives beyond the SCN 578065 are required to clear the datafile’s fuzziness.

Next thing we got to do is to confirm that the archives are in place until atleast the required SCN reported by the above command. In this case, we need archives until atleast SCN 578065. Let’s check if the required archives are available.

RMAN> list backup of archivelog all;
List of Backup Sets
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       2.26M      DISK        00:00:02     24-MAR-09
BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20090324T142932
List of Archived Logs in backup set 3
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    3       574556     24-MAR-09 577816     24-MAR-09
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5       2.26M      DISK        00:00:03     24-MAR-09
BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090324T143340
List of Archived Logs in backup set 5
Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
---- ------- ---------- --------- ---------- ---------
1    3       574556     24-MAR-09 577816     24-MAR-09

The above snapshot shows two backupsets for archivelogs. Backupset with key 5 has the entries until SCN 577816. Not good. We need archives until SCN 578065. So, the archivelogs we have are not sufficient for the backup. We must locate the archive logs or redo logs beyond the SCN 577816 and upto 578065. Let’s look for redo for sequence numbers beyond 3. (it is 3 because the above Backupset with key 5 has archive logs until Sequence 3).

The below command shows the SCN contained in the redo log files. The Thread 1, Sequence 5 has changes until 584110. The redo log file with sequence 4 has the changes until 574556 which is not sufficient in our case. We will need redo logs until sequence 5 for recovery.

SQL> select * from v$log;
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          5   52428800          1 NO  CURRENT                 584110 24-MAR-09
         2          1          3   52428800          1 YES INACTIVE                574556 24-MAR-09
         3          1          4   52428800          1 YES ACTIVE                  577816 24-MAR-09

This confirms the availability of all the required archives for recovery in our case.

Once the required backup is in place, it is very easy to recover from any critical situation in Oracle. Let’s begin the recovery.

The Procedure
Recovery can be complete or incomplete. A complete recovery has no data loss whereas an incomplete recovery has some data loss. An incomplete recovery is accompanied by RESETLOGS. An incomplete recovery is necessary when some or all of the data is unavailable to recover the object from its restore point to the point of dataloss. An object here could be a datafile, tablespace, or database. Restore point here is the point until where the DB has been restored to using the backup, and to make the DB current until the point of object loss we will need recovery to be performed. Recovery requires availability of archived and online redo logs. If the archived logs or redo logs are missing, then incomplete recovery is the only option. We may have to use RECOVER…UNTIL TIME option for point-in-time recovery of the missing object. (more on the point-in-time recovery in future posts).

In our case, since we have all the required archives we will perform a complete recovery. There is no data loss in this recovery procedure. The DB can remain in MOUNT or OPEN state during this procedure. Only the datafile will be made offline during the restore/recovery operation if the DB is in OPEN state.

The Recovery Itself
Since all the backups are available, the actual recovery procedure is quite simple.

Connect to RMAN and issue the below statements:


The snapshot of the same, in my DB, is below. In my snapshot, the ALTER TABLESPACE users ONLINE statement is missing. I wanted to confirm that the RECOVER TABLESPACE command runs successfully before bringing the tablespace online. I ran the ONLINE tablespace command later at the end.
Also note that this RMAN command OFFLINE’s and ONLINE’s the entire tablespace. Since in our case this tablespace had only one datafile (which was missing), it wont much matter OFFLINE’ing the tablespace or datafile. If we had more than one datafile in this tablespace and one among those datafiles was missing or corrupt, we would OFFLINE only that datafile instead of the entire tablespace. The command is ‘ALTER DATABASE DATAFILE <file_num> OFFLINE’.

5> }
Starting restore at 24-MAR-09
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 00004 to D:\VIKRAM\ORADATA\TEST2\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\VIKRAM\FLASH_RECOVERY_AREA\TEST2
channel ORA_DISK_1: restored backup piece 1
               \O1_MF_NNNDF_TAG20090324T143340_4WL9W61M_.BKP tag=TAG20090324T143340
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 24-MAR-09
Starting recover at 24-MAR-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 24-MAR-09

The recovery is complete successfully. We don’t see any errors reported. Now we ONLINE the tablespace as below:

sql statement: ALTER TABLESPACE users ONLINE

This completes the recovery of the missing datafile. After this, the command (which errored out earlier) will run successfully:

SQL> select count(1) from scott.bonus;

Let me know in case you find any issues following this procedure and I will assist you.

Please leave a comment if this post was helpful to you or if you have any suggestions or advice to make these posts more informative.

March 16, 2009

Print only on Single side of the Paper

Filed under: e-business suite,Printers — Vikram @ 9:16 pm
Tags: ,

A printer can be configured to print either on single-side or double-side of the paper. When the printer is configured to print both sides of a printer and if the requirement is to print only on one side of it, then we will need to customize the driver for that printer. It is a common knowledge that the printer configuration can be very messy, especially if it deals with tweaking the driver settings or the Initialization parameters. Let’s see how a printer which is configured to print both sides of a paper can be forced to print only on a single side of the paper by customizing the initializing parameters of that printer’s driver.

Supposing the printer that needs to be configured is called HP4300. We will need to find out the driver that is associated with this printer and then make the changes.

1) Find out the Type associated with the Printer

Navigate to ‘System Administrator‘ responsibility –> Install –> Printer –> Register
Query for the printer HP4300

The type associated with our printer is ‘—PASTA Universal Printer Type’

2) Query for the Style and Driver that needs to be changed

Upon clicking the button ‘Printer Types’ in the above screen, you will see a Printer Types form. Query for the ‘—PASTA Universal Printer Type’ and note the Style and Driver.

In my case the client used the custom Style LANDSCAPE-VSG and the Driver of the same name. This was the one I had to modify. You may query for the style and driver that is relevant in your case. Make a note of the Driver name to query in the next form.

3) Open the Driver form and take a backup

Upon clicking the ‘Driver’ button in the above form, we will see the below ‘Printer Drivers’ form. Query for the relevant driver that is picked from the previous screen. In my case the driver name is LANDSCAPE-VSG. This is a custom print driver specific to my client. You may want to look at the driver details pertinent to your requirement.

NOTE: Take a snapshot of the screen now to revert back the changes later in case you decide to back out. You may copy the Initialization string for that driver. This is the initialization parameter that we will be changing.

4) Make changes to the Initialization string

Change the initialization string from the above highlighted version to the one I show below:


Note the changes in the bold font. I have added l0s (elzeroyes) to the initialization string. The rest of the string remains the same.

5) Bounce the Concurrent Managers to bring the changes into effect

6) Test the setup.

The printer should now print only on single side of the paper though it is configured to print both sides of it.

Explanation: The changes that we made in the Initialization string enables Simplex printing. Simplex printing means printing on one side of the paper, as opposed to Duplex printing that prints on both sides of the paper. The zero that was added in the above string denotes Simplex printing and one (if added) would have meant Duplex printing. The cryptic language of printers is called Printer Command Language (PCL).

I would love to hear your experiences with printers.

March 13, 2009

Missing Redo Logs – Scenario

Filed under: Oracle,Redo Logs,RMAN — Vikram @ 11:39 pm
Tags: , ,

Consider a scenario where you have lost your redo logs on a DB instance. It is possible to recreate them. It is also possible to recover the database with no data loss in certain situations. Let’s jump to action immediately. But first we need to know different ways of redo loss and which category we fit in.

A Redo log is a journal of events happening in the DB at any given time. Redo logs are the most crucial part of the database after the controlfile. The documentation defines the redo logs as follows:

The online redo log is a set of operating system files that record all changes made to any database block, including data, index, and rollback segments, whether the changes are committed or uncommitted. All changes to Oracle blocks are recorded in the online log.

A redo log, at the time of being deleted, will be in one of the below scenarios:
1) The redo log is not CURRENT and archived
2) The redo log is not CURRENT but not archived
3) The redo log is CURRENT (DB was shut down cleanly)
4) The redo log is CURRENT (DB was not shut down cleanly)
Identify which scenario fits you. In all the scenarios except (1) we will need to open the database using OPEN RESETLOGS option. You may use the below SQL to find the above details:

SQL> select, b.member, a.archived, a.status
  2  from v$log a, v$logfile b where;

    GROUP# MEMBER                                   ARC STATUS
---------- ---------------------------------------- --- ----------------

Remember: RESETLOGS is compulsory when there is an incomplete recovery.

1) Redo log is not CURRENT and archived

If a redo log file is already archived, its loss can safely be ignored. Since all the changes in the DB are now archived and the online log file is only waiting for its turn to be re-written by LGWR (redo log files are written circularly) the loss of the redo log file doesnt matter much. It may be re-created using the command


This will re-create all groups and no transactions are lost. The database can be opened normally after this.

2) Redo log is not CURRENT and not archived

If the redo log is lost before it is archived then there are a few transactions in the redo log that are lost too. Since a crash recovery is no more possible, a media recovery is required. The DB needs to be restored from a previous backup and recovered until the last available archive log is applied. The reason I say crash recovery is not possible is that by definition a crash recovery should need only online redo logs to recover the database to a consistent state. If a recovery needs a log file that is not online (like archive logs) then the recovery is no more a crash recovery. It becomes media recovery. In our current case since we are applying the archives and using the previous backup to restore it is not a crash recovery.
Also since we cannot recover database completely as some data is lost in the redo log files before being archived, this becomes an incomplete recovery too. The DB needs to be opened up in RESETLOGS mode. The steps to recover are below:

-- Restore the DB from previous backup and open the DB in mount state.
-- Apply all the archives available

In a similar scenario an RMAN restore will be as below:

RMAN> RESTORE CONTROLFILE FROM '<backup tag location>';
RMAN> RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";

The above commands can also be used in a normal recovery scenario from Production to Test with a few modifications. The actual plan of steps that I had followed can be found here. There is a metalink note 1044466.6 that discusses this recovery in greater detail.

3) Redo log is CURRENT (DB was shut down cleanly)

If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss. It is advisable to take a full backup of DB immediately after the STARTUP.

4) Redo log is CURRENT (DB was not shut down cleanly)

When a current redo log is lost, the transactions in the log file are also lost before making to archived logs. Since a DB startup can no more perform a crash recovery (since all the now-available online log files are not sufficient to startup the DB in consistent state), an incomplete media recovery is the only option. We will need to restore the DB from a previous backup and restore to the point just before the lost redo log file. The DB will need to be opened in RESETLOGS mode. There is some transaction loss in this scenario.

RMAN> RESTORE CONTROLFILE FROM '<backup tag location>';
RMAN> RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";

Here we come to an end of all the scenarios in which a redo log can be lost.

There is a simpler way to restore the deleted current redo log file on Unix/Linux platforms too. The procedure to do it will be mentioned in my subsequent posts.

Meanwhile, I would love to hear from you any suggestions or additions to this post. Thank you.

Online Help – Blocked

Filed under: e-business suite — Vikram @ 9:16 pm
Tags: ,

I had this unique request from my client to block Online Help for the e-business suite users. When the users click on the Help -> Window Help or Help -> Oracle Applications Library from the application forms, they should not see any data. Or the site should be blocked for them.

There could be better ways to fix this. But the quick fix that I chose to perform was to block the help pages at Apache level. The procedure is detailed here.

In the file $IAS_ORACLE_HOME/Apache/Apache/conf/apps.conf I added the following lines:

<Location ~ "/OA_HTML/jsp/fnd/fndhelp\.jsp$">
   Order deny,allow
   Deny from all

And bounced the Apache.
$ $APPLCSF/scripts/$CONTEXT_NAME/ restart

Ensure apps.conf file location is un-commented in oracle_apache.conf file and oracle_apache.conf is un-commented in httpd.conf file. All these files are in the same location: $IAS_ORACLE_HOME/Apache/Apache/conf.
The users will now not be able to launch the Online-Help. If they do, they will see the HTTP-403 error. When checked the access_log for Apache you will see the similar lines in it: - - [13/Mar/2009:11:58:11 -0400] "GET /OA_HTML/jsp/fnd/fndhelp.jsp?lang=US&root=FND:LIBRARY&path=US/FND/@FNDSCSGN_NAVIGATOR HTTP/1.1" 403 237

Note the 403 in the above line. This is the status Apache has returned when we queried the fndhelp.jsp.

Of course, with a little ingenuity we can re-direct the users to a more friendly error page. Apache is a wonderful framework and with its proper knowledge one can do great things.

March 8, 2009

RMAN Restore from Production to Test – Scenario

Filed under: Clone,RMAN — Vikram @ 6:35 pm
Tags: , , ,

Below is a scenario of RMAN restore from an old backup. This is my very first experience with RMAN. I intend to work more on RMAN over the next few days and I would like to share my knowledge with everyone. Feel free to correct me anywhere I am wrong. Your comments are welcome.

The client takes a level 0 (full) backup of DB every day. Since the DB is small, a level 0 backup suits us well. Else a proper backup strategy would have been useful. My client tests his tape backup quarterly for its propriety. RMAN backups from disk are taped weekly. Each quarter the tape contents are dumped onto the disk on the Test server and a new Test instance is created. Since this is a quarterly task and had been done many times before I was sure the documentation that was already in place was sufficient. But there was a change in the DB this time which could have caused issues and I wouldn’t know it until I began the restore.

I had all the backup pieces for that day’s RMAN backup. I needed to begin the restore. The restore in itself is simple. Since Prod and Test databases have different file locations, I needed to be careful in letting RMAN know where to restore the files to. If the restore is being done on the same server as where there is source, then this becomes necessary to let RMAN know that the new DB files need to be restored to a different location and the DB should be created using those new db files. Else RMAN may overwrite the current datafiles. Then you may not want to live in your own skin! In our case, however, since the Test and Production servers are different we are least likely to trifle with the Production db files. But since the requirement is to restore the datafiles to a different location than what is in the Production, I use SET NEWNAME FOR DATAFILE ... command.

1. Confirm the tape dump contains all the required backup pieces. You may do this in many ways. The easiest way is to look at the RMAN log file for that day’s backup. That will contain all the Backuppiece names for that backup. Additionally, you may also query the RMAN repository in the below fashion. I was looking at the 24-FEB-09 backup to be restored from and the output below is trimmed to show only that day’s backup.

RMAN> list backup summary;

List of Backups
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– ————— ——- ——- ———- —
224 B F A DISK 24-FEB-09 1 1 NO TAG20090224T180002
225 B F A DISK 24-FEB-09 1 1 NO TAG20090224T180002
229 B F A DISK 24-FEB-09 1 1 NO TAG20090224T182832

RMAN> list backuppiece tag='TAG20090224T180002';

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-
223 224 1 1 AVAILABLE DISK G:\RMAN\BACKUPS\B_78K86D13_232_1

RMAN> list backuppiece tag='TAG20090224T182832';

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
——- ——- — — ———– ———– ———-

Ensure the above Pieces are available in the tape dump.

Caveat: This didn’t list all the backuppieces to me. The backuppieces with Archives are not listed here and I am not sure (yet) how to do it. I will find out and update the blog very soon. There definitely should be a way to query all the backuppieces that were generated for a day’s backup.

2. Connect to RMAN and restore the backup controlfile from the backuppiece.

Starting restore at 06-MAR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=212 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-MAR-09

3. Mount the Database using the restored controlfile.

database mounted
released channel: ORA_DISK_1

4. Execute the below command entirely in a single RUN... block.

E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\REDO01.LOG'' ";
E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\REDO02.LOG'' ";
E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\REDO03.LOG'' ";
RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";

You will change the datafile locations in the above script to suit to your requirements.

The SET NEWNAME ... command is used to redirect the RMAN utility to restore the datafiles to a location pointed to. Since the datafile locations are different in Source and Target instances this is necessary. Else RMAN will restore the files to the same location (or directory path) of Production DB file locations.

The redo log files have to be renamed in the controlfile. Hence the ALTER DATABASE RENAME FILE ... command.
The above command should complete the restore of the DB.

5. You may open the DB manually.


The restore is complete. Oh, I also had to recreate TEMP files.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ptc\Windchill_9.0\ocu\ORADATA\WIND9\TEMP01.dbf' size 500M;

Below is an additional issue I had seen while doing this refresh and this error should not arise to anyone doing this task under already specified conditions. In my case, I hadn’t noticed that the 10g ORACLE_HOME versions of Production and Test were different. Production is and Test is Ideally, the ORACLE_HOME from Production should have been copied onto Test server too, but in my case I didn’t do it. This was the unforeseen issue I was referring to at the beginning of my post.

ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Instance terminated by USER, pid = 2360
ORA-1092 signalled during: alter database open resetlogs...

This error was because the restore was happening, in my case, between two different DB versions. I will need to upgrade the DB before I can open the DB.
Here is what I did:

Connect to SQL*Plus
SQL> conn /as sysdba
SQL> @?/rdbms/admin/catupgrd.sql
SQL> @?/rdbms/admin/utlrp.sql

Ensure there are no errors mentioned in the Upgrade log. This is very important.


This fixed the issue. Do let me know your experiences with RMAN too.

March 6, 2009

Yellow Bar

Filed under: Jinitiator,Script — Vikram @ 12:08 am
Tags: ,

Here I discuss a way to automate the fix for yellow-bar resolution that I had used for one of my clients. This involves very minimal end-user intervention and introduces a windows batch script that will do the dirty work for us.

Yellow Bar‘?
When Oracle Applications is launched, Oracle expects its code to be executed in a trusted environment and so uses JInitiator to run Java applets. The features of the applet are extended in a trusted mode. Rather, the features of the applet are restricted in a non-trusted mode. The Yellow Bar at the end of the applet window is how the applet conveys you that the it is not running in the trusted mode.

A basic symptom which is most often reported by the end-users is their inability to copy/paste data from the forms onto their notepads/editors on Desktop client. When not in trusted mode, applet doesn’t allow the data to be accessed in that mode.

Let the applets run in the trusted mode. Yellow bar will disappear.

How to run the applets in the trusted mode?
The applets are run in trusted mode if they are digitally signed by a digital certificate.

Each JAR file is signed with a digital certificate. The certificate is usually in the location $APPL_TOP/admin/appltop.cer on the middle_tier. When an applet is downloaded for execution, Jinitiator verifies the certificate of that applet and checks for its availability in its database. If the certificate is registered with the jinitiator the applet can be trusted and so the users wont see the yellow bar.

A typical way of resolving yellow bar is to uninstall JInitiator on the local PC and launch the application, which will bring along with it the identitydb.obj file that contains the certificate. The applets already are signed with the certificates in the identitydb.obj file and so the yellow bar should disappear.

Another way of resolving yellow bar is to import the certificate of the application that is complaining Yellow Bar into the existing identitydb.obj file. This is a more convenient method but involves the end user to perform a few steps on his PC. However, with a little script one can automate this procedure and not overwhelm the end-users by making them do things beyond their scope.

In short the steps to import the digital certificate into the existing identitydb.obj file are below:
1) Copy the $APPL_TOP/admin/appltop.cer file from the Application node onto the local PC.

Copy the file into the "C:\Program Files\Oracle\<JInitiator version>/bin directory.
The <JInitiator version> in the above path should be replaced with the version of the Jinitiator the application uses in the MIDDLE_TiER.
Example path: "C:\Program Files\Oracle\JInitiator\bin"

2) Open a command prompt on the Windows Desktop client and change directory to “C:\Program Files\Oracle\JInitiator\bin”

Start --> Run --> cmd

Then type the below commands in the cmd prompt:

C:\Documents and Settings\Vikram> cd "C:\Program Files\Oracle\JInitiator\bin"
C:\Documents and Settings\Vikram> javakey c <name> true (creates a new trusted applet called <name> in identitydb.obj)
C:\Documents and Settings\Vikram> javakey ic <name> appltop.cer (signs the new <name> trusted applet with the new certificate appltop.cer)
C:\Documents and Settings\Vikram> javakey -l (displays all the trusted certificates)

3) Clear browser cache

4) Clear JCache directory contents in the “C:\Program Files\Oracle\JInitiator\JCache” directory

5) Launch the application in a fresh browser.

The above steps need to be performed on each client Desktop and if there are 500 users, then 500 users will be asked to perform these steps on their Desktop clients. It is asking too much of course. There is a simpler way to automate this procedure for all users and lets see how.

The below steps need to be performed on the MIDDLE_TIER
1) Create a new directory and copy into it the certificate that needs to be imported onto the client desktops.

$ mkdir yellow
$ cp $APPL_TOP/admin/appltop.cer yellow
$ cd yellow

2) Open the editor (vi/emacs) and paste the following code.

copy appltop.cer "C:\program files\oracle\jinitiator\bin"
cd "c:\program files\oracle\JInitiator\bin"
del /Q "C:\Program Files\Oracle\JInitiator\jcache\*"
javakey c SAND true
javakey ic SAND appltop.cer
javakey l

Save the file as yellow.bat.
Note, there are two things that you need to remember while using this code:

    Change the JInitator path to suit your needs
    SAND is my DB SID. You may change it to yours.

3) Zip the current directory and place it in $OA_HTML directory
Issue the below command from the same directory where you saved the yellow.bat and appltop.cer files.

$ zip -r $OA_HTML/ ../yellow
$ ls -ltr $OA_HTML/yellow*

This is complete. Now all remains to be done is for the users to do the below to fix the yellow-bar issue on their respective clients boxes.

The below steps are performed by individual end-users

4) Launch the URL: http://hostname.domainname:port/OA_HTML/
The browser prompts you to save the file. Save the file in any location of your convenience.

5) Unzip the contents and double-click on the yellow.bat file within the unzipped directory.

6) Launch the application in a fresh browser. Yellow bar should not be seen anymore, hopefully.

Thank you for reading this. You may leave a comment here if you have any doubts and I shall respond.

Next Page »

Blog at