August « 2009 « Oracle DBA – Tips and Techniques

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

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 tablespace test_asm
2 datafile size 100m;

Tablespace created.

SQL> select file_name from dba_data_files
2 where tablespace_name=’TEST_ASM’;

FILE_NAME
——————————————————————————–

+DATA/genua1/datafile/test_asm.376.696333127

SQL> conn system/xxx
Connected.

SQL> create table myobjects
2 tablespace test_asm
3 as select * from dba_objects;

Table created.

SQL> select count(*) from system.myobjects;

COUNT(*)
———-
56504

Create a database link from the source database to the target database

SQL> CREATE DATABASE LINK DBS2 CONNECT TO
2 system identified by oracle
3 using ‘racdb1′;

Database link created.

SQL> select * from dual@dbs2;

D
-
X

Create a directory to hold the converted datafile on the source

SQL> create directory source_dir as

Continue reading 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

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 storage to a conventional O/S file system, the restore will fail as it will look for the ASM diskgroups where the backup was taken on the target node as well.

We will see an error like …..

channel ORA_AUX_DISK_1: reading from backup piece +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
ORA-19505: failed to identify file “+DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727″
ORA-17503: ksfdopn:2 Failed to open file +DBATEAM/bwdba/backupset/2009_08_25/nnndf0_tag20090825t131155_0.388.695826727
ORA-15001: diskgroup “DBATEAM” does not exist or is not mounte
failover to previous backup

In this case we will need to use the BACKUPSET clause of the BACKUP command to relocate that backup from the ASM storage to

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

11g Adaptive Cursor Sharing

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 only for a few or specific parameters or values. And this is particularly so on tables with highly skewed data especially the columns which are being referenced via the WHERE clause.

In Oracle 11g however, with Adaptive Cursor Sharing, if the optimizer observes a sub-optimal plan for a particular bind variable value, it will enable particular bind variables or range of bind variable values to use a different execution plan for the same SQL statement.

To illustrate the 11g Adaptive Cursor Sharing, we will create a table called MYOBJECTS which is based on the ALL_OBJECTS view. The

Continue reading 11g Adaptive Cursor Sharing

DBUPGDIAG script to check integrity of database before 11g upgrade

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 status of the database before (or) after upgrade. The script will
– create a file called db_upg_diag__.log in your local
– working directory. This does not make any DDL / DML modifications.
–
– This script will work in both Windows and Unix platforms from database
– version 9.2 or higher.

col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,’fmMonth DD, YYYY’) TODAY from DUAL;
select version from v$instance;
set termout on
set echo off
set feedback off
set head off
set verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,’dd-Mon-yyyy_hhmi’) timecol,’.log’ spool_extension FROM
sys.dual;
column output new_value dbname
SELECT value || ‘_’ output FROM v$parameter WHERE name

Continue reading DBUPGDIAG script to check integrity of database before 11g upgrade

ORA-27504 error creating ASM instance on 11g RAC

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

# no -o tcp_sendspace=262144
# no -o tcp_recvspace=262144
# no -o udp_sendspace=65536
# no -o udp_recvspace=262144
# no -o rfc1323=1

Adding these entries to the /etc/rc.net will ensure that these parameters take effect on every machine reboot.

Using FLASHBACK to rollback a TRUNCATE

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 BE TRUNCATED

SQL> insert into scott.myobj select * from all_objects;

50496 rows created.

SQL> /

50496 rows created.

SQL> select count(*) from scott.myobj;

COUNT(*)
———-
100992

OBTAIN THE CURRENT SCN – FLASHBACK WILL HAPPEN TO THIS SCN

SQL> select current_scn from v$database;

CURRENT_SCN
———————
15633908021

TRUNCATE THE TABLE

SQL> truncate table scott.myobj;

Table truncated.

SQL> select count(*) from scott.myobj;

COUNT(*)
———-
0

AT THE SAME TIME OTHER CHANGES ARE HAPPENING IN THE DATABASE AND THESE CHANGES WILL BE RECOVERED AFTER

Continue reading Using FLASHBACK to rollback a TRUNCATE

11g RAC Software Relink Error on AIX

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 or open library file: -l ha_em_r
ld:open(): A file or directory in the path name does not exist.
make: 1254-004 The error code from the last command is 255

The cause of this error is that a particluar fileset rsct.basic.rte which is required specifically for a 11g RAC installation has not been installed at the OS level. After the fileset was installed we could relink the software without the same ld errors as mentioned above.

We can check if the fileset has been installed by running the command ‘lslpp’ as shown below.

testdb:/u02/oradata/testdb> lslpp -l rsct.basic.rte
lslpp: Fileset rsct.basic.rte not installed.

After the fileset is

Continue reading 11g RAC Software Relink Error on AIX

11g RAC Transparent Application Failover (TAF)

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 preferred instance is racdb1 and
for service racdb2 the preferred instance is racdb2.

The following srvctl commands illustrates the same.

middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb1
Service racdb1 is running on instance(s) racdb1

middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb1
racdb1 PREF: racdb1 AVAIL: racdb2

middba1:/u01/oracle/dump> srvctl status service -d racdb -s racdb2
Service racdb2 is running on instance(s) racdb2

middba1:/u01/oracle/dump> srvctl config service -d racdb -s racdb2
racdb2 PREF: racdb2 AVAIL: racdb1

From a SQL*PLUS client, we establish a session as user SYSTEM using the service racdb1. Note the machine name where this service is running from.

testdb:/u01/oracle> sqlplus system/xxx@racdb1

SQL*Plus: Release 11.1.0.6.0 – Production on Fri

Continue reading 11g RAC Transparent Application Failover (TAF)

READ ONLY Tablespace Restore and Recovery

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 to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups.

However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail.

Let us illustrate the same with an example where we have made the USERS tablespace read only and then simulated a

Continue reading READ ONLY Tablespace Restore and Recovery

CRS-0184 error and its resolution

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 configuring nodeapps

Creating VIP application resource on (2) nodes0:CRS-0184: Cannot communicate with the CRS daemon

The crsd.log file showed the following lines ….

2009-08-10 14:18:36.059: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:19:12.143: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:20:15.037: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11
2009-08-10 14:21:11.379: [ CRSMAIN][10286] Failed to spawn a thread for UI connection. error=-11

The fix for this error was to increase the Shell Limits for the user ‘root’ as the Clusterware processes are started as root.

The documentation does mention that we need to so the

Continue reading CRS-0184 error and its resolution

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration