Archive for the 'Uncategorized' Category


I’m moving to Paris

Hi fellow Thetendjee readers.

Today, no 10g stuff but a personal news.

I’m leaving Bangkok, Thailand to go to Paris, France.
I will be landing there on 23rd of December.

The position is Senior DBA at Mondial Assistance Group.
I will be working with much more databases on different versions (9i and 10g) and with different systems.
It will be primarily SUN.
I’m really happy and excited about this new position.
I will have the chance to move around Europe as well because I’m in charge of Oracle databases for our Business Units in Europe.
More databases, more systems, more responsabilities and more experience –> Ultimately more to write on this blog!

I will have the opportunity to polish my *nix scripting skills.

I have had a great time in Thailand and I want to stress to anybody reading this that working in Thailand is an awesome experience.
If you ever got a chance to go work there, don’t think twice.


Did you buy an Oracle enterprise license? Then you should know that…

The more this blog is going on, the more I’m thinking it’s intended for IT people whor are not DBAs.

There are a lot of people like that in many companies. They didn’t really wanted it but they got that powerful baby in their hands and don’t really know what they can or can’t do with it.

So following the concept, here is a little tip for them, because it seems many people don’t really know that.

 If you’ve bought an Oracle entreprise license, you got the right to create an Oracle standby server without having to buy a new license for that standby server. The Entreprise licensing includes the cost of a standby server.

Therefore use it! Do a standby, it’s the best backup you could have.


Duplicate a database using RMAN

Like I said before, RMAN is a great tool. It is possible to backup the database and recover with simple commands or create a duplicate database using that tool.

I’ve been trying the duplicate command recently.

You will need this duplication ability if you got a database for training purpose. This database must be fresh to train your users with the latest customers information, products etc… and if it’s not possible to perform a cold backup because your system is critical.

To help yourself, use this document: Backup and Recovery Advanced User’s Guide

I’m going to call the live db’s host PRIMARY and the duplicated database’s host AUXILIARY. Those are the terms used by RMAN, so let’s get used to it.

To make things simple, it is a good idea to use the same server architecture on both server. This will avoid the renaming of files.

Below is a list of things to do:

1)On the PRIMARY host

  • add the option UR=A in the CONNECT_DATA part of your tnsnames.ora entry to connect to your auxiliary database. It should look like this:


  • Perform a backup with RMAN using the following command: backup database plus archivelog not backed up 1 times;
  • Move the backup folder from PRIMARY  to AUXILIARY host. Use the same folder name as on the PRIMARY host.

2) On the AUXILIARY host

RMAN does not recreate the folder structure as it is on production automatically (sadly) so it has to be done manually. They are:

  • Datafiles folders
  • Control files folders
  • Traces folder
  • Archives log folder
  • pfile (if you do it like me, you got a pfile folder where the SPFILE is located. Then in oracle_home\database you got a file called initSID.ora and inside this file the following line: SPFILE=[SPFILE location\SPFILESID.ora]

The oracle service must be created on first hand. So, copy the SPFILE from PRIMARY to AUXILIARY and move it in the same location as on your PRIMARY server.

Then create the service using oradim command:

If you want to create a new password for the sys user the use the -syspwd option as in this example. Otherwise, just copy the password file from PRIMARY. It’s located in [ORACLE_HOME]\database

C:\>oradim -NEW -SID [as on PRIMARY] -syspwd “acomplexepassword” -startmode auto

Create your listener using net services. This is pretty straight forward, so I won’t document it here.

Create your tnsnames.ora and sqlnet.ora for this new database. If you want to use implicit connection when logging in locally, use the following in your sqlnet.ora:


With the BEQ option, it is possible if logging in on windows with a user which belongs to ora_dba group, to connect locally without providing usernames and passwords.

When this is done it is possible to connect to the database with SQLplus and start the database in nomount mode.

C:\set oracle_sid=SameAsPRIMARY

C:\sqlplus / as sysdba (that’s thanks to BEQ in sqlnet.ora)

SQL>startup nomount

if the the database won’t start with an error telling it’s not finding the spfile, then point to it

SQL>startup nomount spfile=path\SPFILESID.ora

3) Go back to PRIMARY

If everything has been prepared carefully, you should be able to connect to PRIMARY and AUXILIARY with RMAN from PRIMARY

C:\set oracle_sid=PRIMARY

C:\rman target / auxiliary sys/

As you can see, we are connecting to PRIMARY without username and password but because we connect to AUXILIARY remotely, username and password is required.


With this command we allocate 3 auxiliary channels which will be used for the duplication.

If you have been using the same SID for AUXILIARY as the one from PRIMARY, then it must be a bit scary and confusing (hence the SameAsPRIMARY naming). It’s reassuring to know that the duplication won’t work if the database is open. RMAN will just say that to run this command the database must be in nomount mode and return an error. Plus the command will connect only to auxiliary (specified at connection time) using the tnsnames value used in the connection.

Since RMAN is using the production repository to duplicate the database, it will try to recover the data with the latest archive logs created on production. But they have been moved manually and not existing in the AUXILIARY environment. If RMAN could move the archive logs continuously it would be a standby DB. So RMAN sends an error saying that it’s not finding a couple of archive logs.

The duplicate command must be run with the UNTIL TIME option.

I got a lot of trouble using my own dates because it has to fit the NLS_DATE_FORMAT parameter. I’ve tried to play with the NLS_DATE_FORMAT to fit my format but it wouldn’t work anyway.

The best way I’ve found is to specify UNTIL TIME sysdate – X.

sysdate -1 is one day before. The duplication would be one day old. 1/24 = 0.04 would be one hour before current state of PRIMARY database. I haven’t tried the 1/24 in my UNTIL TIME option. If someone try this UNTIL TIME sysdate – 1/24, let me know the result. I would be interested.


How to switch between SPFile and PFile

Any DBA who worked on oracle 8i knows where to find init[sid].ora.

The SPFile is located in the same folder just like the Pfile.

Except… It’s not manually managed. It’s managed by oracle. This is done to avoid human error in parameters.

There can be problems in parameters though. During the migration, a parameter (DB_BUFFER_SIZE), from the 8i era was left in the parameter file and was conflicting with another one (DB_CACHE_SIZE) in the parameter file.

When restarted, the database couldn’t even mount…

Don’t worry, when you get that error, there’ll be a list of conflicting parameters.

Usually, you should be able to get into OEM and remove the bad parameter then it would be possible to start again. But it was my luck and OEM was not working too.

There’s only one way out of it. It is possible to get back to the old 8i style and use an init.ora file which can be modified manually.

To do so, connect as sysdba on command line:

C:\SET ORACLE_SID = [Your Sid]

C:\SQLPLUS / as sysdba


As it says, it will create the Pfile from the SPfile. Then it is manually manageable. When the culprit is removed, simply do the opposite thing:


This will create a new spfile from the modified init[sid].ora

Now all what is left, is to start the database


March 2018
« Mar    


Blog Stats

  • 514,673 DB lovers