Archive Page 2


Have you paid a visit to the Oracle Wiki yet?

Oracle wiki is out since a few weeks only.

It’s all new and shiny! This is going to be very exciting.

Have you added your contribution yet? There’s a whole new ground to cover here!
As long as the rules are followed that is.

What is not quite clear for me yet is what is expected to be seen there?
I’ve got that funny feeling that I have a lot to say but no idea if it fits what is expected…

If it’s technical then there’s OTN.
If it’s trouble then there’s metalink.

What is Oracle wiki?

The site tells us:

«5. How does the Oracle Wiki complement Oracle’s corporate Web site and the Oracle Technology Network Web site?
The aforementioned sites, although they do contain community-derived content, primarily contain information provided by Oracle, and it’s structured the way we like it. The Oracle Wiki complements this official content with that generated completely out of the community–think of it as a free-form “Oracle parallel universe” built by customers and partners.»

What I understand is: Whatever you like and the way you like. If it doesn’t fit the general approbation, the community will quickly bring you back into its reality.

I’m waiting a bit to see what’s going on there then I’ll start doing some work.


Stuck with 2 different networks

Recently, while working on dataguard we had a problem configuring it accross networks between our production site and our DR site.
After a few tests, it seems that the configuration works fine locally but not through the lease line.
So, to bypass this, we wanted to take the standby server back to production site and configure dataguard locally.

This raises a few problems.

1) We can’t modify the IP address of Primary or Standby server
2) The network on the production site is different from the network on the DR site.

Basically, when we bring it to the production site, it’s not possible to ping or connect to the standby server.

The solution is very simple:

The production site network is 172.30.XX.0/
The DR site network is 172.30.XY.0/

Prerequisite: Both servers need 2 NICs

What we are going to do is the following:

On primary:
Configure a 2nd NIC to use an IP address of the DR site: 172.30.XY.34

On standby:
Configure a 2nd NIC to use an IP address of the production site: 172.30.XX.12

On primary:
Type the following command on command line:
C:\>route add 172.30.XY.0 MASK 172.30.XX.12

On standby:
C:\>route add 172.30.XX.0 MASK 172.30.XY.34

If we now try to ping the IP address 172.30.XY.XXX of the standby database from production site, we will receive an answer.
What we are telling to the production server is: If you are sending a packet to the DR Site, then send it to the address 172.30.XX.12 instead. The same applies to the standby server.

With this route added, the production server won’t be visible to the DR site anymore since every packet are routed to a different location. So use it with caution.

Finally, when all is done, what is left to do after the configuration is to delete the route:

On primary:
C:\route delete 172.30.XY.0

On standby:
C:\route delete 172.30.XX.0

Disable the secondary NICs. All is back to normal.


Manually create a standby database

This post talks only about manually creating a standby database.

Configuring Dataguard CLI (DGMGRL) and dataguard through the Grid Control will be discussed later on in other subjects.

Since this blog is under oracle 10g, I will consider the databases to use spfiles.

To ease the difficulty, I will also consider that the folder structure on the primary database and the standby database are the same.

I will also try to describe the expected error when something is not done properly

The creation of a standby database consists of 3 parts:

1) Creation of standby spfile, controlfile, password file

2) Connectivity configuration (Listener, tnsnames)

3) Standby restoration, recovery and management



The databases’ instance names are: THTEST10

The primary database unique name is: THTEST10

The standby database unique name is: THTESTDG

Creation of standby spfile, controlfile, password file

1) Create the SPFILE for production and standby

On the production database:

Connect to sqlplus:

C:\> set oracle_sid=thtest10

C:\>sqlplus “/ as sysdba”

SQL>create pfile=’initthtest10.ora’ from spfile=’spfilethtest10.ora’;

From the file generated, initthtest10.ora, we are going to modify a bunch of parameters before converting it into spfile again.

If they don’t exist, add:






modify the archiving parameter:

*.log_archive_dest_1=’location=g:\oradata\THTEST10\arch valid_for=(ONLINE_LOGFILE,ALL_ROLES) db_unique_name=THTEST10′

The point is to add the optional values VALID_FOR and DB_UNIQUE_NAME. If they are missing, they might result into errors at the DGMGRL configuration time.

Make sure the host is specified in the parameter below:


Most of these parameters can be modified using the command when using a spfile.

SQL>alter system set <parameter name> = ‘value’ scope=both;

But some parameters, such as db_unique_name will require to modify the parameter file before hand, hence a shutdown of the DB is required.

It’s not possible to modify it while the DB is on.

On the standby database

Copy the modified init file and modify it again to fit standby requirements.

*.LOG_ARCHIVE_DEST_1=’location=g:\oradata\THTEST10\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=THTESTDG’

Finally, don’t forget to convert the pfile to spfile again and make sure both your primary and standby starts with a spfile.

2) Create the control file for the standby

On the production server, type the following command:

C:\>sqlplus connect / as sysdba
SQL>alter database create standby controlfile as ‘d:\somewhere\control01.ctl’;

Move the control file to the location specified in your spfile. If there’s more than one, just make a copy of the freshly generated one, and rename it.

2) Create the password file for the standby

Simply copy it from production to standby.

Connectivity configuration (Listener, tnsnames)

Notice the green bold service names in the pfile in the step above.

These will be specified in the tnsnames.ora and through it, our databases will communicate.

It is important to have both databases specified on both servers.

The tnsnames.ora file should look like this:




(ADDRESS = (PROTOCOL = TCP) (HOST = primaryserver)(PORT=1521)









(ADDRESS = (PROTOCOL = TCP) (HOST = standbyserver)(PORT=1521)




(UR = A)



Notice the value (UR=A) in the CONNECT_DATA of the tns entry THTESTDG.WORLD.

This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.

Don’t forget to create the listeners on both servers.

Standby restoration, recovery and management

Finally the interesting part.

Before starting to work on the standby, take a hot backup of your primary database:

C:\>set oracle_sid=thtest10

C:\>RMAN target /

RMAN> backup database plus archive log not backed up 1 times;

Copy the backups to the standby. The folder structure for your backups should be the same as well to simplify.

On the standby server

Create an oracle service for your standby database with the oradim command

C:\>oradim -NEW -SID THTEST10 -startmode manual -shutmode n

Since the spfile is already located in ORACLE_HOME\database it doesn’t need to be mentioned.
Same for the password file which should be here already from the 1st step.

Restore and recover your database using RMAN

C:\ set oracle_sid=thtest10

RMAN target /

RMAN> restore database;

… (a log of things happens here)

RMAN> recover database;

… (archive logs are applied)

Now that the database is recovered, we will need to copy the redo logs from production to the standby.
That is required because this standby database will not be open with the resetlogs command therefore the redo logs will not be generated.
So, go back to production and copy the redo logs.
Make sure they are not accessed in order to copy it. To check the status of your redo log groups, connect as sysdba and use the following query:

SQL> select group#, status from v$log;


You can copy the groups with status INACTIVE. The group with a status CURRENT can’t be copied right now but we will force switch to another group:

SQL>alter system switch logfile;

SQL> select group#, status from v$log;


Now it’s ok to copy group# 3 to the standby server

Switching to automatic recovery

Previously, to recover the database with RMAN we had to set the database to mount mode. We must now shutdown and go into mount standby mode. That’s how it works:

SQL> startup nomount;

SQL>alter database mount standby database;

SQL>recover automatic standby database;

You should get ORA- messages: ORA-00279 ORA-00289 ORA-00280 ORA-0027. This is a normal behavior. The OS message saying that it’s not finding a missing archive log is normal too since at a certain point it will reach the latest archive and the next one is not generated yet. Result: it fails but we like it.

Cancel the operation by simply typing cancel. When the media recovery is canceled, you should get an error message as follow:

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: ‘D:\ORACLE\ORADATA\OE\SYSTEM01.DBF’
ORA-01112: media recovery not started

This is normal too. There will be no open resetlogs anyway.

We can now move to:

Switching the standby into managed recovery mode

SQL>recover managed standby database;

This command will not return the hand to you.

Any further sql query will have to be run from another command line.

Testing your newly created standby database

Go to the primary database and type the command:

SQL>alter system switch logfile;

This command will generate a new archive log file.

You should see it’s generation in the alert log.

You should be able to see the standby database fetching it and applying it to the standby database.

You should also see the newly moved archive log in the archive folder.

If it’s not working

Investigate the following:

Are the listeners working?
Are the tnsnames.ora configured to reflect the parameters specified in log_archive_dest_2?
Is the parameter *.service_names=’THTEST10.WORLD’ reflecting the service_name specified in tnsnames.ora (in connect_data)?

These should be a good starter. This process is very rigorous. Read the error message carefully and comply to what it requires.

We will see next how to configure dataguard to manage the primary and standby database.



Put a password to that Listener

The oracle listener can be the object of attacks from someone willing to take control of the database.

Putting a password on the listener is easy so let’s get at it.

Open a console:



If you got some instances described then this is your current listener. The default name is listener and a potential hacker is well aware of that.

It’s good to create the listener with a different name.

If you don’t know your listener’s name, go to the services and look for OracleListenerSomelistenername. Your listener name is Somelistenername.

LSNRCTL>change_password Somelistenername

old password: There was nothing so we just press enter.

new password: Agoodpassword

retype new password: Agoodpassword


The save_config command will add the password encrypted in the listener.ora file located in ORACLE_HOME\NETWORK\Admin

If that command haven’t been performed then no password have been added.

Tips, courtesy of caffeine-addict who should recognize himself if he walks by


How to perform and recover from a cold backup

Back to basic.

The cold backup is the easiest backup that could be performed.

It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.

It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.

Perform a cold backup

A cold back requires a clean shutdown of the database.

shutdown immediate or normal are the only shutdown allowed.

A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.

The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).

When the clean shutdown is done, copy the oradata folders to a new location.

After the files are copied, the database can be started up.

Recover from a cold backup

Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.

1) If the service doesn’t exist yet, issue the oradim command:

oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword

If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.

2) Copy the oradata folders to the exact same location from primary to the server to recover.

If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.

3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.

In the case a data file has been moved to new location, the database must be started in mount mode:

C:\set oracle_sid= mySID

C:\sqlplus / as sysdba

SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)

and a rename command must be issued for each files relocated:

SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;

SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;

SQL>alter database open;


The art of communicating outside of the IT room

This title aims at describing a situation which occurs often between IT and any other non technical department.

IT people have a reputation of geeks, inapt to communicate with anything else that doesn’t have any flashing lights on it.

It’s not so true, like all legends its exagerate. Still, legends and urban stories come from true histories…

Note, before bashing starts, I’m generalizing. There is of course a lot of competent and very able IT people. This post is for the others.

I have to admit we have a difficult task. We often need to explain to people who has no IT background nor skills, how this is going to work…

My point today is very high level.

This is Mr Bob. He is an accountant, has no IT skills except Excel and the computer looks like black magic to him. Mr Bob do know however how all his numbers plugs into his accounting process.

This is A.J. A.J is a developer. He speaks a very technical language and understand algorithm and boolean logic.

Mr Bob has asked A.J to develop an application which would be able to control some costs at the very beginning of his data entry process.

The requirements has been done smoothly and AJ got it pretty easy. But here is when the problems starts, at test levels.

Mr Bob is facing a black box. He knows what checks has to be done because he asked for it, but when something happens (a minor bug), he is bound to doubts, and fear. This software is going to be on production, crushing numbers into his accounting. This is scary.

As a developer/ DBA / Sys, network Admin, we should strieve to clear out those doubts and fear from the “Users”. We have to understand that standing in front of a black box is really not fun. So, explaining a little bit… Or a little more if required can go a long way into making Mr Bob confident and make the project a success.

These doubts and fears might come out under the form of questions. Challenging the logic in the application or the developer sometimes. But in the end, the real problem is: Mr Bob is scared. So, skip the complexe words that Mr Bob doesn’t understand.

 If AJ has messed up something that is not making it work properly. He should say: “I’m sorry. I did XXX. It was doing YYY in the application. I’ve fixed it by doing ZZZ. Try again it will work”. Use print screen if it makes it easier to explain!

What AJ should not say (as is very often the case): “Oh, I see what it is. It’s fixed now. Can you try again?”

Not being aware of Mr Bob emotions, won’t prevent a project from being done, but being aware of it would make the project much more smooth and ease tensions between the partys.

Here are the qualitys which I think are required to turn a good IT person into an excellent IT person:

1) Empathy (We feel your pain).

2) Ability to explain in simple words.

3) Confidence. (Particularly for DBAs in critical situations. Someone scared to death might call because his database is not working, the whole company is freezed. Keep cool.

Showing confidence and saying very simple things like “I see what it is. Let’s check what’s going on”. Keep the tone very calm. you KNOW what’s going on. You KNOW what you are doing. This would cool the tension straight away and your interlocuter’s face might even change back from blue to normal again.)

The list, I suppose, can go a long way. But then, nobody is superman as well.

If you think there’s another non IT skill which should be in this list, then comment the post.


The Oracle patching best practice

On windows, patching an Oracle database can be tricky.

Very often for example, the dll file will be used by some process which is a bit… Undefined. So to make sure everything run smoothly, the following process must be used.

It should in fact be used as a best practice on windows.

Stopping Oracle services might not be enough.

– the instances,
– the Intelligent Agent,
– the OLAP agent,
– the Oracle Management Server,
– the TNSListener,
– HTTP server.

– SNMP service,
– MSDTC (Microsoft Distributed Transaction Coordinator) service,
– Microsoft IIS (admin) service,
– Microsoft COM+ Event System,
– MTSrecovery service,
– BackupExec agent,
– ArcServe Agent,
– IBM Director Support Program.


The list above comes from oracle support.

  1. Stop OracleService, Listener, DBConsole and anything else related to Oracle + additional services as stated above.
    1. Change the services start mode to manual
  2. Go to system>Environment variable.
    1. Backup PATH environment variable to notepad
    2. Remove all Oracle path from PATH (I had c:\oracle\product\102\bin)
  3. Reboot the server
  4. Install the patch:
    1. Make sure to select the correct oracle_home. By default, it will install the patch in a new home.
  5. Put the oracle path back in PATH environment variable.
  6. start the oracle service using the following command: C:\>oradim -startup -sid [SID] starttype srvc
  7. Start the listener via windows service manager
  8. In SqlPlus, log in as sysdba:
    1. C:\>set oracle_sid=[SID]
    2. C:\>sqlplus / as sysdba
    3. SQL>startup upgrade
    4. spool d:\somewhere\upgde.log
    5. @[oracle_home]\RDBMS\admin\catupgrd.sql (depending on the machine it can take up to 40 minutes)
    6. spool off
    7. Check if there is any ORA- error (this is long because there are a lot in the comments)
    8. shutdown
    9. startup
    10. @[oracle_home]\RDBMS\admin\utlrp.sql (takes up to 10 minutes)
    11. @[oracle_home]\RDBMS\admin\utlrp.sql (takes about 1 minute)

July 2018
« Mar    


Blog Stats

  • 515,988 DB lovers