Archive for October, 2007

31
Oct
07

Oracle Database 10g Utilities

I was going through Oracle Techology Network (otn) and found this:  Oracle Database 10g Utilities.

That is a great ressource for everything new in Oracle 10g. I think I know what I’m going to do this week-end.

I mean… Over than installing Linux Kubuntu Gutsy Gibbon on my new hard disk after the previous disk failed.

31
Oct
07

The world was shouting out loud for some explanations on ORA-12514

I was having a look at my blog stats today and it seems that most of the traffic I got from search engines are triggered by ORA-12514…

Just a simple look at the following table, coming straight from the stats:

ORA-12514 23
ora-12514 13
ORA-12514: TNS:listener does not current 5
dbsnmp password 3
TNS:listener does not currently know of 3
DG_BROKER_START=true scope=both; 2

That’s 78% of the search on this blog.

So what I’m going to do, instead of just a solution to ORA-12514, is a detailed explanation of how connectivity works with Oracle: Listener, sqlnet.ora and tnsnames.ora

I’ll also include some basic best practices as well. All that with as few difficult words as possible and as many simple words as possible.

I don’t pretend to be an Oracle guru but I would be really happy to provide my small piece of work to the greatest community online.

Soon. On this blog…

30
Oct
07

Configuring dataguard to manage the standby database

It’s possible to have dataguard configured using a grid controller but today I will explain how to configure using command lines.

To anyone using this how-to, I strongly suggest reading this post.
You will find a list of parameters which have a big impact on the smoothness of the configuration of dataguard client (DGMGRL)

pre-requisites are:

1) You got a standby database synchronizing with you primary DB without any problem.
2) The parameters listed in the link above are specified.

Create dataguard configuration

The command can be run on either the primary or the standby server because the parameter files for dataguard will then be transfered to the standby database’s host.
I’ve choosed to perform it on the primary server.

C:\>set oracle_sid=<SID>
C:\>DGMGRL /
DGMGRL>
Connected.

DGMGRL>create configuration ‘somename’ as
primary database is ‘primary’
connect identifier is primary.world;

In this command, the word ‘primary’ is the db_unique_name value specified in your parameter file.
If you are not sure about it, you can connect to sqlplus as sysdba and type:

SQL>show parameter db_unique_name;
note: If you are not sure about the exact parameter name, type only a part of it. It will display any parameter featuring that piece of string typed.

DGMGRL>show configuration;
This command will display the configuration of dataguard for the specified primary DB. Obviously the status is “DISABLED”

Adding the standby to the configuration

DGMGRL>add database ‘standby’ as
connect identifier is standby.world
maintained as physical;

Database “standby” added.

DGMGRL>show configuration;
The configuration will now display both the primary and standby databases in your configuration but the status is still disabled.
It’s now time to enable the configuration.

DGMGRL>enable configuration;
Enabled.

DGMGRL> Show configuration;
The status is now turned to SUCCESS.

But this is a perfect world…

How to configure dataguard again if the first try fails

In a world where your first trial fails for some reason, returning to the initial state (a primary, a standby, no dataguard configuration) is required and it’s not easy.
If you are trying to enable the configuration and the command hangs, then you have a problem.

All the previous steps, including the modification of parameters in the SPFILE don’t require a shutdown of the DB.
But to reset the configuration, a shutdown of the DB will be required.
If you are working on a critical system, a shutdown will have to be scheduled off peak hours etc… etc…

What the command “enable configuration” did is the generation of configuration files, located in ORACLE_HOME\database. It’s the same folder as the SPFILE.
It will also transfer those files to the standby host. The reason is simple, when the production server fails, I would love to be able to still use what is left.
And what is left is my standby database and server.

The files are named as follow:
DR1<INSTANCE>.DAT
DR2<INSTANCE>.DAT
hc_<instance>.dat

Deleting those files would mess up with a dataguard configuration at best but will not harm a critical system.
But as it turns out, they are not easy to delete because they are accessed by oracle services.
DR1 and DR2 are easily removed.

To remove this hc_ file:

connect to SQLplus:

C:\>sqlplus / as sysdba
SQL>alter system set dg_broker_start=false scope=both;

When this is done, shutdown the database and delete all the .dat files specified above.
The same process must be performed on the standby server and database.

When the files are deleted:
Connect to sqlplus again and set the parameter back to “TRUE”
SQL>alter system set dg_broker_start=true scope=both;
Perform the same on the standby.
It is now possible to try again to enable the configuration.

To complete this explanation on the configuration of DGMGRL, here is a list of parameters which were very important to the success of the configuration

*.db_unique_name=’TESTDG’
*.local_listener=’(ADDRESS=(PROTOCOL=tcp)(HOST=standbyserver)(PORT=1911))’
*.log_archive_config=’dg_config=(TEST10,TESTDG)’
*.LOG_ARCHIVE_DEST_1=’location=g:\oradata\TEST10\arch VALID_FOR=(ALL_LOGFILES, ALL_ROLES)db_unique_name=TESTDG

*.log_archive_dest_2=’SERVICE=TEST10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=THTEST10
*.dg_broker_start=TRUE
*.standby_file_management=’AUTO’
*.fal_server=’TEST10.WORLD’
*.fal_client=’TESTDG.WORLD’

On my first configuration, all the parts marked in Orange were absent of my parameter files. That was all I had in the how-to I used.
The synchronization of the primary and the standby worked that way.
But when it comes to configuring Dataguard, it was a complete failure.
As it turns out, the primary cause of all my problems were the missing parameters and options.

Adding the databases to the Grid controller

If later on , for more control a grid controller is installed (makes sense isnt it ^^). All that will be needed is to install the controller agent on the primary and the standby server.
The grid controller will detect right away that there is a standby database managed by dataguard and it will be possible to view it through the grid controller.

Enjoy! It has been a great project. I’ve enjoyed a lot going through this.
There has been a lot of frustration on the way due to the difficulty of getting information on how to do this. Particularly after it failed the first time.
I sincerely hope that this document will be useful to a lot of people.
Succeeding gave a great feeling of achievement.

If there’s anything wrong or unclear, just let me know in the comments. Let me know as well how it went for you too.

18
Oct
07

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.

17
Oct
07

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/255.255.255.0
The DR site network is 172.30.XY.0/255.255.255.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 255.255.255.0 172.30.XX.12

On standby:
C:\>route add 172.30.XX.0 MASK 255.255.255.0 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.

16
Oct
07

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

——

Preliminary:

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:

*.db_name=’THTEST10′
*.db_unique_name=’THTEST10′

*.log_archive_config=’dg_config=(THTEST10,THTESTDG)’

*.log_archive_dest_2=’SERVICE=thtestdg.world VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=THTE
STDG’

*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’

*.standby_archive_dest=’g:\oradata\THTEST10\arch’
*.standby_file_management=’AUTO’
*.dg_broker_start=TRUE
*.fal_client=’THTEST10.WORLD’
*.fal_server=’THTESTDG.WORLD’

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:

*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=testserver)(PORT=1910))’

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.

*.db_unique_name=’THTESTDG’
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=standbyserver)(PORT=1911))’
*.log_archive_config=’dg_config=(THTEST10,THTESTDG)’
*.LOG_ARCHIVE_DEST_1=’location=g:\oradata\THTEST10\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=THTESTDG’
*.log_archive_dest_2=’SERVICE=thtest10.world VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=THTEST10′
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.dg_broker_start=TRUE
*.standby_file_management=’AUTO’
*.fal_server=’THTEST10.WORLD’
*.fal_client=’THTESTDG.WORLD’

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:

THTEST10.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = THTEST10.WORLD)

)

)

THTESTDG.WORLD =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVICE_NAME = THTEST10.WORLD)

(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;

GROUP# STATUS
————————–
1 INACTIVE
2 INACTIVE
3 CURRENT

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;

GROUP# STATUS
————————–
1 CURRENT
2 INACTIVE
3 INACTIVE

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.

 

04
Oct
07

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:

C:\>LSNRCTL

LSNRCTL>status

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

LSNRCTL>save_config

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