12.20.2007

Odd issues while preparing Physical Standby

After an uphill task of upgrade/migration of 2.5TB sized data warehouse Oracle database from 9i to 10g and then from one OS to another (cross platform), the subsequent challenge is building a DR (Standby Database) for this database.

The DR (Disaster Recovery) site is around 3 km away from the HO. Since the size of the database is huge, we thought of moving tapes (physically) or duplicating the backup tapes from HO to the DR site, but, none of the ideas was materialized due to few technical/ un-technical difficulties. Fortunately, the speed of the lease line (leaser link) between the two sites is good enough and we decided trigger the duplicate database (standby) command from HO.

Ufff.. It took 3 days to finish the restore. However, the subsequent recovery on the standby database failed by the following problem:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/19/2007 05:16:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-19625: error identifying file /datafile1.dbf
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 2

Surprisingly, the datafile, datafile1.dbf (filename is changed here) which Oracle complaints was exists on the mount point. Then, thought of starting the MRP (recover managed standby database disconnect from session), unfortunately, the MRP terminated by the following errors:

Errors in file /dbdata/oradba/admin/OFDMP/bdump/ofdmp_dbw0_1077358.trc:
ORA-01157: cannot identify/lock data file 1479 - see DBWR trace file
ORA-01110: data file 1479: '/mountpoint/
datafile2.dbf'

Again, the complained file exists on the mount point, however, this time the problem was that the file has ‘carriage return’ before the name, (this may happen when you try to create/add a datafile from OEM). The workaround was following:

mv /mountpoint/ datafile2.dbf’ /mountpoint/datafile2.dbf

and then, change file name in the controlfile with the following command:

(make sure the standby_file_management is set to MANUAL on the standby).

alter database rename file ‘/mountpoint/ datafile2.dbf’ /mountpoint/datafile2.df’;

After the above workaround, the MRP (Media Recovery Process) was started successfully and looking for the archive gap.

When I tried to restore required archived log on the standby database from the backups (archived backups), the restored command failed with the following:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/20/2007 13:44:20
RMAN-20242: specification does not match any archive log in the recovery catalog

I thought of just testing the restore on production, the restore (tried t restore single archive log) was successfully.

I lately realized that the missing archives are generated after creating the standby controlfile and obviously the standby controlfile was not having the information about these archived logs. (Backups were performed with nocatalog option).

Following is the workaround:

1. Create a catalog and register production database.

2. On standby, using the catalog (rman target / catalog). Now, standby can get the info about the backups through the catalog.

3. Restoring required archived logs.

Earlier, I have done dozens of DG configuration facing different challenges. Well, everything it’s a new challenge and learning experience.

Happy reading,

Jaffar

12.10.2007

Simulating 11g Snapshot Standby Database feature on Oracle 10g?

As we all knew that the Oracle 11g improved the capabilities of standby database immensely, where a physical standby database can easily open in read-write mode, which can be ideally suitable for test and development environments. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.

What if you want to achieve the same on Oracle 10g? Well, I absolutely don’t have any clue about others, but, we have come across of such situation couple of days ago when our DR (Disaster Recovery Solution) team came to us with a request to test our standby database. They want the standby database in read write mode to do some real scenario tests and once the testing is done, they want the database to be back to standby mode.

We initially said, we can open the database in read only mode for their testing, but, the requirement demands the database to be in read write mode. We thought, we can break the standby database for their testing and once the testing is done, we can rebuild the standby database again. We know that this is very well possible with Oracle 11g but not with Oracle 10g. My colleague, Mr. Asif Momen, did some R&D come up with a solution where a Oracle 10g standby database can open in read write mode and can also be reverted back to standby mode.

The procedure as follows:

1. Set the following parameters on the standby database:

db_recovery_file_dest_size & db_recovery_file_dest

- Make sure the values are reflected.

2. Stop the media recovery process, if active.

3. When the standby is in MOUNT mode, Create a guaranteed restore point:

CREATE restore point before_rw guarantee flashback database;

3. Stop the log shipping on the primary database. (for safer side)

alter system archive log current;

alter system set log_archive_dest_state_2=DEFER;

4. Failover the standby database using the following command:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

-Make sure the media recovery process is turned off

-Minimize the protection mode to MAXIMUM PERFORMANCE, if the mode is set other than the MAXIMUM PERFORMANCE.

5. Open the database (read write mode).


AT THIS POINT, YOU CAN USE THIS DATABASE AS NORMAL READ WRITE DATABASE.

Reverting the database back to standby mode:

  • Shutdown the database
  • Startup database in mount mode
  • Flash back database to restore point using the following:
FLASH BACK DATABASE TO RESTORE POINT before_rw;
  • Convert the database back to standby mode using the following:
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
  • Shutdown the standby database and remove the previously set parameters.
  • Start the standby database in mount state and drop the restored point.
  • Enable the Media Recovery on the Standby database.
  • Activate log shipping on the primary using the following:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENALE;

Since, we have done very little changes in the database, after converting to read write mode, the time which took to revert the database back to standby mode took few minutes only. Well, it is definitely need to be seen the time that take during the conversion to standby mode after huge changes in the read write database.


It worked well with us and Mr. Asif definitely deserved an appreciation

The tests have been carried out on AIX 64 bit with Oracle 10.2.0.3 release.

In the Part II, I will be posting my testing.

Happy Reading,


Jaffar

11.12.2007

Back from vacation

I just back to office after a month vacation and going through the back log/pending work. Currently, having an uphill task of upgrading an Oracle 9i data warehouse database (3 TB size) to Oracle 10g and also migrating this database from HP to AIX.

Its been nearly two months that I didn't write anything to my blog also didn't participate in any kind of database forums as I was gearing up for my every first seminar (RMAN). I have successfully delivered my one exclusive RMAN seminars in Sweden and Denmark respectively in September month. Good amount of participants were present, 20 participants in Sweden and 28 participants in Denmark. Although, the seminars went well, I felt still there was a lot room for improvement. I was bit worried about the time constraint to complete the seminar. In the modified RMAN seminar contents, now, more practical examples with 11g RMAN new features and changes are also added.

The first thing which I did was installing 11g on Windows OS in my PC. I am more interested to test RMAN changes and testing Active Standby Database(snapshot database) in 11g database. I am also busy in preparing a new one day exclusive seminar on High Availability Data Guard.

In my next blog, I am going to discuss about my experience with Active Standby Database and findings.

Thank for your time,

Jaffar

8.27.2007

11g Database Creation with DBCA

After successfully completing Oracle 11g on Red Hat Linux(v4), I thought of creating a new database using DBCA utility instead creating manually. While using DBCA utility to create a new database, I have found following three new changes:

1. If no listener is configured, DBCA utility prompts a message to configure a listener first.
2. Security Settings gives the following options:
- Keep the enhanced 11g default security settings(recommended)These settings include
enabling auditing and a new default password profile.
- Revert to pre-11g default security settings
3. Automatic maintenance task tasks
such as optimizer statistics collection and proactive advisor reports.
The default maintenance windows are 10:00 pm to 2:am on weekdays and all weekend long.

When I count records from v$system_event, surprisingly, I found only 77 records? Is it okay?

Jaffar

11g 'hot patching'

One feature which caught my extreme attention while installing 11g was 'hot patching' feature.

Oracle says: ''hot patching,' which improves system availability by allowing database patches to be applied without the need to shut databases down."

For me, it is really hard to digest about upgrading (applying patches) while they are currently running. However, if this is believed to be true, then, definitely this feature going to resolve the hassles of shutting down the production databases while applying patches.


Jaffar

8.26.2007

11g Installation

Last night I was able to successfully installed Oracle 11g on Red Hat Linux(v4). The only problem I have faced during the installation stage (the prerequisite check stage) was about missing Red Hat rpms (three) and minimum memory required for installation(I have installed on a virtual machine with 700+M) .

I am very inquisitive to test RMAN enhancements and also Active Standby Database feature.

Jaffar

8.18.2007

Oracle 11g Metalink notes

Metalink has couple of new notes/articles about Oracle 11g Active Duplicate Database and DBCA enhancements. Following are the metalinks notes :

11g BDCA New Features / Enhancements - 454631.1
Automatic Memory Management(AMM) on 11g - 443746.1
RMAN 'Duplicate Database' Feature in 11G - 452868.1
Finding alert.log file in 11g - 438148.1

Happy reading,

Jaffar

8.14.2007

Celebrating 60th Indian Independence Day


Indian Independence Day


I wish all the Indians across the global a very happy independence day.


God bless all,

Happy Reading,


Jaffar

8.13.2007

Oracle 11g - Active Standby Database

I have read about Oracle 11g Active Data Guard and pretty impressed with the enhancements done in the Data Guard technology.

Although, I haven't tested those enhancements, but, looks very useful.

Following are the few notable enhancements:

1. Enabling physical standby database in read only mode while all the changes from the production database are being applied to it. This feature resemblance to logical standby database techniques in previous Oracle versions.

2. Snapshot Standby - A snapshot Standby is open for read-write which can be ideally suited for test environments, able to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
Using a single command discard changes made while read-write mode and quickly resynchronizes the standby with the primary database.

I really need to do a test case on these enhancement in order to find out whether it really work the way Oracle says or any bugs associates with them.

For more information, read the following pdf document which is available at Oracle site.

http://www.oracle.com/technology/products/database/oracle11g/pdf/active-data-guard-11g-datasheet.pdf

Happy reading,

Jaffar

7.30.2007

An interesting situation with Standby database.

We have a data warehouse database sized more than 2TB using Oracle 9i on HP Superdom machine.

Couple of days back, I have come across of a very tricky situation with its standby database. Yes, you read it correctly. We have the standby database in place for the this database. For god sake, don’t argue with this as there are N number of reasons behind having a standby database for the data warehouse database.

The situation was that the mount point, which has all the standby database files, on standby database server became 100% full and the media recovery background process died/crashed (whatever you say) and archive applying was stopped. I went to the UNIX team and asked them increase the size of the mount point. They gave me a big surprise, saying that the mount point is already 2TB size and on HP Superdom the mount can’t exceed 2TB size. Therefore, increasing the size of the mount point was not possible.

After hearing their comments, I have initially thought that I can open the standby database in READ ONLY mode, move some datafiles to a new mount point in order to have some free space on this mount point. The idea doesn’t work as I was not able to open the database. Oracle was complaining about the media recovery when I tried to open the database in read only mode.

I was in a impression that this would be a dead end and I have ended of recreating the standby database again. Again a standby database which is more than 2TB…. Uffffssss

One of our UNIX admin guy gave me an idea. It was like moving few datafiles from the old location to the new location and creating the OS level link for the datafiles in the old location pointing to the new location.

I thought, I could try this and it worked beyond my imagination. I have shutdown the standby database, allowed him to move the datafiles (for him, it was like flat files) to new location and create the links for the files in the old location pointing to the new location.

I was bit worried while bringing the standby database up and starting media recovery process. The idea worked out and Oracle started applying archived logs on the standby database from the point where they were stopped.

I felt like in my career, every problem is a learning experience and give new ideas to deal with the situation.

That’s all now,

Happy reading,

Jaffar

7.25.2007

V$ View Definitions Library - Metalink Note : 220021.1

Today, I have come across of a metalink note : 220021.1 that has all the v$ views with their defination.

The note put all the V$ views together at one place and moverover with their defination. Umm.. this could useful when you get everything at one place.

If you have not metalink aceess, well, Oracle documentation does has the same.


Visit Metalink Note : 220021.1

Happy Reading

Jaffar

Oracle Database Sql Expert beta exam

This morning I have attended the 1Z0-047 Database Sql Expert beta exam and expecting around 80% of it. The exam has 139 question with 3hrs of time. However, I have completed the exam just before the give time, i.e. within 2hr 45 minutes.

This was my first beta exam and I have felt that the duration of the exam is too long with plenty of . There were couple of questions with absolutely wrong choices(answers) and few of the questions were redundant.

But, the exam prepration has refreshed my sql basics once again and also helped me to learn new SQL aggregated function like, GROUPING, GROUPING_SETS, ROLLUP,CUBE, and other few new feature of SQL (WITH CLAUSE, Multitable Inserts, MERGE).

Well, I have to wait 2 or more months in order to receive the score sheet and ofcourse the result whether I am through or not.

In my opinion, this exam is more suitable for the Developer who are in touch with writing queries and upto date of sql funtion. Definately, a piece of cake for them with NO REALL CHALLENGES.

Happy reading,

Jaffar

7.23.2007

Oracle ACE Director - Now includes Database and Development categories

Well, sometime back, Tim had discussed about the new look and feel of Oracle ACE page on the OTN and also about Oracle ACE Director Program at his blog http://www.oracle-base.com/blog/2007/07/23/oracle-ace-director/. His question was about the Oracle ACE Directors category and point out that none of the existing ACE Directors are nor Database neither Development community. All from Fusion Middleware community.

This morning I have noticed an email from the Oracle ACE Program team stating that the Oracle ACE Director is now opened for Database and Developers community as well.

Not only will Oracle Fusion Middleware Regional Directors now be known as Oracle ACE Directors, but we have also opened it up to include Oracle Database performance/management and application development experts.”

What this means to you as an Oracle ACE
The Oracle ACE program will continue to be managed as it has been. The new benefit is that you may be eligible for the Oracle ACE Director level. Oracle ACE Directors are well known experts in their field and have committed to participating in a dialog with Oracle and their local technical community.

Well, its good to know that Oracle is considering critics, whether it is an open letter to Larry about the AWR licencing or including database and development categories for Oracle ACE Director program and taking quick actions.

Happy Reading,


Jaffar

Presenting RMAN 1 day seminar in Sweden and Denmark

I will be presenting one day seminar on RMAN basic and advanced concepts in Sweden and Denmark on 26th & 27th September respectively.

The course index is as follows:

Backup & Recovery Concepts
  • What is backup and why backups are important?
  • What is recovery?

Backups Methods
  • Logical Backups (Export/Import and Data Pumps)
  • Cold Backups Hot Backups (Consistent & Inconsistent Backups)

Recovery Manager (RMAN) Architecture & Concepts
  • RMAN Introduction and Architecture
  • Performing Backups using RMAN Recovery Catalog Maintenance

Different levels of Backups
  • Mechanism of Incremental Backups
  • Block Change Tracking Concepts

Flash Recovery Area (FRA)
  • Introduction FRA Concepts and Benefits

Recover Database
  • Recovering from different scenarios Instance/Crash Recovery
  • Media Recovery
  • Complete Recovery
  • Incomplete Recovery

Block Recovery RMAN Advanced Topics
  • Automatic Point-in-Time Recovery (TSPITR)
  • Cloning Database
  • Creating Standby Database/Rolling Upgrade
  • Transportable Tablespaces from RMAN Backups Cross- Platform
  • Transportable Tablespaces Cross Platform Database

Anyone wants to register for Denmark seminar, use the following url to register for the seminar.

http://www.oracle.com/education/emea_eblasts/local_campaign/dk/dk_syed_jaffar_hussain_1day_seminar_27_september_ballerup_120707_ol.html

Those who are in Sweden and willing to attend this seminar, can use the following url and register for the seminar.

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D16314_955393&p_org_id=40&lang=S&source_call=

Since this is my ever first international seminar, I am bit nervous and at the same time excited about the opportunity.

Happy Reading,

Jaffar



7.15.2007

alert.log file in 11g

According to metalink note - 438148.1, beginning with Release 11g, the alert log file is written as XML formatted and as a text file (like in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, yet another new dump dest in 11g).

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, 'u01/oracle/product/ora11g/log' (depends on ORACLE_HOME settings).

The location of an ADR home is given by the following path, which starts at the ADR base directory: ADR_BASE/diag/product_type/product_id/instance_id

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

Within the ADR home directory are subdirectories:
alert - The XML formatted alertlog
trace - files and text alert.log file
cdump - core files

The XML formatted alert.log is named as 'log.xml'

Happy Reading,

Jaffar

7.11.2007

11g, Whitepappers on OTN.

I have seen many people talking about the arrival and launch of Oracle 11g.

I found few of 11g Whitepapers on OTN. Following is the URL for the PDFs.

http://www.oracle.com/technology/products/database/oracle11g/index.html

Jaffar

7.10.2007

1Z0-047 Oracle Database SQL Expert

Well, after learning about this beta exam from Laurent, I couldn't stop myself registering this exam. I am going to take this exam on 25th of this month. I knew, its too far, but, due to the time constraints and busy schedule, I had to register the exam on the specified date.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=1001&lang=US&p_exam_id=1Z0_047

I am not so found of this expert certification word, as I don't believe in simply reading books, attending the exam (selecting right answer from the list of answers) and passing the expert exam.

It would have brought more interest and value to the certification if Oracle consider EXPERT exams as practical ones where cnadidate can show his/her reall skills rather than the temporary knowledge got from some testing questions or etc for the sake of passing the exam.

Jaffar

7.09.2007

Change behavior of GROUP BY clause in Oracle 10g.

After successful upgrade of one of the busiest 9i database to 10g, we have received a very first
change of behavior from the users.

Users complained that after upgrading the database, some of the queries which were returning results in an order are now not returning the data in any order now.

The queries have GROUP BY clause, and after some research, we found out that
starting with 10g, Oracle uses the new HASH GROUP BY mechanism which will not guarantee the result in any order unless ORDER BY clause is specified.

HASH GROUP BY algorithm is explained in the following URL (looks old stuff)
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbugen9/00000428.htm

In a nutshell, in Oracle 9i, GROUP BY clause gives result in sort order, this behavior has
changed from 10g onwards.

The general and Oracle recommendation is, to add the ORDER BY clause in order to have 100% guaranteed order by result.

Following is the test which I have carried out on 9i and 10g database (UNIX PLATFORMS):

>>on 9i database

select owner,count(1) from dba_segments group by owner;

OWNER COUNT(1)
------------------------------ ----------
BARABC 42
BASABC 10
BIPABC 82
CPABC 23
DMIABC 40
DMI_TRGTABC 247


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 VIEW OF 'DBA_OBJECTS'
3 2 UNION-ALL
4 3 FILTER
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'



>>on 10g database

OWNER COUNT(1)
------------------------------ ----------
SYSTEM 353
CISABCD 46
OEABCD 35
SCABCD 71

-----------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU)
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 18020 299K 81 (7)
1 HASH GROUP BY 18020 299K 81 (7)
2 VIEW DBA_OBJECTS 18020 299K 79 (4)


As you can see, 9i query returns the result in the ascending order, where as 10g, didn't.

If the ORDER BY clause is added on 10g database, the explain plan looks like below, an extra sorting is being performaned:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 SORT (GROUP BY)
3 2 VIEW OF 'DBA_OBJECTS'

The default behavior of GROUP BY clause in 9i, provides an internal sorting when the GROUP BY clause is used, avoiding an extra sorting, which in my opinion could improve the performance of the query.

I don't know the internal behavior of HASH GROUP BY algorithm, but, this will be useful when
you don't want data in any order after the GROUP BY clause.

After further investigations, I have found out that HASH GROUP BY has few bugs which
leads to an inaccurate result. As per Oracle, the bug it is resolved in 11g version.

Metalink : Bug 4604970 WRONG RESULTS WITH 'HASH GROUP BY 'AGGREGATION ENABLED .

Alex had discussed about this behavior at his blog:
http://www.oracloid.com/2006/05/hash-group-by-can-give-wrong-result-in-oracle-102/

Disable HASH GROUP SORT in 10g:

Oracle recommend setting the _gby_hash_aggregation_enabled parameter = false OR
optimizer_features_enabled=9.2.0 to get rid of HASH GROUP BY mechanism.

alter session set "_gby_hash_aggregation_enabled" = FALSE;

Id Operation Name Rows Bytes Cost (%CPU)
---------------------------------------------------------------------------------
0 SELECT STATEMENT 2181 37077 997 (2)
1 SORT GROUP BY 2181 37077 997 (2)
2 VIEW SYS_DBA_SEGS 2181 37077 996 (1)

Julian Dyke has a brief explanation note and a example at his site:

http://julian.dyke.users.btopenworld.com/com/Optimisation/Operations/HashGroupBy.html

My colleague did a small test on 9.2.0.7 database where he found out an interesting point that the ordering of the result is different when ORDER BY clause is not specified with the GROUP BY.

drop table test;
create table test(a varchar2(30));

insert into test values ('ahmed');
insert into test values ('zubair');
insert into test values ('11');
insert into test values ('-11');
insert into test values ('Zubair');
insert into test values ('test');

commit;


SQL> set autotrace on exp
SQL> select a, count(*) from test group by a;

A COUNT(*)
------------------------------ ----------
-11 1
11 1
Zubair 1
ahmed 1
test 1
zubair 1

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'TEST'



SQL> select a, count(*) from test group by a order by a;

A COUNT(*)
------------------------------ ----------
-11 1
11 1
ahmed 1
test 1
Zubair 1
zubair 1

Please note that the Schema names are modified to show the test case.

References:

Refer Metalink Note : 345048.1 - 'Group By' Doesn't not Sort If you don't use order by in 10g.
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251893,00.html
Update : (14 July)

I was reading the Oracle University Introduction to SQL course PPTs and in the 'Aggregate Data using The Group Functions' it did mention that the rows are sorted by ascending order of the columns included in the GROUP BY list.

I personally think that was a great idea, it indeed avoid an extra sorting (comes using by ORDER BY clause.


Happy Reading,

Jaffar

6.08.2007

STANDBY DATABASE + Error is 16191.

Last night I was demonstrating to my friends about standby database creation and its administration using Oracle10g, Release2 (10.2.0.1) on Windows XP Operating System and at the end of standby database creation (creating on the same host) found out that log information is not shipping from the primary database to the standby location despite setting all the required parameters correctly on primary and standby.

After wasting an hour time, finally had a look at primary database alert log and found the following error messages:

====
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
It may be necessary to define the DB_ALLOWED_LOGON_VERSION
initialization parameter to the value "10". Check the
manual for information on this initialization parameter.
------------------------------------------------------------
Thu Jun 07 01:40:44 2007
Errors in file d:\oradata\ocm\bdump\ocm_arc0_876.trc:
ORA-16191: Primary log shipping client not logged on standby

PING[ARC0]: Heartbeat failed to connect to standby 'SDB'. Error is 16191.

Errors in file d:\oradata\ocm\bdump\ocm_arc0_876.trc:
ORA-01031: insufficient privileges
====

When searching in the metalink about 'DB_ALLOWED_LOGON_VERSION' found the following

Note: Bug 2981553, which is implemented in 10.1.0.2, removes the parameter db_allowed_logon_version. This is replaced by the sqlnet.ora parameter called sqlnet_allowed_logon_version.

As per note, setting SQLNET_ALLOWED_LOGON_VERSION doesn't solve the issues.

Looking more close of the error message, I then, realized that I gave the different password to standby database password file than the primary password file.

After recreating the password file of standby database with the similar password of primary database password file, automatically log shipping started transmitting from primary to standby.

Well, I don't realized that just having different passwords to primary and standby parameter files will leads to this problem.

The other change I noted in the 10g Standby versus 9i is that, in 9i, when you say simply say startup Oracle throws an error saying the the controlfile is standby, where as in 10g, upon using startup, it automatically opens the standby database in READ ONLY state. Umm...

Happy reading,

Jaffar

--

6.04.2007

Mystry resolved (RMAN/VERITAS +ORA-19511)

Since last month, all of sudden our data warehouse database(size 1.7tb) RMAN full database backup started failing with the following error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch09 channel at 06/01/2007 13:15:15ORA-19502: write error on file "OFDMP_Dly_BACKUP_DBFFri010607krij6c1h_1_1", blockno 10731521 (blocksize=1024)ORA-27030: skgfwrt: sbtwrite2 returned errorORA-19511: Error received from media manager layer, error text:VxBSASendData: Failed with error:Server Status: Communication with the server has not been iniatated or the server status has not been retrieved fromthe server.

Looking into the above error message it is clearly understood that the problem is definitely relates to Media Manager as the communication between the channels from the client side to the netbackup server side loosing the communication after sometime.

We reported this problem with Veritas (vendor) and their support staff were here to run the back in trace mode and took those trace file for further investigations.

Meanwhile, I have also opened a p1 TAR with Oracle Support, they simply said that the problem is moreover related to Veritas and we should contact Veritas to resolve the issue.
It was like not having backups for more than 1 month (luckily we have DRC in sync with the primary).

Surprisingly, backing up a single, set of datafiles or tablespace was working fine, the problem is only when backing up entire database. Once it finishes 1 hr of time, backup was failing with the above stated error message.

Upon Veritas recommendation we gave look at NET_BUFFER_SZ value in the Netbackup Server and the Client side(the database server configured as netbackup client, since there is no server edition available on HP Superdom, we were using Netbackup client) we found out that the client and the server has different values in the NET_BUFFER_SIZE file.

We changed the value of this file in the client side to match with the server side file and backup started working fine.

As per general recommendation, NET_BUFFER_SZ file value on the client should be <= to the value on the server side.

Now the backup is running without any issues. No one has clue how this file got changed the values.

Happy Reading,

Jaffar

How to get a stack trace from a CORE file

We might have of come across of many situation where Oracle create CORE file, usually when the process has attempted perform something which Operating System does not like.
 
Since the CORE file is in binary format, it is difficult to read the contents of it and upon contacting Oracle Support, they might ask you to get the stack trace from a CORE file which will be helpful for their investigation.
 
I will shows you couple of steps using which you can produce the stack trace for a CORE file, this example is only for UNIX platforms.
 
By default, 'core' file will be generated under the directory mentioned with CORE_DUMP_DEST init parameter, or  in $ORACLE_HOME/dbs or in the directory from where you run the applicationf.
 
First step is to know which executable (program) cause core file genration.
                
               file core (make sure you are in the core file directory and execute the command).
 
Second step is to get the stack trace from the core file using one of the following command which supports to your OS:
 
                  dbx, xdb, gdb, dde, sdb, adb, debug, gdb
 
For me on HPUX Superdom, 'adb' command works.
 
Example:
                        script mystack
                        adb $ORACLE_HOME/bin/program core
                             $c
                             $q
                          exit
 
When the above commands are executed, stack trace of the core file will be in 'mystack' file and you can upload this file to the Oracle Support for their investigation.
 
 
Referecnes:
 
Note : 1812.1 Getting a Stack Trace from CORE file

6.03.2007

Turned to 34 years today

Ummm... today is my birthday and I have turned to 34 years. Early thirties!!! myself feeling bit older now.
I usually don't celebrate my birthday, I treat this day as a normal day. Birthday has a funny meaning in Urdu. In Urdu it is called as 'Saal Ghira', which means, a year fallen.
 
I have said my kids to celebrate my birthday as their birthday by distributing sweets and chocolates in their respective classes. They were born in July and we usually go to India during July on annual vacation and celebrate their birthday as a small family function. This time we are going to India in September not in July as we use to. Since they know that we are not going to India, they agreed to celebrate their birthday today. We brought new clothes and gifts for them to feel happy.
 
My wife and kids planned a surprise party at home for me and a cake has been ordered from french corner shop, my favorite biriyani being prepared for lunch. Well, they don't that I know their surprise!. But, I kept it as a secrete that I don't about their surprise!
 
Thanks for reading,
 
Jaffar

6.01.2007

RAC - finally into production

It was like a long cherished dream become true when we successfully moved one of our highly OLTP database from the single node to 2 node RAC on AIX platofrm.

We initially thought of implementing extended RAC between head office and DRC sites. At the last moment we had to drop the plan due to some non-technical reasons. Rather, we simulated the extend RAC in the head office using two different storages.

They were couple problems faced during the implementation, 1) when we changed the default port from 1521 to another, there was an issue with Enterprise Manager Console, as it was still looking for the default port (1521). We couldn't successes in the first attempt of running 'emca -reconfig dbconsloe db' command. However, running the same command second solve the issue. 2) AMS Listener entry missing. After some research we found out the the LOCAL LISTENER entry missing from the ASM initialization parameter. Setting the LOCAL LISTENER parameter in the ASM init file solve the issue. These were related with Enterprise Manager (console), however, the database was functioning without any issues.

Although, we have 2 node RAC, we thought not to distribute the load equally on the two nodes, rather, 90% on one node and 10% on another node, (of course missing failover connection concept here). This was simply to monitor the behavior of the RAC. If we finish the week successfully without issues, we will then implement the load distribution equally to the both nodes.

Once we get the stable line between the head office and DRC site (which a kilometer away from the head office), we will another third node.

I should say that the RAC installation doesn't fancy me was I initially thought. The major challenges lays of maintaining RAC environment and survival commands to quickly come from problematic issues. I am sure that experience will teach us.

If everything goes will, Mr. Murali Vallath will be presenting 4 day RAC in-house training at our premises in coming months. Bit excited about this opportunity.

Thursday and Friday are off (week-end) here in ME (Saudi Arabia). The database will be in live mode this Saturday.

I will keep posting about my experience of RAC.

Happy reading,

Jaffar

5.23.2007

Transport Tablespace from RMAN backupsets

Transportable Tablespace from RMAN backupsets:

Transportable tablespace supports the fastest method of copying set of tablespaces from one Oracle database to another.

In general, tablespaces must be kept in read only mode before being transported. It will be difficult task to put bigger size tablespaces and 24x7 accessed high availability tablespaces in the read only mode for longer time. This could prove a potential problem with the high availability databases.

Starting with version 10g Release 2, Transportable tablespace can be created from RMAN backupsets. In contrast, when RMAN creates transportable tablespace set from backupsets, it doesn’t touch the live datafiles from the tablespace to be transported and this elements the need of putting the tablespace in read only mode. Hence, it improves the availability.

Depending on the backup retention policy, transportable tablespace cal also be enables until point in time, SCN or restore points.

Step-by-step procedure for creating Transportable Tablespace from RMAN backup

RMAN uses three following steps to create Transpor Tablespace:

1. RMAN constructs an auxiliary instance.
  • Creates an initialization parameter file.
  • Brings the instance in NOMOUNT state and then restores production database controlfile from RMAN backup to server auxiliary instance.
  • Then, mounts the database.

2. RMAN restores auxiliary and transportable set of datafiles and Perform Point-in-Time Recovery.

  • Restore system related tablespaces(system,sysaux,undo & temp)
  • Restore transportable set of datafiles to the specified auxiliary destination.
  • Performs SWITCH operations at auxiliary instance.
  • Performs point-in-time recovery at the auxiliary instance.
  • Archived logs will be applied on the demand and will be deleted upon apply.
  • Upon completion of recovery, RMAN performs OPEN RESETLOGS on the auxiliary instance.

3. Auxiliary tablespace are put into read only mode, and Data Pump export mode invokes to construct the dump file with set of transportable tablespace.

Upon successfully completion of above operations, RMAN shutdown the auxiliary instance and deletes all the files generated during the above operations. It also generates the loading and unloading scripts.


Creating Transport Tablespace from RMAN backupsets

Start the RMAN and use the following syntax to create transportable tablespace from the RMAN backup.

RMAN> transport tablespace amicus
tablespace destination ‘d:\tts_rman’
auxiliary destination ‘d:\temp_dest’
;

Description:

TRANSPORT TABLESPACE is the command used to create transportable tablespace from RMAN backup.

TABLESPACE DESTINATION: is the location where RMAN left the set transportable datafiles, and Data Pump loading, unloading scripts names, dumpfile.dmp, export.log and impscrpt.sql.

AUXILIARY DESTINATION : is the temporary location where RMAN restores system, sysaux, undo and temp tablespace from the source database, and also redo logs. These files will be deleted automatically upon successfully completion of transport tablespace operation.

It is possible to divert the Data Pump files (import script, dump file and logfile) generated by RMAN to the Data Pump directory using the following syntax:

RMAN> transport tablespace amicus
Tablespace destination ‘d:\tts_rman’
Auxiliary destination ‘d:\temp_dest’
Datapump directory dump_dir
Dump file ‘amicus.dmp’
Import script ‘amicus_tbs.sql’
Export log ‘amicus_tbs.log’;

RMAN Transport Tablespace also can be used with SCN, UNTIL and RESTORE POINTS options.

Examples:

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL SCN 11379;

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL RESTORE POINT 'before_change';

Transport tablespace amicus
Tablespace destination 'd:\tts_rman'
Auxiliary destination 'd:\temp_dest'
UNTIL TIME 'SYSDATE-1';

Auxiliary Instance Parameter:

Following are the initialization parameters are used to construct the auxiliary instance.

Db_name
Compatible
Db_unique_name
Db_block_size
Db_files
Shared_pool_size – Set to 110M
Large_pool_size – set to 1M

Customizing Auxiliary File Location:

Its also possible to relocate the auxiliary instance files to a specific location for various reasons. Using SET NEWNAME FOR DATAFILE is used in the RUN block to achieve the above.

Common Errors triggers during RMAN transportable tablespace process:

Failing due to the insufficient value for shared_pool_size.
Filename conflict.

Limitations:

There are limitation applies specific to this feature.

There must be valid backup of all the datafiles required for this operations. In case there is no backup of the tablespace used for this operation, following error will be thrown on the RMAN prompt:

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed

RMAN-00571: ========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================

RMAN-03002: failure of tranport tablespace command at 05/22/2007 21:59:01
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "JAFFAR"

Automatic auxiliary instance be created on the same host as the source instance.
Dropped and renamed tablespace can’t be used later for RMAN Transport Tablespace.
Tablespace must be self contained.


Happy Reading

Jaffar

5.06.2007

Oracle 10g upgrade.

As part of upgrading all our production databases from 8i & 9i to 10g, last Friday, early morning, we had successfully upgraded our core banking business critical database from 9i to 10gR2.
Prior to this upgrade, we had done the upgrade with other business critical databases, but, this database is very specially, critical, thus our management wanted a special concern while upgrading this database and looked-for an immediate fallback option.
We had one week of time to plan everything and simulate the upgrade on development server to measure the exact downtime and fall back procedure and application testing. Our several attempted simulations took maximum 1 hr of time and we have requested for 1 hr downtime and fortunately we have got 1 hr down time for the upgrade.

Following methods have came to our mind:

Transportable Tablespaces, Rolling Upgrade.
Streams
Standby

Streams : Since we require the fallback option, we tested using streams where we need to install 10g software on a different machine, create database, export and import and configure Streams to replicate changes from 9i to 10g and vice versa. On a granted cut off time, divert all connection from 9i to 10g and still keep the stream replication between 9i and 10g as the fallback option.
We didn’t like this method, because it involved export and import and we don’t want to use export and import facility for such an important upgrade.

And we finally zeroed the Standby method idea. In this method, what we did was, first create a standby database on the same server where primary up and running, keep primary and standby in sync, disconnect all session from the primary database, stop primary database, switchover standby database to primary database. Once the standby became primary, fulfilling all perquisites, like creating SYSAUX tablespace and other stuff, we have started the manual upgrade. The ‘catupgrade’ took 12 minutes for 64 gb database size.
We were very much glad as the upgrade activity finished in 50 minutes and we still had 10 minutes to do all our pre-checks.
In case we couldn’t finish the upgrade in the given time, we simple stopped the standby which turn primary, and started the old primary database as fall back option.
After an upgrade, there were couple of issues which took less time to resolve.

4.30.2007

Standby and Primary Databases on the same host + ORA-1033

As part of 10g upgrade to all our production 9i databases, Next week we planned to upgrade one of the business critical 9i database to 10g. This database is very business critical and 24x7. It hard to get longer downtime. We are lucky enough to get a hour downtime to upgrade this database. The database is size is around 100gb

We thought of minimizing the upgrade process to time by looking deeply into the following options as well thinking to rollback the upgrade in case of application not acceptable of 10g:

Method1:

Installing 10g on a new machine, and import the data. Using streams, replicate the 9i database to 10g and vice versa. This method gives us the rolling back option in case end-users find strange behavior with 9i.

We need to drop this method as we need to initially use export and import whereas we preferred the RMAN backup options. Oracle supports says, this is available with 10g database.

Method2:

Transportable Tablespaces. Installing 10g software on a new machine, export production with transportable options, copy datafiles to new machine, import the transportable tablespace.

This method involves, a database creation and schema creations.

Method3:

Create a standby database, break the standby, cover to primary and do the manual upgrade.

We decided to go with method 3 as its looks more controlled method and easy method.

This database already has a standby database in place, on different machine, DRC location.

However, we thought of creating another standby of this database on the primary database machine and do the upgrade.

In this process, we have initially, stopped the previously existing standby database, and created a new standby database on the same machine where primary database exists and make standby sync with the primary. We wanted to observe for a week and do the upgrade during the morning hours of week-end during non-peak hours.

Once the process finishes, we went home. After sometime, we have got a call from the operations team that they are not able to run the batches due to ORA-1033 error. We have connected through the VPN and investigated about the problem.

When we shutdown the standby, the ORA-1033 error was not occurring, and when standby mounts or keep in managed recovery mode, the ORA-1033 error occurs whenever trying to connect to the primary database.

After lot of investigations, we found out that we have missed to set the SERVICE_NAME parameter in the standby initialization parameter.

Upon setting SERVICE_NAME parameter in the standby, the ORA-1033 error disappears.

We couldn’t believe the this parameter can cause the error.

However, now everything is perfect and just ready for the next week upgrade.

Happy Reading,

Jaffar

4.01.2007

Submit an article/test case to Metalink and you can Win Up to $5,000 in prizes!

ere is your opportunity to showcase your expertise and knowledge of Oracle products and technologies with your peers in the industry। Contribute to Oracle's Customer Knowledge Exchange, the online customer channel that enables you to showcase your expertise, build your own customer network, and share news and information with your peers starting April 1 through May 15, 2007 and become eligible to win a $5,000.00 Oracle University Scholarship and much more.

First to Contribute, First to Win - Gift certificates of $100 to the first 20 contributors to have their article published in Oracle MetaLink – Customer Knowledge Exchange।

Best of Luck,

Jaffar

3.29.2007

Fully Automated TSPITR

Couple of days ago, I have tested Automatic TSPITR feature which is introduced with version 10g. Maybe, many experts might have already discussed about this and might have written articles about this feature। However, I just thought of sharing my experience which could help to the DBA who is not yet aware of this feature or never tested this feature.


Starting with version 10g, Tablespace Point-in-Time Recovery(TSPITR) can also be done automatically, which is referred as fully Automated TSPITR.

This process requires the following two manual steps:

1. Specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets
2. Configure the required channels on Target Instance.

Manual TSPITR is required the following procedure:

The basic procedure for performing user-managed TSPITR is as follows:

Take the tablespaces requiring TSPITR offline.

  • Plan the setup of the auxiliary database.
  • Create the auxiliary database and recover it to the desired point in time.
  • Drop the tablespaces requiring TSPITR from the primary database.
  • Use the transportable tablespace*** feature to transport the set of tablespaces from the auxiliary database to the primary database.
*** Probably in versions 8i and 9i, because transportable tablespace were first introduced with 8i (v. 8.1.5). Version which doesn’t have TTS, can do export and import of desired table/s.

To perform full automated TSPITR, you just need to add AUXILIARY DESTINATION in the RMAN along with recover tablespace.

Assuming that a table has been dropped from tablespace DATATS:

Example:

run
{ RECOVER TABLESPACE datats
UNTIL logseq thread 1 -- thread 1 indicates single instance
AUXILIARY DESTINATION '/u10/temp'; --Auxiliary Location
}

When the above script runs, RMAN then carries out the following steps:

If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database.
Restores a backup control file from a point in time before the target time to the auxiliary instance.
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance.
Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE).
Recovers the restored datafiles in the auxiliary instance to the specified time.
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database.
Shuts down the auxiliary instance.
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.


What I like about this feature is that, given the enough space to the auxiliary location, RMAN will do everything which required for TSPITR and once the process is done it will automatically obliterate the auxiliary instance, including files which restored during this process.

Its really worth to have a test about this feature.

Please note that I have not discussed about the prerequisites of performing TSPITR.

References:

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit003.htm#sthref778
Metalink Note : Getting Started with Transportable Tablespaces - Note:100693.1

Happy reading,
Jaffar



3.18.2007

ALTER DATABASE BEGIN BACKUP

Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.

I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.

Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:

ALTER DATABASE BEGIN BACKUP;

ALTER DATABASE END BACKUP; -- to exit from the backup mode.

In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.

-- The following has done with Oracle 10gR1

SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

-- All the datafiles are in backup mode now

SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

-- All the datafiles are out of backup mode now.

Happy reading,

3.13.2007

One more new Oracle Informative Site

Lately I have learnt hat my boss is maintaining a Oracle Informative Site. Upon visiting to his Oracle site, I feel good as he put a lot of stuff about Oracle (trying to put more), and also what we have done lately at our organization to resolve some of production issues, and couple of interesting articles for downloads.

I must appreciate for his work as he took some quality of time from his busiest schedule to put good stuff at his site and made it feel good site.
Although, the site is not fully done whith he wanted to have, still good to visit. He also promised me that he gonna put more stuff about RAC which we are doing on Stretched Clusters.

I am also going to contribute and participate for few activities on his site and we will put good articles written by us which could be useful in day-to-day business of a DBA and could help to resolve some of issues of production databases.

Following is the link for his site:

http://www.pinnacleoracle.com

Happy reading,

Jaffar

3.03.2007

Known RMAN Performance Problems - Metalink Note : 247611.1

This morning, I have come across of a recently revised Metalink Note : 247611.1 (revised on 27-02.2007) which addressed several known performance problems with RMAN backup and recovery.

The note has workaround for the following bugs:

Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Note 342999.1 First Resync on Production Host After Running RMAN on
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles


Happy reading,

Jaffar

2.28.2007

Ufff... RECYCLE BIN - Three Strange Problems

After long 8 hrs official hours in the office, I was just above to leave home as my week-end starts. I have told my family that I will be on time home and promised them for outing including a good dinner as well as shopping. Unfortunately, I could not make it on time home as I promised, it took more couple of hrs of work due to few silly problems and I thought of just sharing it.

One issue leads to another and that also kept me to resolve another (different) issue.

Problem 1: MMAN terminated the instance

While I was leaving home, my colleague came to me said he found something strange with export and import.

The issue was, he did a user level export (10gR2) and imported in another database also 10gR2. The problem was that, when he made count of tables and indexes on source and target databases to make sure, he found out that at target database around 70 tables are missing compares to source database. I saw few errors in the import log, but, they were java related issues.

As source and target databases on version 10g, I told him to try with EXPDP instead of traditional EXP/IMP. When he started the export data pump using EXPDP, surprisingly. MMAN terminated the instance. We tried to start the databases couple of times, but, instance was continuously crashing.

Searching in the METALINK doesn’t help either as it says it’s a bug and fixed in 10gR2, as we are already on 10gR2, thought something else would be wrong. I just suspected about the SGA parameters, specifically, SHARED_POOL_SIZE. The SHARED_POOL_SIZE parameter was set to 120M, increasing the parameter value from 120M to 250M has resolved the issue.

Problem 2: Difference between source and target – after export and import.

After solving the MMAN instance termination issue, EXPDP and IMPDP doesn’t resolves the problem as it was still 70 tables difference between source and target.

After spending one hour, we found out that those 70 tables were present in the RECYCLE BIN in the source database. This was mystery behind about missing tables in the target database.

Problem 3: Oracle software mount point ownership change.

The whole day we were busy with the new database creation, schema preparation and other database setups for one of the forthcoming IPO.

The UNIX admin gave us the new server with more CPUs and RAM where they simply detach the disk from the old server (where we initially installed and setup everything) to the new server so that we will not loose our work.

When tried to bring up the database on the new server, got permission issues as Oracle doesn’t able to neither read nor write bytes in the controlfile. While checking permissions, everything just looks okay also we were able to create files using TOUCH on the all the mount points.

After some investigations, we found out that the new server Oracle software was installed using Oracle user as the old machine user was Oracle10. Upon on changing the ownership to Oracle10 of Oracle Software filesystem, everything worked as usual.

Meanwhile, I have got around 10 calls from my kids as well my wife as well.

However, we didn’t miss the dinner and shopping.

Happy reading,

Jaffar


2.27.2007

RAC - Reading, Implement & Test

Finally we have got the go signal from the mgmt to test and implement RAC on extended (stretched) clusters for one of the highly OLTP database, this was being discussed for a quite long time. Since, the database is so important, we are carefully doing/testing measuring our scenarios on the RAC.
As an initial step, we have successfully installed Oracle 10gR2 RAC, able to maintain clusters without any issues on AIX, although it took considerable time, finally, we have done it. Now we are thinking of deploying the application to have a test.

I don’t have much experience with RAC nor do I have much knowledge about the through functionality of clusters, RAC and etc.

Considering the above, I though of start learning the concepts of clusters and Oracle RAC functionality. I have ordered following three books from Amazon site:

Oracle 10g RAC Grid, Services & Clustering by Murali Vallath
Oracle Database 10g Real Application Clusters Handbook (Osborne Oracle Press) by K Gopalakrishnan
Pro Oracle Database 10g RAC on Linux: Installation, Administration, and Performance (Expert's Voice in Oracle) by Julian Dyke today!

Yesterday, I got Mr. Gopla’s book and waiting for other two books, expecting end of this week.

Last night I read one chapter about ASM Instance of Gpola’s book. It was a good read; as the author explained very detailed information in a simple format which can be easily digest by anyone who is reading.

I thought of just giving the review of this book once I finish my reading(s).

I am sure, I will be doing it, at least for the sake of writing review, I will read the book thoroughly again and again, until the concepts get cleared to me.

Happy Reading,

Jaffar

2.19.2007

Final GOOD BYE to a GOOD FRIEND!

My neighbor and a beloved friend Mr. Kalyan was died on 11th of this month at the age of 31 due to massive heart attack. It’s been more than a week now, but, I am still not able to digest the truth that he is not between us. I can imagine the pain his family members going through.

He was the founder of www.thenkoodu.com a tamil blogs aggregator / tamil blogs portal web site. He was so innovate and very kind and cooperative to everyone. He was working with Saudi American Bank past 8 yrs or so. He survives with a wife and a daughter of 4 hrs.

For me, he was more than a friend and great motivator all the way. Although, he is not with us, but, his family and himself holds a special place in our life. I just pray god to give good health and happy life to his rest of the family.

A final good bye to you my dear friend.

1.29.2007

couple of 2 day new books added to Oracle docs.

I am sure every one consult/refer to Oracle documentation very frequently.
In addition to 2 Day DBA, today, I have found couple of 2 day books added to the Docs. Library of 10gR2.

These books provide background information for new users:

2 Day + Real Application Clusters Guide
2 day + Performance Tuning Guide.

This is really a good idea for the new users as instead of going across many pages to find the information, now, they can simply get digest concepts in few pages.

New users, who are keen to learn about RAC and Performnace Tuning, they simply can start with those books.

http://www.oracle.com/pls/db102/homepage


Happy reading,

Jaffar

1.28.2007

CONTROLFILE AUTOBACKUP should be TURNED ON??

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN was first introduced with Version 8 through which database server database can be backup. Since then, Oracle has enhanced RMAN features drastically by every release/version. I am not going to discuss neither the RMAN history nor the backup.

I would like to mention here, how enabling few default parameters of RMAN can save the life of dba significantly.

RMAN has following default parameters and its default values:

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO /app/oracle/dbs/snapcf_EHEALTH.f'; # default
I would be talking the benefits of CONTROLFILE AUTOBACKUP.

By default CONTROLFILE AUTOBACKUP is OFF. I would strongly recommend enabling CONTROLFILE AUTOBACKUP ON.

I remember in my early days of DBA job, a lot of notes/articles and many people use to suggest backup the controlfile immediately after any maintenance on the tablespaces, which was a good suggestion.

RMAN just takes away this head ache of backing controlfile after any maintenance on tablespace, when you turn ON CONTROLFILE AUTOBACKUP feature.

Benefits:


With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.

A control file autobackup lets you restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file autobackup.

Whenever you add a datafile/s, resize, increase/decrease the size of datafile/s or etc, controlfile is automatically backed up.

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up.


Conclusion:
You can turn the autobackup feature on or off by running the following commands through RMAN utility:CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP OFF;

Reference:

Oracle documentation – backup and recovery concepts.

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1006.htm#sthref261

1.27.2007

ORA-12537: TNS: CONNECTION CLOSED

As part of my OCM preparation, I have installed Oracle version 9i and 10gR2 respectivelyand also applied patch sets 9.2.0.8 and 10.2.0.3.
Created a test database on version 9i, enabled archived mode in order to practice backup andrecovery scinarios. Thought of having 9i database backup catalog in 10g database.
Therefore, I have created a small database in 10g. A rman user has been created in 10g for 9i databaserman catlaog.
When trying to connect to the rman user from 9i to create catalog, rman was complaining about thecompatibility, although, my 9i version is 9208, I was little bit puzzeled.'
Tried connecting through sqlplus of Oracle 9i to 10g database where I got 'ORA-12537: TNS: CONNECTION CLOSED'
Wasted good amount of time searching on the google and metalink, but, could not done with the error.
Later, I realized that I have listed 10g database SID with 9i listener.Upon moving 10g database SID with 10g listener, all went smoothly and the ORA-12537 gone away.

1.20.2007

You wanna participate in 11g Beta Program?

If you wanna try your hand on 11g beta, here is an url.
Register yourself and try your luck to test 11g beta.

http://otnbeta.oracle.com/bpo/prospects/index.htm

Jaffar

Bug 4200702 - BLOCK CHANGE TRACKING WHEN USING DIRECT LOAD

According to Metalink Note : 300989.1, when block change tracking enabled and using direct load operations, it is like to get an ORA-19694 on Oracle 10.0.1.x version specific to all the platforms due to a bug 4200702.

Possible Symptoms
~~~~~~~~~~~~~~~~~
RMAN-571: ==========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-571: ========================================================== RMAN-3009: failure of backup command on ORA_DISK_1 channel at 02/14/2005 ORA-19694: some changed blocks were not found in the change tracking file

Workaround would be disabling change tracking feature.

According to the note, this is fixed in a patchset 10.1.0.5 and one-off patches are available for 10.1.0.4 and 10.1.0.3.

I guess, who are on 10gR2 and enabled the BCT feature doesn’t have this bug.


Happy reading,

Jaffar

Desktop RAC - Virtually installed and configured.

I have successfully implemented a Desktop RAC scenario which is provided by Oracle at http://www.oracle.com/technology/tech/linux/vmware/index.html.

Initially I have downloaded all the four zip files required to test the Desktop RAC for Red Hot Linux, there are two zip files for Novell SUSE as well.

Note: you are required to register with VMware and Red Hot Linux in order to get the trail version of that software and a unique number.

Following are the two issues I have come across while implementing Desktop RAC.

1. One of the zip file download contains VMware workstation 5.3 download from the VMware site. Unfortunately you see a message at VMware site that VMware workstation 5.3 is no more available for download and it suggest you to download VMware workstation 5.5 version.
This is required because you need to get the unique key to validate the VMware installation.

I have first installed VMware workstation 5.5 and skipped the VMware installation part while running Oracle installation.

2. First two zips contain Oracle RAC software for Linux and each zip contains Disk1 folders respectively. The problem was that while unzipping zip files which has same folder names respectively, needed to rename and this cause problem while setup as the step looking for all the required files under the Disk1 folder. What I did was to cross compare the folder in the Disk1 folders respectively and copy the missing in to one Disk1 folder.

After couple of unsuccessful attempts, finally I have done with Desktop RAC. I can see that Red Hot Linux 4.0 OS installed, Oracle RAC (10gR2) with ASM configured.
Also, it gives you bunch of examples step-by-step in documentation to test the scenarios.
The implementation gives you two instances and a database named as RACDB with ASM configured.

This is good for the beginners to have RAC installed on a PC which doesn’t require state-of-the-art hardware.

I guess, this is not good for an experienced DBA. You can’t install CRS, create RAC database and configuration and etc.

For me, it’s a good chance to start and make myself familiar with RAC commands and do some testing before I go for any training or work with production.

If I come across any good issues, I will update on my blog.

Happy reading,

1.15.2007

New PC!!

Last night I brought a new PC with the latest configuration (I guess). PC contains 2 GB RAM, 250GB hard disk (7600 rpm), dual processor, DVD r/w and Intel original mother board.

Buying PC with the good configuration was a long pending task which fulfilled last night. This is the first step towards my OCM preparation where I would like test all the scenarios mentioned in the OCM Exam topics.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=10gOCM

Second step would be attending the Oracle RAC course to satisfy the prerequisites of having attended two advanced courses conducted by OU. Although, I don’t like the idea two advanced courses attendance to meet the prerequisite, I have to attend against my will if I want to go for OCM.

Final step would be attending for OCM exam.

To meet the second and final step, I need quite a good time as I am paying it from pocket and I would be happier if my employer comes forwards and ready to sponsor for this, which won’t look possible because of the amount.

Hopefully, by end of this year, I will be in a position to invest money on this ** PROJECT** and appear for OCM.


Happy reading,

Jaffar