September « 2010 « Oracle DBA – Tips and Techniques

11g Active Standby Database Automatic Block Corruption Repair

In addition to the real time query capability of the 11g Active Data Guard feature, we can also add to our high availability capability by using the Automatic Block Media Repair feature whereby data block corruptions on the Primary database can be repaired by obtaining those blocks from the standby site – all performed by a background process (ABMR) transparent to the application.

The same functionality can be used to repair block corruptions on the Active Standby site by applying blocks which are conversely now received from the Primary site.

Let us see a test case of the same.

We create a test table and assign it to the USERS tablespace.

SQL> create table myobjects
2 tablespace users
3 as select * from all_objects;

Table created.

Using DBMS_ROWID, we determine the blocks which this table occupies (if you like, just restrict the query to the first 5 blocks in case the

Continue reading 11g Active Standby Database Automatic Block Corruption Repair

11g Active Data Guard - enabling Real-Time Query

Active Data Guard is a good new feature in 11g (although requires a license) which enables us to query the Standby database while redo logs are being applied to it. In earlier releases, we had to stop the log apply, open the database in read only mode and then start the log apply again when the database was taken out of the read only mode.

With Oracle 11g Active Data Guard, we can make use of our standby site to offload reporting and query type applications while at the same time not compromising on the high availability aspect.

How do we enable Active Data Guard?

If we are not using the Data Guard Broker, we need to open the standby database, set it in read only mode and then start the managed recovery as shown below.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed

Continue reading 11g Active Data Guard – enabling Real-Time Query

10g Agent mass deployment using the clone method.

The oracle 10g management agent needs to be installed on all the remote services which we wish to monitor and administer using the Enterprise Manager Grid Control product.

If we are faced with deploying the agent on hundreds of servers which is very likely in case of large organizations, we need to be able to automate the process of installing the agent on each of these nodes rather than have to do it manually on each machine.

There are a number of methods of mass deploying the agent which is outlined in this white paper 10g R2 Management Agent Deployment Best Practices.

Let us have a quick look at how we can deploy the agent using the clone method.

Source machine is sunos1 and target machine is sunos2. The agent Oracle Home on both machines is /u01/app/oracle/product/agent10g

Tar the source Agent Oracle Home, copy to target machine and untar the same.

sunos1:/u01/app/oracle/product $ tar -cvf

Continue reading 10g Agent mass deployment using the clone method.

Downgrading a failed 11g Release 2 Upgrade - the easy way

One of the best practices before performing any database upgrade is to take a full backup before we start the upgrade process just in case the upgrade fails and we want to then restore the previous version.

In many cases owing to the size of the database, we are constrained both by time as well as disk space when it comes to taking a full backup of the database.

So can we just get away by taking a backup of a much smaller subset of the database and not worry about backing up all our data and index tablespaces? – just the controlfiles, redo log files and the tablespaces – SYSTEM, SYSAUX, UNDOTBS1 (or whatever you call your undo tablespace) and TEMP (or whatever you call your temporary tablespace/tablespaces).

Let us see a test case where we start by performing a manual upgrade of a 10.2.0.4 database to 11g Release

Continue reading Downgrading a failed 11g Upgrade – the easy way

Cloning 10g Application Server Mid Tier

Recently at one on my client sites, there was a requirement to create an additional 10g Discoverer instance of an Oracle Application Server. Rather than attempt a fresh installation as a number of patches had been applied to this Oracle Home, I decided to perform an Oracle Application Server clone instead.

Here are the steps outlined – source machine is sunos1 and target machine is sunos2.

On the source and target ensure that the PERL5LIB variable is set.

sunos1:/u01/app/oracle $ export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$ORACLE_HOME/perl/lib/site_perl/5.6.1

Execute the prepare_clone.pl perl command

sunos1:/u01/app/oracle/BIPROD/clone/bin $ ./prepare_clone.pl ORACLE_HOME=/u01/app/oracle/BIPROD

Creating the log directory: /u01/app/oracle/BIPROD/clone/logs

Clone log file location: /u01/app/oracle/BIPROD/clone/logs/clone1284085526.log
Error log file location: /u01/app/oracle/BIPROD/clone/logs/error1284085526.log
[Fri Sep 10 10:25:26 2010 INFO] Starting with the Prepare Clone operation at the source
[Fri Sep 10 10:25:26 2010 INFO] The temp directory being used is /tmp
[Fri Sep 10 10:25:27 2010 INFO] The file /u01/app/oracle/BIPROD/inventory/Clone/clone.xml is clean. Repair operation not needed.
[Fri Sep 10 10:25:37 2010 INFO] The prepare

Continue reading Cloning 10g Application Server Middle Tier

11g RMAN Duplicate from Active Database and ORA-01017

Recently while performing an 11g Release 2 Active Database duplication, I encountered an error which one would normally associate with password files and the fact that the SYS password is not the same on the source and target databases.

RMAN-03002: failure of Duplicate Db command at 09/01/2010 09:17:39
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/01/2010 09:17:39
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Apparently, if we are performing an active database duplication via RMAN, we cannot connect to the target database using OS authentication like ‘ target /’.

We need to fully qualify the connection to the target database with the SYS user and password like ‘target sys/syspassword’ and not just ‘target /’.

I found a few links which discuss the same topic and thanks for the solution which I am sharing with

Continue reading 11g RMAN Duplicate from Active Database and ORA-01017

Purging trace and dump files with 11g ADRCI

In previous versions of Oracle prior to 11g, we had to use our own housekeeping scripts to purge the udump, cdump and bdump directories.

In Oracle 11g, we now have the ADR (Automatic Diagnostic Repository) which is defined by the diagnostic_dest parameter.

So how are unwanted trace and core dump files cleaned out in 11g automatically?

This is done by the MMON background process.

There are two time attributes which are used to manage the retention of information in ADR. Both attributes correspond to a number of hours after which the MMON background process purges the expired ADR data.

LONGP_POLICY (long term) defaults to 365 days and relates to things like Incidents and Health Monitor warnings.

SHORTP_POLICY (short term) defaults to 30 days and relates to things like trace and core dump files

The ADRCI command show control will show us what the current purge settings are as shown below.

adrci> show control

ADR Home = /u01/app/oracle/diag/rdbms/ttrlwiki/ttrlwiki:
*************************************************************************
ADRID

Continue reading Purging trace and dump files with 11g ADRCI

Oracle DBA – Tips and Techniques

That’s Me

Feedback

Categories

Archives

Recent Posts

Administration