Some of my fellows ask me how to change the oracle SID. For those who never done these steps see that this is a difficult thing, but it's quite simple though.
1. Login as sysdba
[ora9i@training01 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Nov 13 16:23:56 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
2. Note the user dump directory
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ -----------------------------------------
user_dump_dest string /apps/ora9i/OraHome1/admin/ora
sid/udump
3. Switch logfile several times using the following command.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
4. Create the basic SQL script for renaming the SID.
SQL> alter database backup controlfile to trace;
Database altered.
5. Shutdown the database
SQL> shutdown
SQL> exit
6. Find the file created from step 4 in directory in step 2, and rename it to appropriate name.
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/admin/orasid/udump
[ora9i@training01 ~]$ ls –ltr
-rw-r----- 1 ora9i dba 1617 Nov 3 16:05 oldsid_ora_7120.trc
-rw-r----- 1 ora9i dba 1617 Nov 3 16:08 oldsid_ora_7837.trc
-rw-r----- 1 ora9i dba 1617 Nov 3 16:13 oldsid_ora_3482.trc
-rw-r----- 1 ora9i dba 1616 Nov 3 16:16 oldsid_ora_4412.trc
-rw-r----- 1 ora9i dba 1617 Nov 3 17:59 oldsid_ora_1818.trc
-rw-r----- 1 ora9i dba 3284 Nov 6 11:31 oldsid_ora_1770.trc
-rw-r----- 1 ora9i dba 7095 Nov 13 21:52 oldsid_ora_32410.trc
[ora9i@training01 ~]$ mv oldsid_ora_32410.trc renameorasid.sql
7. Delete unwanted lines and update as necessary, the result appears as below. Please notify the one in bold.
[ora9i@training01 ~]$ vi renameorasid.sql
CREATE CONTROLFILE set DATABASE "orasid" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
'/apps/ora9i/OraHome1/database/datafiles/orasid/logg1m1orasid.log',
'/apps/ora9i/OraHome1/database/datafiles/orasid/logg1m2 orasid.log'
) SIZE 8M,
GROUP 2 (
'/apps/ora9i/OraHome1/database/datafiles/orasid/logg2m1orasid.log',
'/apps/ora9i/OraHome1/database/datafiles/orasid/logg2m2orasid.log'
) SIZE 8M,
GROUP 3 (
'/apps/ora9i/OraHome1/database/datafiles/ orasid/logg3m1orasid.log',
'/apps/ora9i/OraHome1/database/datafiles/orasid/logg3m2orasid.log'
) SIZE 8M
-- STANDBY LOGFILE
DATAFILE
'/apps/ora9i/OraHome1/database/datafiles/orasid/systemorasid.dbf',
'/apps/ora9i/OraHome1/database/datafiles/ orasid/undoorasid.dbf',
'/apps/ora9i/OraHome1/database/datafiles/orasid/exampleorasid.dbf',
'/apps/ora9i/OraHome1/database/datafiles/ orasid/toolsorasid.dbf',
'/apps/ora9i/OraHome1/database/datafiles/orasid/userorasid.dbf'
CHARACTER SET US7ASCII
;
8. Create/move directories and rename the datafiles.
[ora9i@training01 ~]$ mkdir -p /apps/ora9i/OraHome1/admin/orasid
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid /cdump
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid/udump
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid/bdump
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/database/datafiles/
[ora9i@training01 ~]$ mv oldsid orasid
[ora9i@training01 ~]$ cd orasid
[ora9i@training01 ~]$ mv logg1m1oldsid.log logg1m1orasid.log
[ora9i@training01 ~]$ mv logg1m2oldsid.log logg1m2orasid.log
[ora9i@training01 ~]$ mv logg2m1oldsid.log logg2m1orasid.log
[ora9i@training01 ~]$ mv logg2m2oldsid .log logg2m2orasid.log
[ora9i@training01 ~]$ mv logg3m1oldsid.log logg3m1orasid.log
[ora9i@training01 ~]$ mv logg3m2oldsid.log logg3m2orasid .log
[ora9i@training01 ~]$ mv systemoldsid.dbf systemorasid.dbf
[ora9i@training01 ~]$ mv undooldsid.dbf undoorasid.dbf
[ora9i@training01 ~]$ mv example oldsid.dbf exampleorasid.dbf
[ora9i@training01 ~]$ mv toolsoldsid.dbf toolsorasid.dbf
[ora9i@training01 ~]$ mv useroldsid.dbf user orasid.dbf
[ora9i@training01 ~]$ mv tempoldsid.dbf temporasid.dbf
9. Update initialization parameter file
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/dbs/
[ora9i@training01 ~]$ vi initorasid.ora
*.compatible='9.2.0.0.0'
*.control_files='/apps/ora9i/OraHome1/database/datafiles/orasid/control1orasid.ctl','/apps/ora9i/OraHome1/database/datafiles/ orasid/control2orasid.ctl','/apps/ora9i/OraHome1/database/datafiles/orasid/control3orasid.ctl'
*.core_dump_dest='/apps/ora9i/OraHome1/admin/orasid /cdump'
*.user_dump_dest='/apps/ora9i/OraHome1/admin/orasid/udump'
*.background_dump_dest='/apps/ora9i/OraHome1/admin/orasid/bdump'
*.db_block_size=8192
*.db_cache_size=134217728
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=1024
*.db_name='orasid'
*.dml_locks=500
*.fast_start_mttr_target=300
*.global_names=FALSE
*.instance_name=' orasid'
*.java_pool_size=52428800
*.job_queue_processes=2
*.log_buffer=1048576
*.max_dump_file_size='10240'
*.max_enabled_roles=48
*.open_cursors=400
*.os_authent_prefix=''
*.processes=150
*.remote_login_passwordfile='NONE'
*.remote_os_authent=TRUE
*.shared_pool_size=54525952
*.sort_area_size=524288
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.utl_file_dir='*'
9. Create password file
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/dbs/
[ora9i@training01 ~]$ orapwd file=orapw<NEWSID> password=o entries=10
10. Update the environment file in ora9i home directory, and then relogin
[ora9i@training01 ~]$ cd ~
[ora9i@training01 ~]$ vi .login
setenv ORACLE_SID orasid
setenv ORACLE_OWNER ora9i
setenv ORACLE_BASE /apps/ora9i
setenv ORACLE_HOME ${ORACLE_BASE}/OraHome1
11. Running the script.
[ora9i@training01 ~]$ sqlplus "/ as sysdba"
SQL> startup nomount
SQL> @renameorasid.sql
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
SQL> alter database rename global_name to orasid;
12. Restart the database
[ora9i@training01 ~]$ sqlplus "/ as sysdba"
SQL> shutdown
SQL> startup
13. Setting up the client
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome/network/admin
[ora9i@training01 ~]$ vi tnsadmin.ora
orasid =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orasid)
)
)
You see that the main steps are step 4 and 7, which create the script and rename.
Okay, you're now ready with your new name of the database instance. Good luck.
10 comments:
Thanks...gonna try this out
I would appreciate if you could also go into the theoretical aspects of DBA
Merci pour cet article très bien construit!
Your blog keeps getting better and better! Your older articles are not as good as newer ones you have a lot more creativity and originality now keep it up!
[u][b]Xrumer[/b][/u]
[b]Xrumer SEO Professionals
As Xrumer experts, we have been using [url=http://www.xrumer-seo.com]Xrumer[/url] quest of a large time things being what they are and know how to harness the colossal power of Xrumer and turn it into a Cash machine.
We also provide the cheapest prices on the market. Diverse competitors devise cost 2x or even 3x and a lot of the opportunity 5x what we pervade you. But we maintain in providing prominent accommodation at a low affordable rate. The whole direct attention to of purchasing Xrumer blasts is because it is a cheaper variant to buying Xrumer. So we train to abide by that mental activity in recollection and provide you with the cheapest rate possible.
Not solitary do we take the greatest prices but our turnaround in the good old days b simultaneously after your Xrumer posting is super fast. We will pull someone's leg your posting done ahead of you distinguish it.
We also outfit you with a ample log of affluent posts on contrasting forums. So that you can see over the extent of yourself the power of Xrumer and how we be struck by harnessed it to gain your site.[/b]
[b]Search Engine Optimization
Using Xrumer you can trust to apprehend thousands upon thousands of backlinks for your site. Scads of the forums that your Place you force be posted on bear exalted PageRank. Having your join on these sites can really expropriate strengthen up some top-grade rank back links and genuinely riding-boot your Alexa Rating and Google PageRank rating owing to the roof.
This is making your put more and more popular. And with this developing in reputation as superbly as PageRank you can keep in view to lead your place in effect filthy gamy in those Search Engine Results.
Conveyance
The amount of conveyance that can be obtained by harnessing the power of Xrumer is enormous. You are publishing your site to tens of thousands of forums. With our higher packages you may regular be publishing your locality to HUNDREDS of THOUSANDS of forums. Create 1 post on a popular forum will by enter 1000 or so views, with announce ' 100 of those people visiting your site. Modern create tens of thousands of posts on in demand forums all getting 1000 views each. Your freight will go sometimes non-standard due to the roof.
These are all targeted visitors that are interested or curious far your site. Envision how many sales or leads you can fulfil with this considerable gang of targeted visitors. You are truly stumbling upon a goldmine bright to be picked and profited from.
Retain, Transport is Money.
[/b]
BECOME ENTHUSIASTIC ABOUT YOUR CHEAPLY BLAST TODAY:
http://www.xrumer-seo.com
[B]NZBsRus.com[/B]
Dismiss Crawling Downloads Using NZB Downloads You Can Swiftly Find High Quality Movies, Games, MP3 Albums, Software and Download Them @ Blazing Speeds
[URL=http://www.nzbsrus.com][B]Usenet Search[/B][/URL]
whoisjohngalt dating [url=http://loveepicentre.com/]india free dating[/url] teen dating website http://loveepicentre.com/ personals abbreviations
Someone deleted several links from badongo and uploading servers.
From now, we will use www.tinyurlalternative.com as our main [url=http://www.tinyurlalternative.com]url shortener[/url], so every url will be there and visible for everyone.
You can choose from many great [url=http://kfc.ms]short url[/url] address like:
kfc.ms easysharelink.info jumpme.info megauploadlink.info megavideolink.info mygamelink.info myrapidsharelink.info mytorrentlink.info myurlshortener.com mywarezlink.info urlredirect.info urlshrinker.info weblinkshortener.com youtubelink.info and many others.
They maintain above 60 different ready domains and the [url=http://myurlshortener.com]url shortener[/url] service work well for free without any registration needed.
So we think it is good notion and suggest you to use [url=http://urlredirect.info]url redirect[/url] service too!
Thank you.
Bravo, this excellent phrase is necessary just by the way
True phrase
You could easily be making money online in the underground world of [URL=http://www.www.blackhatmoneymaker.com]blackhat marketing[/URL], Don’t feel silly if you have no clue about blackhat marketing. Blackhat marketing uses alternative or little-understood avenues to produce an income online.
Post a Comment