« December 2007 | Main | February 2008 »

January 2008 Archives

January 2, 2008

How to generate series of AWR reports

By alejandro.vargas on January 2, 2008 9:52 AM

To checkup performance issues usually I do get awr and ash reports every 15 minutes along the period I want to check.

This script helps to automatically generate a set of awr reports: Run_AWR_Reports




| Comments (0) | TrackBacks (0) AddThis Social Bookmark Button

January 4, 2008

ASM Based Tablespaces Backup With Rman for Long Term Offline Storing

By alejandro.vargas on January 4, 2008 8:01 AM

This post is the answer to a conversation with a Senior DBA.

He asked me if it is possible to set a tablespace offline, use Rman to backup it, then remove the tablespace underlying datafiles, and, when needed restore the tablespace without performing recovery.

The idea behind is to be able to free the space used by tablespaces that we do not need to have online, but we need to keep them, usually for legal reasons, for a period of several years.

The standard solution would be to move and compress this data into historical databases, based on cheaper storage . The data transfer can be achieved using transportable tablespaces, cross-platform transportable tablespaces or data pump.

I did test two approaches of doing this: Using Transportable Tablespaces and using Offline Tablespaces.

Full details can be found here:  ASM Based Tablespaces Backup With Rman for Long Term Offline Storing

| Comments (0) | TrackBacks (0) AddThis Social Bookmark Button

January 6, 2008

MAA Seminar: Questions and Answers

By alejandro.vargas on January 6, 2008 7:00 AM

There were a couple of questions I did take home from the Seminar:

1. Which is the minimal retention time that can be used when using Flashback Database?
2. Is it possible to open a 10g Physical Standby read-write, perform application testing on it, and reinstantiate it as Physical
Standby using flashback technologies?
3. Which is the correct High Availability configuration for tnsnames.ora when using Data Guard?
4. How I can avoid redo shipping when the standby database is not available?
5. Which are 10g Data Guard Data Type restrictions?
6. Is it possible to use tape compression in top of Rman compression?
7. Is it possible to use Rman Convert command on a small endian ASM diskgroup mounted on a big endian server?
8. Is it possible to integrate Oracle and My Sql?
9. Is it possible to use Storage backups with ASM?

Here you can find the Answers:

| Comments (3) | TrackBacks (0) AddThis Social Bookmark Button

January 8, 2008

ASM Database Clones Naming Issues

By alejandro.vargas on January 8, 2008 10:01 AM

There are several ways to clone an ASM based database, some of them result in a directory structure that do not match the current database name, i.e, the source database directory structure is built this way:

+DATADG/proddb/datafile
+DATADG/proddb/controlfile
+DATADG/proddb/onlineredolog
... etc

When cloning we will keep the same directory structure and change the database name to testdb or devdb or repdb, some dba's do not feel comfortable having a "testdb" database on a directory like "+DATADG/proddb/..."

In this post I will mention 2 possible workarounds.

1) Using a generic unique_db_name to be shared by all clones
2) Creating a set of directories and moving the database oracle files over to it

1. Using a generic unique_db_name to be shared by all clones

The easiest one is to use a generic db_unique_name for the set of databases to be cloned.

For instance if the database to be cloned is a billing system we may set the db_unique_name at database creation to "billing", in this case we will get for all our clones a generic root directory named "billing" instead of one that is  the name of source database in the group.

When using Database Configuration Assistant (DBCA) you can set manually the parameter db_unique_name by choosing parameters -> advanced parameters configuration.

The static parameter  db_unique_name can be changed at any moment, it will require an instance restart to become active.

If you are using Oracle Managed Files (OMF) The new unique_db_name is used as root directory for any new oracle files created from the moment the new name is made active.

Following you can see an example of a database created with a db_unique_name='generic' and a db_name='redhorse'

SQL> select name from v$database;

    NAME
    ---------
    REDHORSE

    SQL> select * from global_name;

    GLOBAL_NAME
    --------------------------------------------------------------------------------
    REDHORSE

    SQL> select db_unique_name from v$database;

    DB_UNIQUE_NAME
    ------------------------------
    GENERIC
   
    SQL> select file_name from dba_data_files;

    FILE_NAME
    --------------------------------------------------------------------------------
    +DB1DATADG/generic/datafile/system.292.643421261
    +DB1DATADG/generic/datafile/undotbs1.293.643421325
    +DB1DATADG/generic/datafile/sysaux.294.643421347
    +DB1DATADG/generic/datafile/users.296.643421387


2. Creating a set of directories and moving the database oracle files over to it

This is a more laborious workaround than to set the db_unique_name of the clone to be the same as the db_name.

Once the clone is created you will manually create the directories matching the new name, and move the oracle files from the old directory to the new directory using rman, i.e.:
  • Create a set of directories reflecting the new database structure, in my case the original database was cpdg1 and the clone is cpdg2. 

(it may happen that your spfile is siting on an alias with the name of the database, you will need to remove the alias to be able to create the directories)

ASMCMD> ls cpdg1
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/

ASMCMD> mkdir cpdg2
ASMCMD> mkdir cpdg2/CONTROLFILE/
ASMCMD> mkdir cpdg2/DATAFILE/
ASMCMD> mkdir cpdg2/PARAMETERFILE/
ASMCMD> mkdir cpdg2/TEMPFILE

  • From now on every create of an oracle file on database cpdg2 will go to the cpdg2 directory, i.e.

SQL> select name from v$database;

NAME
---------
CPDG2

SQL> create tablespace users2;

Tablespace created.

SQL> select file_name from dba_data_files where tablespace='USERS2';

FILE_NAME
-------------------------------------------------------
+DB3CLNDG/cpdg2/datafile/users2.267.643260907

  • To move tablespaces based on the old directory cpdg1 set the tablespace offline and move the datafiles with rman

SQL> alter tablespace users offline;

Tablespace altered.

  • Start an rman session
RMAN> copy datafile '+DB3CLNDG/cpdg1/datafile/users.264.640191515' to
RMAN> '+DB3CLNDG';

Starting backup at 06/01/2008 03:42:53
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004
name=+DB3CLNDG/cpdg1/datafile/users.264.640191515
output file name=+DB3CLNDG/cpdg2/datafile/users.268.643261377
tag=TAG20080106T034255 RECID=1 STAMP=643261380 channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:07 Finished backup at
06/01/2008 03:43:03

RMAN> switch tablespace users to copy;

datafile 4 switched to datafile copy
"+DB3CLNDG/cpdg2/datafile/users.268.643261377"

  • Bring the tablespace online again
SQL> alter tablespace users online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
-----------------------------------------------------
+DB3CLNDG/cpdg2/datafile/users.268.643261377

  • Now you can delete the old file
{oracle} /oradisk/app01/oracle/product/11db/dbs [pollux.com] > asmcmd ls
+DB3CLNDG/cpdg1/datafile/users.264.640191515
users.264.640191515
{oracle} /oradisk/app01/oracle/product/11db/dbs [pollux.com] > asmcmd rm
+DB3CLNDG/cpdg1/datafile/users.264.640191515
{oracle} /oradisk/app01/oracle/product/11db/dbs [pollux.com] > asmcmd ls
+DB3CLNDG/cpdg1/datafile/users.264.640191515
ASMCMD-08002: entry 'users.264.640191515' does not exist in directory
'+DB3CLNDG/cpdg1/datafile/'








| Comments (1) | TrackBacks (0) AddThis Social Bookmark Button

January 11, 2008

How to Rename and Register with CRS a Clone of a RAC Database

By alejandro.vargas on January 11, 2008 10:54 AM

To rename a database we set the new name recreating the controlfile, in the case of a RAC database the procedure is slightly more complex, as we need to recreate the controlfile in single instance mode using a modified pfile, and then recreate the spfile.

Once the new controlfile is ready we need to register the database with CRS in order to enable its management as a RAC database.

This document "How to Recreate the Controlfile on a RAC Environment" illustrate the steps required to accomplish that.

| Comments (1) | TrackBacks (0) AddThis Social Bookmark Button

Search All Blogs

Google Search


Only search this blog

About January 2008

This page contains all entries posted to Alejandro Vargas' Blog in January 2008. They are listed from oldest to newest.

December 2007 is the previous archive.

February 2008 is the next archive.

Many more can be found on the main index page or by looking through the archives.

RSS

Subscribe to this blog's feed
Powered by
Movable Type and Oracle