ORACLE-BASE - Oracle9i DBNEWID Utility

ORACLE-BASE - Oracle9i DBNEWID Utility

DBNEWID Utility

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Oracle 9i Release 2 (9.2.0.1)

In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.

This paper does not cover Oracle 9i Release 1, as I did not try the following procedures against it. The rest of this paper covers Oracle 8i.

Oracle 8i

In Oracle 8i, Oracle provides the procedure “dbms_backup_recovery.zeroDbid” for this purpose. While Oracle does not formally support it, Oracle note 174625.1 covers its usage.

If you are using RMAN and choose to use the following method based on Note 174625.1, please keep in mind Notes 105832.6 and 1062736.6 Should RMAN already know about the original and clone databases, you will need to deregister and re-register the databases with RMAN.

The basic concept is:

1. With the database in mount but NOT open mode.
2. Use dbms_backup_recover.zeroDbid to zero the DBID
3. Recreate the control files to assign a new DBID to the database.

DISCLAIMER

Neither Oracle support nor I will provide support for the following procedure. I can only say it worked for me. You perform it at your own risk. According to Oracle Support it runs the risk of rendering the database unusable, and therefore exists for the purpose of testing in a non-production environment. Never use this procedure on a production system; it should only be run on clone copies.

As always, before any major or significant changes to a database, you should perform a cold (closed) backup.
Step by Step Instructions
To Change an Oracle Database Identifier (DBID)

1. Connect to the database in SYSDBA mode.
2. Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.

shutdown immediate

3. Startup the database in mount mode

statup mount

4. Check the present (soon to be old) database ID (DBID)

select dbid, name

from v$database;



DBID NAME

---------- ---------

578456994 SNEAKERS



Elapsed: 00:00:00.20

5. Generate the create controlfile statement into a trace file.

alter database backup controlfile to trace;

6. Generate a new DBID using the zeroDbid function of dbms_backup_restore

execute dbms_backup_restore.zeroDbid(0)

File: DBMSBKRS.SQL

NOTE: The value zero (0) is a special case, which is the case we need.



PROCEDURE zeroDbid(fno IN binary_integer);



-- This procedure is used to update the header of a datafile so that the

-- a new dbid would be calculated later on with a create controlfile

-- statement. This is necessary for the cloning of a database

-- The file to update is identified by the fno input parameter, a special

-- case is allowed when fno == 0, such that all the headers of the datafiles

-- in the control file are zeroed out.

-- Three fields in the datafile header (stored in the first block)

-- are zeroed out:

-- The field that holds the database id

-- The checksum field

-- The bit that signals that the checksum is valid

--

-- There are no return values

--

-- Input parameters:

-- fno

-- Identifies the file number of the datafile that will be modified

-- If it is zero, all datafiles listed in the controlfile are updated.

-- Output parameters:

-- NONE



7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles (Unix: rm; Windows erase).



9. Start up the database in nomount mode.

startup nomount

10. Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).

Change from:

CREATE CONTROLFILE REUSE DATABASE "SNEAKERS" NORESETLOGS NOARCHIVELOG

Change to:

CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

11. Open the database.

alter database open resetlogs;

12. Check the new DBID. It should be different.

select dbid, name

from v$database;



DBID NAME

---------- ---------

689567005 SNEAKERS



Elapsed: 00:00:00.20

How to script a SQL Server a Job with Visual Basic and SQL-DMO...

How to script a SQL Server a Job with Visual Basic and SQL-DMO...
Try this. Fill in the blanks as indicated by << >>. Put it in a job with a ActiveX Script step.
One thing you might want to check is where the filename is generated. If you have any weird characters it might generate an invalid filename.

Dim conSrvr, Srvr, oJB, strJob
Dim fso, iFile, strFldr, strFilename
Dim dteNow, strDate

strServer = "<<>>"

dteNow = Now()
strDate = CStr(YEAR(dteNow))
strDate = strDate & RIGHT("00" & MONTH(dteNow),2)
strDate = strDate & RIGHT("00" & DAY(dteNow),2)
strDate = strDate & RIGHT("00" & HOUR(dteNow),2)
strDate = strDate & RIGHT("00" & MINUTE(dteNow),2)
strDate = strDate & RIGHT("00" & SECOND(dteNow),2)

strFldr = "<<>>" & strServer & "\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect strServer

For Each oJB In conServer.JobServer.Jobs
strFilename = strFldr & oJB.Name & ".sql"

Set iFile = fso.CreateTextFile(strFilename, True)

strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf

iFile.Write (strJob)
iFile.Close

Next

Set conServer = Nothing
Set fso = Nothing

Monitoring Oracle processes on Windows

Monitoring Oracle processes on Windows NT:
Monitoring Oracle processes in Windows NT
Ravinder Bhalla
06 Oct 2004
Rating: -4.33- (out of 5)

It's much easier to monitor CPU usage in a Unix environment for each individual Oracle process (i.e., PMON, SMON, DBWR) as compared to NT. In NT, there is only a single process (i.e., oracle.exe). In order to get the status of individual threads of Oracle processs in a Windows environment, use Quick Slice, a GUI-based standalone application. It's a free download from Microsoft. It works with Oracle8i and 9i in all Windows environments. Here is how to use it:

1. First of all, you need to find the process id of all threads running in Oracle.

SQL> select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr;

Here is the output:

Thread ID Backg User Name OS User STATUS Session ID Serial No.
OS Program
------------ ----- ---------- ---------- -------- ---------- ----------

20229 PMON Oracle ACTIVE 1 1
oracle@db02.s0.gc.media.com (PMON)
20231 DBW0 Oracle ACTIVE 2 1
oracle@db02.s0.gc.media.com (DBW0)
20233 LGWR Oracle ACTIVE 3 1
oracle@db02.s0.gc.media.com (LGWR)
20235 CKPT Oracle ACTIVE 4 1
oracle@db02.s0.gc.media.com (CKPT)
20237 SMON Oracle ACTIVE 5 1
oracle@db02.s0.gc.media.com (SMON)
20239 RECO Oracle ACTIVE 6 1
oracle@db02.s0.gc.media.com (RECO)
20241 ARC0 Oracle ACTIVE 7 1
oracle@db02.s0.gc.media.com (ARC0)
20243 ARC1 Oracle ACTIVE 8 1
oracle@db02.s0.gc.media.com (ARC1)
20245 DMON Oracle ACTIVE 9 1
oracle@db02.s0.gc.media.com (DMON)
18460 SYS Oracle ACTIVE 17 22737
sqlplus@db02.s0.gc.media.com (TNS V1-V3)
15700 Oracle ACTIVE 12 7
oracle@db02.s0.gc.sj.ipixmedia.com (LNS0)

In this example, thread 20229 is PMON and 20231 is DBW0, etc.

2. Now doubleclick on the qslice.exe file under "c:Program FilesResource Kit."

3. To display the individual threads of a process, find the process -- oracle.exe and double click on it.

Another graphical window will pop up showing all of the threads and the cpu usage of each.

4. To identify the threads, match up the TID column to the thread ID column of the previous SQL statement. To match the TID numbers with the Oracle thread process ID you will need to convert the TID number from HEX to DEC using a scientific calculator.

The red bar displays kernel time taken by that thread; the blue bar displays user time.

ITworld.com - Zipping your way to free space: Part 2

ITworld.com - Zipping your way to free space: Part 2: "bash-2.03$ ./compressTest

real 1:55.6
user 1:41.1
sys 11.2
compress 57% reduction

real 54.6
user 29.1
sys 22.1
pack 26% reduction

real 2:33.0
user 2:23.6
sys 5.7
gzip 71% reduction

real 2:39.5
user 2:29.2
sys 6.1
zip 71% reduction

real 11:35.1
user 11:14.3
sys 4.5
bzip2 74% reduction

As you can see from these results, my best compression ratio resulted from the bzip2 compression, though this command took quite a bit more time to run than all of the other commands. Whether or not compression time should be given serious consideration in determining which compression tool you should use probably depends on how many files you need to compress and whether you need to compress these files in a narrow window.

The gzip and zip commands were neck in neck -- both with respect to compression and with respect to running time. Since my calculation ignored decimal places, any difference in the performance of these two tools has been obliterated by the simplicity of my math.

The script that I used to generate these time and compression results is shown below. Notice that I take care to preserve the original file, so that it is available for each compression operation. Also, because the syntax for the zip command is different -- requiring a file name to be specified -- I put my compression commands in a case statement.


#!/bin/bash

if [ '$1' == '' ]; then
echo -n 'file> '
read $file
else
file=$1
fi

orig_sz=`ls -l $file | awk '{print $5}'`
mv $file $file.$$

for tool in compress pack gzip zip bzip2
do
cp $file.orig $file
case $tool in
compress) time compress $file
comp_sz=`ls -l $file.Z | awk '{print $5}'`
;;
pack) time pack $file > /dev/null
comp_sz=`ls -l $file.z | awk '{print $5}'`
;;
gzip) time gzip $file
comp_sz=`ls -l $file.gz | awk '{print $5}'`
;;
zip) time zip $file.zip $file > /dev/null
comp_sz=`ls -l $file.zip | awk '{print $5}'`
;;
bzip2) time bzip2 $file
comp_sz=`ls -l $file.bz2 | awk '{print $5}'`
;;
esac

percent=`expr $comp_sz \* 100 / $orig_sz`
reduction=`expr 100 - $percent`
echo $tool ${reduction}% reduction
done

mv $file.$$ $file
# rm -i $file.*


Starting with a file name "myfile", you will end up with a series of files such as those shown below. If you don't want to keep these files around after you've completed your timing/efficiency tests, uncomment the remove command at the bottom of the script.

-rw-r--r-- 1 shs staff 291209216 Feb 3 10:14 myfile
-rw-r--r-- 1 shs staff 127372957 Feb 3 10:16 myfile.Z
-rw-r--r-- 1 shs staff 76466288 Feb 3 10:27 myfile.bz2
-rw-r--r-- 1 shs staff 85682214 Feb 3 10:20 myfile.gz
-rw-r--r-- 1 shs staff 217362875 Feb 3 10:18 myfile.z
-rw-r--r-- 1 shs staff 85682333 Feb 3 10:26 myfile.zip

Next week, we will examine the remaining compression considerations -- whether the compression algorithm is patented and whether the compression commands have other limitations that you need to consider.

"