Wednesday, November 16, 2005

Invalid permissions after installing 10.2

Afeter installing Oracle 10gR2 on Solaris 64-bit, the permission bit of the directories and files under $ORACLE_HOME has been set incorrectly. A lot of directories and files have been set to 0 for others. Hence they are inaccessbile for any other user. This is related to an Oracle bug 4516865. The Doc. 612605.992 in MetaLink discussed how to work around this problem.

Monday, November 14, 2005

Triple size as the source table after copying tables through database links

Today I find out an interesting symptom. After copying tables from the source database in 9.2.0.5 to the target database in 10.1.0.2 via a database link, the size of every column related to CHAR including CHAR and VARCHAR2 in the target database is three times as large as the their source. There is no problem while using exp/imp to clone the tables from the source to the target. I suspect the different character sets in the 2 databases affect the result. It's UTF8 in the source DB as it's AL32UTF8 in the target DB.

Friday, November 11, 2005

Database duplication using RMAN

Scenario 1:

Starting on Nov. 3, 2005
Duplicate ARBORBP without a password file and SQLNET connection, after restoring a full backup taken on Oct. 14, 2005, RMAN errors below appear

RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002: failure of Duplicate Db command at 11/04/2005 06:51:49
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01990: error opening password file '/dg04/vol01/app/oracle/product/9.2/dbs/orapw'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory

Conclusion:
A password file must be created before using RMAN to duplicate a database

Scenario 2:

Starting on Nov. 4, 2005
Creating a password file for the auxiliary instance TEST
Add entry for TEST to listener.ora and tnsnames.ora as well
Duplicate ARBORBP without specifying UNTIL clause, lasting for around 17 hours, after applying the incremental backup taken on Oct. 31, 2005, RMAN errors below appear

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/05/2005 14:33:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44833 scn 572637893394 found to restore
RMAN-06025: no backup of log thread 1 seq 44832 scn 572637892189 found to restore
RMAN-06025: no backup of log thread 1 seq 44831 scn 572637891001 found to restore
RMAN-06025: no backup of log thread 1 seq 44830 scn 572637889406 found to restore
.
.
.

Conclusion:
This is a known problem of DUPLICATE command refers to Oracle Metalik Doc. 274118.1. You must make sure all the archived log required to clone a database have been backed up. UntilNov. 7, 2005, only the archived logs prior to 44635 have been backed up but not all. This caused this try to fail.

Scenario 3:

Starting on Nov. 8, 2005
Specify the UNTIL clause 'sysdate - 2', date back to Nov. 6, 2005
Since there is another incremental backup on Nov. 7, 2005, it's expected all the necessary archived log have already been backed up
On Nov. 4, 2005, there is a new tablespace BCS2004 added into the DB, hence a new datafile. At the first try, there is no entry for the newly added datafile in RMAN command file. The below error appears.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 10:31:08
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /dg02/vol04/oradata/arborbp/bcs2004_01.dbf conflicts with a file used by the target database

At the second try, an entry below added into RMAN command file to reflect this change.

SET NEWNAME FOR DATAFILE 27 TO '/dg02/vol01/oradata/restore/bcs2004_01.dbf';

However, still get the error below

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 11:09:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 27 found to restore

Finally, the file entry is removed from the RMAN command file and a SKIP TABLESPACE claus is added to skip the newly added tablespace BCS2004. Moreover, to specify the point in time more accurately, using UNTIL SEQUENCE instead of TIME. A sequence number 44965 of the last archive log which has been backed up to tape on Nov. 7, 2005 has been specified. Without restoring the incremental backup taken on Nov. 7, 2005, the incremental backup taken on Oct. 31, 2005 has been applied. Afterwards, Oracle began to applying the archived log from 44635 which is generated right after finishing the incremental backup on Oct. 31, 2005. After applying around 200 archived logs without any problem, the below erros appear while encountering the log 44835.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2005 05:16:51
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/dg02/vol01/oradata/restore/arch/arch_1
_44835.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 27: '/dg02/vol04/oradata/arborbp/bcs2004_01.dbf'

The log 44835 was produced on Nov. 4, 2005. The newly added datafile was recorded in it.

Conclusion:
You must use a full set of backups to duplicate a database. From the time when the latest inremental 0 backup is taken to a point in time you want the duplicating database recovered to, there should NOT be any structural change like adding datafile.

Scenario 4:

Starting on Nov. 10, 2005
Still no entry for the newly added file and SKIP TABLESPACE BCS2004, this time the archived log 44640 is specified in SET UNTIL clause. This should meet all the requirements to duplicate a database. All the necessary logs have been backed up to tape; from the last incremental level 0 backup taken on Oct. 14, 2005 to the archived log 44640, no datafile has been added. There are 2 incremental level 1 backup taken on Oct. 24, 2005 and Oct. 31, 2005 respectively and a few number of archived logs between 44635 and 44640 to be applied.
Without luck, got errors below again!

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2005 11:32:07
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44635 scn 572614439208 found to restore

After looking into the case, the root cause of this error is because we are using CONTROLFILE of the target database rather than the RMAN catalog. Due to the limited space in the controlfile, after finishing the incremental level 1 backup taken on Nov. 7, 2005, all the archived log backed up on Oct. 31, 2005 have been flushed out of the controlfile. Since 44635 is the last one, there is no trace of it.

RMAN> list backup of archivelog all completed before '31-oct-2005';


RMAN> exit

However, all the archived logs from 44636 do exist in the control file since they have been backed up on Nov. 7, 2005 in the inremental level 1 backup.

Finally things turn out to be a little simple! According to the Metalink Doc. 274118.1, I manually restored the required archived logs between 44635 and 44640 in the production database ARBORBP by connecting to the RMAN catalog. I then transferred them to the auxiliary box to be applied to the auxiliary instance. The rest of work is pretty straightforward! Using SQL*Plus to recover the auxiliary instance and open it with RESETLOGS option. I didn't bring the instance to 44640 since it's not necessary. I only brought it to 44638, a consistent state!

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Nov 11 12:17:08 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> recover database using backup controlfile;
ORA-00279: change 572614439988 generated at 10/31/2005 17:28:33 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44635.arc
ORA-00280: change 572614439988 for thread 1 is in sequence #44635


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44635.arc' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cacel
ORA-00308: cannot open archived log 'cacel'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'


SQL> recover database using backup controlfile;
ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44636.arc' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 572614825088 generated at 11/01/2005 06:07:50 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44638.arc
ORA-00280: change 572614825088 for thread 1 is in sequence #44638
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44637.arc' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

Monday, November 07, 2005

My problematic 7D get replaced yesterday


Lotus
Originally uploaded by Flyhorse@LonelyPlanet.
Last saturday the shopper Digi 33 called me and said there are Minolta 7Ds available while I was on the way to Shen Zhen. Pretty excited since I was told previously I may have to wait for a long time.

Well... yesterday I went to the shop to replace the problematic 7D. Again, obviously the so-called new 7D is not a brand-new one. I found out the S/N is even earlier than mine. With the caution, I thoroughly chcked it. There was some hair on the mirror and the WB button was moved. These made me very uncomfortable since the shopper is not honest at all! They even told me there is no way to replace once more and they were out of stock again! What they can do is to fix my problematic one! Jesus! I don't want them to fix my 7D. Who knows where they will bring my 7D, probably not the Minolta maintenance centre. I'd rather pay Minolta to fix it if I must fix it! Anyway, using my blower, I wiped the hair off the mirror. Through a thorough examination, there was not any other problem for the time being.

My wife and I were going to Repulse Bay after the replacement. However, we decided to go to Hong Kong park to test this new 7D. This picture has been taken in the park yesterday. Using Sigma 70-210/2.8 at 210mm end, I took this picture without a tripod at f5.6@40 with ISO 100. Minolta's unique anti-shake function is pretty cool. This picture is very sharp even it's was taken at 1/40" at 210mm without a tripod.

Wednesday, November 02, 2005

How to install Veritas DB Agent for Oracle on Solaris

Well... it's pretty straightforward. If you install from a CD natively, it won't be a problem; if you install from a CD remotely, you need to tar the whole CD first and untar into a temporary directory in the target box as root. Simply run 'install' shell script and complete the installation by following the steps.

After the installation, you need to make a symbolic link for the Media Manager library in the Oralce home.

$ > cd $ORACLE_HOME/lib
$ > ln -s /usr/openv/netbackup/bin/libobk.so64.1 libobk.so

However, there is no need to relink the Oracle executable as some doc. ask you to do so. When you make a call in RMAN, the library will be linked dynamically by Oracle.

To test the connectivity, run RMAN:

RMAN> run {
2> allocate channel ch1 device type 'sbt_tape' parms 'ENV=(NB_ORA_POLICY=ipdev_oracle,NB_ORA_SERV=maple)';
3> }

allocated channel: ch1
channel ch1: sid=27 devtype=SBT_TAPE
channel ch1: VERITAS NetBackup for Oracle - Release 5.1 (2004043016)
released channel: ch1

A successful installation!