Friday, September 11, 2009

Changing the Database Name in 10g

Changing Database ID and Name is just so easy as 1,2 and 3...
Here it goes:
NID --> is the command that is used to change the database name.

Let's say I need to change the database name from SAN1 to SAN2.

Steps to follow:
01. No need to say Backup is important correct !!!
02. Shutdown the database that needs a database name change
03. Startup in MOUNT mode
04. Run NID command:
nid TARGET=sys/sys_password_here@SAN1 DBNAME=SAN2
In the above command:
nid --> Oracle Command Tool DBNEWID
Target --> The database connection information for the source database that needs a name change.
DBNAME --> Its the New name for your database
05. From the NID command, it will prompt you to make up your mind before renaming the database. Respond according to your mind set to that prompt and continue.
06. Once its done, you need to set your db_name parameter in the spfile.
07. Make sure to set your oraenv is still pointing to the OLD DB Name (SAN1)
08. So, Start the database in MOUNT mode. (dont worry about ORA-01103 here)
09. change the spfile:
SQL> alter system set db_name=SAN2 scope=spfile;
10. Come out from sqlplus and create a password file using ORAPWD utility
11. Copy/Change your initfile (that refers to your spfile) name to reflect the NEW DB Name
12. Connect to sqlplus and start the database in MOUNT mode
13. Now Open with resetlogs:
SQL> alter database open resetlogs;
14. Last but least, reload the listener:
lsnrctl reload
15. Now the database name is SAN2 and Listener status also shows its active and you are good to go.

Following is the run thru of the above steps:

/oracle/product/10.2.0/db_1:(+ASM)$ . oraenv
ORACLE_SID = [+ASM] ? SAN1
/oracle/product/10.2.0/db_1:(SAN1)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:34:31 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
/oracle/product/10.2.0/db_1:(SAN1)$ nid TARGET=sys/lmao_sorry@SAN1 DBNAME=SAN2

DBNEWID: Release 10.2.0.4.0 - Production on Wed Aug 26 11:36:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database SAN1 (DBID=3609722023)

Connected to server version 10.2.0

Control Files in database:
+DGROUP1/SAN1/control01.ctl
+DGROUP1/SAN1/control02.ctl
+DGROUP1/SAN1/control03.ctl

Change database ID and database name SAN1 to SAN2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1234567890 to 0987654321
Changing database name from SAN1 to SAN2
Control File +DGROUP1/SAN1/control01.ctl - modified
Control File +DGROUP1/SAN1/control02.ctl - modified
Control File +DGROUP1/SAN1/control03.ctl - modified
Datafile +DGROUP1/SAN1/datafile/system.350.695906669 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/undots1.1361.695906675 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/sysaux.348.695906679 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/users.1201.695906683 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/datafile/tools.345.695906685 - dbid changed, wrote new name
Datafile +DGROUP1/SAN1/tempfile/temp.1205.695906681 - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control01.ctl - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control02.ctl - dbid changed, wrote new name
Control File +DGROUP1/SAN1/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to SAN2.
Modify parameter file and generate a new password file before restarting.
Database ID for database SAN2 changed to 169189862.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

/oracle/product/10.2.0/db_1:(SAN1)$ . oraenv
ORACLE_SID = [SAN1] ? SAN1
/oracle/product/10.2.0/db_1:(SAN1)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:41:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
ORA-01103: database name 'SAN2' in control file is not 'SAN1'


SQL> alter system set db_name=SAN2 scope=spfile;

System altered.

SQL>


$> orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdSAN2 password=lmao_sorry entries=10

$> Copied existing initSAN1.ora to initSAN2.ora

/oracle/product/10.2.0/db_1/dbs:(hmm9test)$ . oraenv
ORACLE_SID = [hmm9test] ? SAN2
/oracle/product/10.2.0/db_1/dbs:(SAN2)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:52:46 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 2083656 bytes
Variable Size 704644280 bytes
Database Buffers 536870912 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL>

SQL> alter database open resetlogs;

Database altered.

SQL>

/oracle/product/10.2.0/db_1/dbs:(SAN2)$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 26-AUG-2009 11:54:29

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
/oracle/product/10.2.0/db_1/dbs:(SAN2)$

Wednesday, July 8, 2009

olsnodes hangs!!!

Alright, our SA team wanted to change the SAN storage and had to reboot the servers. Once the RAC servers got rebooted, it wont let me get to the prompt when trying to login with Oracle user.
After debugging a while, I found that its due to . oraenv in the .profile thats halting my login. So, removed . oraenv with in the .profile and loged in.
Now, its time to digg in to see why . oraenv is halting.
I had my own peice of code kept in that oraenv file to get the node# by using olsnodes command and thats where its halting the process!!!
Strange isnt it. Generally olsnodes command should return the number of nodes in the RAC in seconds. When I run olsnodes it just hangs and never comes back.
Found this metalink id: 729349.1. !!!an unpublished bug#6004127!!!
Basically, what its doing is going thru all the log files under $CRS_HOME/log//clients/ directory to create a new css*.log file and my box had too many to halt the command :)
So, remove all those log files from that folder and olsnodes works like a champ...

Thursday, July 2, 2009

Long running Update SQL

Not the first time that I have done such thing(s) but this time I decided to write it here.

There was this update statement which was written quite efficiently to update a table rows by matching with another table entries. The other table will have the original rowid of the main table that's being updated (basically need to modify the main table column type so had to create a temp table with all the records for those columns and stored the rowid also to make the later update faster).

So, when the table ddl operation is done, the update statement triggers and even with the rowid search criteria the update was taking more than one hour (killed the process after an hour).

So, made that as a pl/sql block by updating a single row at a time in a loop and it finishes in less than a minute.

So, pl/sql block to update a table is not bad as every body thinks. Of-course its like a small code rather than a single SQL but its sure very efficient as the single update statement has to do all searching, matching and updating in the memory where as the pl/sql block don't have to deal all of the records at once.

update sql:
update table1 a
set (a.col1, a.col2) = (select b.ncol1, b.ncol2
from temp_table1 b
where a.rowid = b.org_rowid)
where exists (select 1 from temp_table1 where a.rowid = org_rowid);

pl/sql Block:
begin
for i in (select org_rowid, ncol1, ncol2 from temp_table1) loop
update table1
set col1 = i.ncol1, col2 = i.ncol2
where rowid = i.org_rowid;
end loop;
end;

Hope this might help to some1.

I really appreciate if there is any better of handling this.