How to transport a tablespace from 10g ASM on Windows to 11g ASM on AIX

Posted by Gavin Soorma
On August 31, 2009
2 Comments

This example demonstrates how to transport a tablespace from a 10g ASM on Windows environment to 11g ASM on AIX environment using the DBMS_FILE_TRANSFER package. The source database on Windows is GENUA1 and the target database is a 11g RAC database RACDB1. Create the tablespace and the test object in the source database SQL> create […]

Posted by Gavin Soorma
On August 26, 2009
1 Comments

By default, when we are using ASM with a Flash Recovery Area configured, backups to disk will go to the “backupset” subdirectory – unless we use the FORMAT clause while taking the backup to point the backups to another location on disk. When we try to duplicate a database which is hosted on an ASM […]

Posted by Gavin Soorma
On August 21, 2009
0 Comments

While one of the performance best practices that is always recommended is the use of bind variables over the literals, Optimizer plan unstability was one of the problems faced by many Oracle 10g shops. When queries that normally perform well, suddenly stopped performing, very often it is found that this bad performance is being observed […]

Posted by Arjun Raja
On August 20, 2009
0 Comments

Script to check integrity of a 9i or 10g database before upgrade to 11g. Run this script connected as sysdba in the 9i or 10g database. NAME: DBUPGDIAG.SQL This script can be downloaded from Oracle Metalink note 556610.1 — PURPOSE: — This script is intended to provide a user friendly output to diagonise — the […]

Posted by Gavin Soorma
On August 19, 2009
0 Comments

While creating the ASM instance using DBCA in an 11g RAC environment on 64 bit AIX 5L, we got the ORA-27504 error along with others as shown below. The problem was caused by incorrect UDP and TCP packet settings and is documented in Metalink note 300956.1 As root, the following changes need to be made […]

Posted by Gavin Soorma
On August 18, 2009
2 Comments

This scenario will show how we can use a combination of FLASHBACK database and also recovery to take a database back in time to undo a TRUNCATE operation and then roll forward the database after the flashback operation to bring it to the current point in time. INSERT ROWS INTO TABLE MYOBJ – THIS WILL […]

Posted by Gavin Soorma
On August 18, 2009
0 Comments

While relinking the Oracle 11g RAC software on 64 bit AIX platform, we got the error as shown below: Looking at the installation log we found the following lines: ld: 0706-006 Cannot find or open library file: -l ha_gs_r ld:open(): A file or directory in the path name does not exist. ld: 0706-006 Cannot find […]

Posted by Gavin Soorma
On August 14, 2009
0 Comments

This example illustrates Transparent Application Failover (TAF) in a two node AIX 11g RAC configuration. The database name is racdb and the two instances are racdb1 and racdb2. Node 1 – middba1 which hosts instance racdb1. Node2 – middba2 which hosts instance racdb2 We have defined two services racdb1 and racdb2. For service racdb1 the […]

Posted by Gavin Soorma
On August 12, 2009
0 Comments

Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments. Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets. But we need to keep in mind that we need […]

Posted by Gavin Soorma
On August 12, 2009
0 Comments

While installing the 11g Clusterware on 64 bit AIX 5L, we encountered the CRS-0184 error while running the root.sh on the second node. The error was … Cluster Synchronization Services is active on all the nodes. Waiting for the Oracle CRSD and EVMD to start Oracle CRS stack installed and running under init(1M) Running vipca(silent) for […]

Posted by Arjun Raja
On August 11, 2009
0 Comments

FLASHBACK DATABASE – IMPACT OF RESETLOGS- A flashback database can be completed and the database can either be opened for READ ONLY or opened with a RESETLOGS option. As long as the RESETLOGS option is not used, a flashback can be rolled back . Example – Create table test – insert some records – select […]

Posted by Arjun Raja
On August 7, 2009
0 Comments

CREATE OR REPLACE procedure utl_file_test_write1 ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2) is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’); utl_file.put_line (output_file, firstline); utl_file.put_line (output_file, secondline); utl_file.fclose(output_file); –exception — when others then null; end; / begin utl_file_test_write1 ( ‘/u03/users/ofsa/ofsatest/oracle_utl_data’, ‘utl_file_test’, ‘first line’, ‘second line’ ); end; / This […]

Troubleshooting Agent Startup and Upload

Posted by Arjun Raja
On August 7, 2009
0 Comments

If the agent in GRID control in not uploading to the OMS – cd $AGENT_HOME/bin oracle(DATABASE)@prdu030:./emctl start agent Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved. Starting agent ………. started. oracle(DATABASE)@prdu030:./emctl status agent Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0. Copyright (c) 1996, […]

Posted by Arjun Raja
On August 4, 2009
0 Comments

FLASHBACK DATABASE TO SCN Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July. Get oldest flashback time – SELECT OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$FLASHBACK_DATABASE_LOG; If there are logs up until 06:30 on 31 Jul, you can flashback. Get the SCN number to flashback to – col first_change# format 99999999999 select first_change# ,to_char(first_time,'DD-MON-YYYY HH24:MI:SS'),sequence# from […]

Posted by Gavin Soorma
On August 4, 2009
0 Comments

While the RMAN Tablespace Point In Time Recovery (TSPITR) automates the process of creating an auxiliary instance and exporting and importing objects from the auxiliary instance to the target database, it still has many limitations and we encountered bugs which apparently have been only fixed in 11g. Not only do we need to allocate temporary […]

Posted by Gavin Soorma
On August 4, 2009
0 Comments

To recover from a case of a dropped tablespace, the Tablespace Point In Time Recovery (TSPITR) method cannot be used. When you drop a tablespace, the controlfile will then no longer have any records of the tablespace which has been dropped. Attempts to use the RMAN RECOVER TABLESPACE command will return the RMAN error RMAN-06019 […]

Posted by Gavin Soorma
On August 3, 2009
0 Comments

In Oracle 11g, the init.ora parameters like user_dump_dest and background_dump_dest are deprecated. They have been replaced by the single parameter DIAGNOSTIC_DEST which identifies the location of the ADR. The ADR is the Automatic Diagnostic Repository which is a file based hierarchical respository of data and information related the the database alert, trace and dump files, […]

News

How to transport a tablespace from 10g ASM on Windows to 11g ASM on AIX

Duplicating a database from an ASM to a Non-ASM file system

11g Adaptive Cursor Sharing

DBUPGDIAG script to check integrity of database before 11g upgrade

ORA-27504 error creating ASM instance on 11g RAC

Using FLASHBACK to rollback a TRUNCATE

11g RAC Software Relink Error on AIX

11g RAC Transparent Application Failover (TAF)

READ ONLY Tablespace Restore and Recovery

CRS-0184 error and its resolution

Flashback database – with and without resetlogs.

How to check the UTL_FILE_DIR parameter?

Troubleshooting Agent Startup and Upload

FLASHBACK DATABASE TO SCN

RMAN TSPITR Limitations

RMAN Recovering a Dropped Tablespace

11g ADR – Viewing and Maintaining the alert log file