Author Archives: alesk
Toad 9.6 with Oracle11g Instant Client
While working on my Windows x64 test server, trying to reproduce (yet another) critical Oracle bug related to compressed tables, I thought some help from the GUI tool can easy my research. Of course, my first thought was to use SQL Developer, but in this particular case Toad was a better choice. Since all other Oracle Software installed on this test box is 64-bit, I didn’t want to install full blown 32-bit Oracle client just for the sake of running Toad. Instead I downloaded 32-bit Oracle instant client 11.1.0.6.
My first attempt of setting environment was something like this:
:: Oracle11g (32-bit, instant client) title Oracle11 (32-bit, instant client) set PATH=D:\ORACLE\instantclient_11_1;%PATH% set ORACLE_HOME=D:\ORACLE\instantclient_11_1 set TNS_ADMIN=D:\ORACLE\instantclient_11_1 start D:\ORACLE\Toad\Toad
The important part is line with SET PATH, where I put 11g client in the front of other directories, already in the path variable, including some 10g homes. It happens that this is not enough. It’s mandatory that you exclude from the path all other Oracle client software that is not part of the instant client.
So the version that works is slightly modified:
:: Oracle11g (32-bit, instant client) title Oracle11 (32-bit, instant client) set PATH=D:\ORACLE\instantclient_11_1;%WINDIR%;%WINDIR%\SYSTEM32 set oracle_home=D:\ORACLE\instantclient_11_1 set TNS_ADMIN=D:\ORACLE\instantclient_11_1 start D:\ORACLE\Toad\Toad
When you run Toad for the first time you’ll likely see combo box with empty list of TNS aliases. Don’t worry, just key in Oracle TNS alias that you want to connect to (assuming that you put valid TNSNAMES.ORA, SQLNET.ORA in the directory from TNS_ADMIN variable).
Disclaimer:
Installing client GUI tools, such as Toad on production server(s) is not recommended – don’t do it :-). I chose to install Toad on this particular test server because the machine is in the sand-box environment that prevents connectivity from the net.
How to backup MS Analysis Services OLAP db with ascmd
I’m not sure why Microsoft doesn’t ship compiled version of ascmd with SQL Server 2005!? Ascmd as you might know is command lined tool that you can use to execute xmla scripts in addition to MDX queries and DMX statements.
My only interest at present is to use ascmd to backup OLAP mode database from command line (if you’re storing data as ROLAP or HOLAP, then make sure you read Backup Strategies with SQL Server 2005 Analysis Services).
Essential references:
- Readme For Ascmd Command-line Utility Sample
- I found compiled version of ascmd at Project Real homepage, more precisely in package ProjectREALRefImpl_10.zip. Unpack ProjectREALSampleCodeAndTools.msi from zip file and install the package, then search for ascmd.exe. I picked up binary for x64.
Copy ascmd to your OLAP server (like ?:\MSSQL\MSSQL.1\OLAP\bin). All that you need now is xmla script that can be generated within MS SQL Server Management Studio and simple batch file, such as:
SET PATH=?:\MSSQL\MSSQL.1\OLAP\bin;%PATH% ascmd -i BackupOLAP.xmla if errorlevel 0 echo Backup BACKUPOLAP= OK > ?:\OLAPBackup\BackupOLAP.log if errorlevel 1 echo Backup BACKUPOLAP= ERROR > ?:\OLAPBackup\BackupOLAP.log
and BackupOLAP.xmla might look like this:
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <File>?:\OLAPBackup\BackupOLAP.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>
…replacing ? with the appropriate drive letter, of course.
Is it possible to restore Oracle 9.2 RMAN backup with 10.2.0.4 version?
This question someone asked on dba-village.com forum and got short (and incorrect) answer: NO.
Actually, this is possible because RMAN can read from backupset files written by older versions. The same is true for patchset differences, 10.2.0.2 backupset can be used for a restore with RMAN 10.2.0.4. We can also restore 32-bit database backup set with 64-bit version. Of course, keep in mind that simply restoring older database with newer version doesn’t give you “instant” upgraded database – all upgrade steps must be performed after a restore (yes, you’ll have to read that manual, afterall;-). I’m not sure how far in the past this backward compatibility goes – I don’t have test environment with 8i or even 8.0 to try this out.
Refer to Metalink Note:369644.1 “Answers To FAQ For Restoring Or Duplicating Between Different Versions And Platforms”.
I tested 9.2->10.2 scenario by restoring 9.2.0.8 backupset with 10.2.0.4 RMAN.
--
-- Connecting to Oracle 9.2.0.8 (noarchivelog) database
--
Recovery Manager: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORA9 (DBID=422058228)
using target database controlfile instead of recovery catalog
D:\Oracle>rman target / nocatalog
-- -------------------------------------
-- Clean shutdown
-- -------------------------------------
RMAN> shutdown
database closed
database dismounted
Oracle instance shut down
-- ------------------------------------
-- Let's mount the database...
-- (for the sake of simplicity I
-- left 9i db in noarchivelog mode)
-- ------------------------------------
RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 705765992 bytes
Fixed Size 455272 bytes
Variable Size 301989888 bytes
Database Buffers 402653184 bytes
Redo Buffers 667648 bytes
-- --------------------------------------
-- Backup 9i database with 9i RMAN
-- --------------------------------------
RMAN> run {
2> allocate channel d1 type disk;
3> backup format 'D:\ORADATA\ORA9\BACKUP\%d_%t_%s_%p.bak' database;
4> backup current controlfile format 'D:\ORADATA\ORA9\BACKUP\ctrl_%d_%u_%s.bak';
5> release channel d1;
6> }
allocated channel: d1
channel d1: sid=11 devtype=DISK
Starting backup at 05.06.08
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORADATA\ORA9\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORADATA\ORA9\UNDOTBS01.DBF
input datafile fno=00007 name=D:\ORADATA\ORA9\XDB01.DBF
input datafile fno=00006 name=D:\ORADATA\ORA9\USERS01.DBF
input datafile fno=00004 name=D:\ORADATA\ORA9\INDX01.DBF
input datafile fno=00003 name=D:\ORADATA\ORA9\DRSYS01.DBF
input datafile fno=00005 name=D:\ORADATA\ORA9\TOOLS01.DBF
channel d1: starting piece 1 at 05.06.08
channel d1: finished piece 1 at 05.06.08
piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK comment=NONE
channel d1: backup set complete, elapsed time: 00:01:15
Finished backup at 05.06.08
Starting backup at 05.06.08
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current controlfile in backupset
channel d1: starting piece 1 at 05.06.08
channel d1: finished piece 1 at 05.06.08
piece handle=D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 05.06.08
Starting Control File and SPFILE Autobackup at 05.06.08
piece handle=D:\ORACLE\ORA9\DATABASE\C-422058228-20080605-02 comment=NONE
Finished Control File and SPFILE Autobackup at 05.06.08
released channel: d1
-- ---------------------
-- Shutdown 9i instance
-- ---------------------
RMAN> shutdown
database dismounted
Oracle instance shut down
-- -----------------------------------------
-- Prepare 10g instance with which you'll
-- restore 9i backup (this step is out of
-- the scope of this note!)
-- -----------------------------------------
Prior to excecuting restore of 9i RMAN backup with
10g RMAN I took backup of 9i files and deleted them
from original location.
As shown below, there are no
datafile in directory D:\ORADATA\ORA9 directory
D:\ORADATA\ORA9>dir
Volume in drive D is SW
Volume Serial Number is 408C-7E40
Directory of D:\ORADATA\ORA9
05.06.2008 23:24 <DIR> .
05.06.2008 23:24 <DIR> ..
05.06.2008 23:08 <DIR> BACKUP
05.06.2008 23:23 <DIR> BACKUP9_ORIGINAL
0 File(s) 0 bytes
And the RMAN backup directory contains:
D:\ORADATA\ORA9\BACKUP>dir
Volume in drive D is SW
Volume Serial Number is 408C-7E40
Directory of D:\ORADATA\ORA9\BACKUP
05.06.2008 23:08 <DIR> .
05.06.2008 23:08 <DIR> ..
05.06.2008 23:08 1.638.400 CTRL_ORA9_08JI6V06_8.BAK
05.06.2008 23:08 517.414.912 ORA9_656636859_7_1.BAK
2 File(s) 519.053.312 bytes
2 Dir(s) 4.094.455.808 bytes free
-- -----------------------------------------
-- Restore 9i backupset with 10g
--
-- (Note: I used 9i pfile to start 10g instance
-- that's what caused some warnings about
-- obsolete parameters.)
-- -----------------------------------------
D:\Oracle>rman target / nocatalog
Recovery Manager: Release 10.2.0.4.0 - Production on Cet Jun 5 23:29:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 683671552 bytes
Fixed Size 1298592 bytes
Variable Size 276828000 bytes
Database Buffers 402653184 bytes
Redo Buffers 2891776 bytes
-- -----------------------
-- Restore control file...
-- -----------------------
RMAN> run {
2> allocate channel d1 type disk;
3> restore controlfile from 'D:\ORADATA\ORA9\BACKUP\CTRL_ORA9_08JI6V06_8.BAK';
4> release channel d1;
5> }
allocated channel: d1
channel d1: sid=157 devtype=DISK
Starting restore at 05.06.08
channel d1: restoring control file
channel d1: restore complete, elapsed time: 00:00:03
output filename=D:\ORADATA\ORA9\CONTROL01.CTL
output filename=D:\ORADATA\ORA9\CONTROL02.CTL
output filename=D:\ORADATA\ORA9\CONTROL03.CTL
Finished restore at 05.06.08
released channel: d1
-- ------------------------
-- Mount database...
-- ------------------------
RMAN> alter database mount;
database mounted
-- ------------------------
-- Restore database...
-- ------------------------
RMAN> run {
2> allocate channel d1 type disk;
3> restore database;
4> }
allocated channel: d1
channel d1: sid=157 devtype=DISK
Starting restore at 05.06.08
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\ORA9\SYSTEM01.DBF
restoring datafile 00002 to D:\ORADATA\ORA9\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORADATA\ORA9\DRSYS01.DBF
restoring datafile 00004 to D:\ORADATA\ORA9\INDX01.DBF
restoring datafile 00005 to D:\ORADATA\ORA9\TOOLS01.DBF
restoring datafile 00006 to D:\ORADATA\ORA9\USERS01.DBF
restoring datafile 00007 to D:\ORADATA\ORA9\XDB01.DBF
channel d1: reading from backup piece D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK
channel d1: restored backup piece 1
piece handle=D:\ORADATA\ORA9\BACKUP\ORA9_656636859_7_1.BAK tag=TAG20080605T230738
channel d1: restore complete, elapsed time: 00:01:25
Finished restore at 05.06.08
released channel: d1
-- ----------------------------
-- Open database with resetlogs
-- ----------------------------
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/05/2008 23:41:57
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/05/2008 23:41:57
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
This error is normal, since we're now running 9i database with 10g binaries,
so we'll still have to upgrade 9i to 10g...
-- ---------------------------------
-- Startup database in upgrade mode
-- ---------------------------------
D:\Oracle>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Cet Jun 5 23:42:53 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup upgrade;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 683671552 bytes
Fixed Size 1298592 bytes
Variable Size 276828000 bytes
Database Buffers 402653184 bytes
Redo Buffers 2891776 bytes
Database mounted.
Database opened.
-- ------------------------------------------
-- from here on you have to follow
-- instructions from Oracle Upgrade Guide...
-- ------------------------------------------
Patchset 10.2.0.4 for Windows x64 is finally released
I don’t know what caused a delay in Patchset release for windows x64, considering that 32-bit version was released two months ago! I sincerely hope that Oracle will not announce 10.2.0.4 as a terminal release – they “owe” us at least 10.2.0.5 (if not 10.2.0.6), with so many bugs shipped with initial 10g R2. In addition, most Oracle shops will wait for 11g R2 before they’ll start upgrading core production systems, imho another patchset would be appropriate.
Windows and GPT FAQ
It’s a matter of time when Master Boot Record (MBR) partitioning schema will be replaced by more scalable, flexible and robust GUI Partition Table (GPT) partitioning schema. I found very good Microsoft FAQ article about GPT on Windows OS. I’m sure I’ll need to refer to this article from time to time, so I think it deserves this short note here.
dbaportal.eu 