Home » RDBMS Server » Server Administration » change SID (label of a database)
change SID (label of a database) [message #257219] Tue, 07 August 2007 15:48 Go to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Under Oracle 10g 10.2.0.1 Win32:
I have a database with SID=CM3.

I want to create a new CM3 database but... before I want to change CM3 as CM4. So... CM4 being the old CM3.

How can I change de SID value from CM3 to CM4? I mean... change database SID value in the database... and not environment variable ORACLE_SID.

Thank you,


mson77
Re: change SID (label of a database) [message #257228 is a reply to message #257219] Tue, 07 August 2007 16:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
use DBNEWID UTILITY
Re: change SID (label of a database) [message #257230 is a reply to message #257228] Tue, 07 August 2007 16:53 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm#i1004734
Re: change SID (label of a database) [message #257231 is a reply to message #257230] Tue, 07 August 2007 17:01 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hi DreamzZ,

I am laughing here...
The GAP between us is really large...
While I am almost in panic... you always come with brilliant solutions!!! Magic solutions...

I was reading:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:318216852435
and thinking... simple need but hard to implement.
And then... comes on Super-DreamzZ!!!

Thank you very much!
I will try it right now!
I am sure that this will satisfy my need.

Regards,


mson77
Re: change SID (label of a database) [message #257232 is a reply to message #257231] Tue, 07 August 2007 17:03 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
You welcome Wink
Hope so.But follow step by step and make sure you have backup of your database.
Re: change SID (label of a database) [message #257233 is a reply to message #257219] Tue, 07 August 2007 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
2 cents worth from the peanut gallery.
I've never used DBNEWID, because I did not want to expend the effort to truly maintain an Oracle File Architecture (OFA) structure.
So my trade-off has always to live with less than ideal SID names to maintain my sanity during crisis periods.

I'd NEVER use DBNEWID in Production while it might be worthwhile in a sandbox environment.

What's in a name?
Re: change SID (label of a database) [message #257235 is a reply to message #257233] Tue, 07 August 2007 17:37 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Well... the reason to change database SID is:

I have a database called CM3.
This database has been created with this parameters:
a) It is not managed by OEM; ==> I want OEM manage this db; ????
b) It does not have EM repository (sysaux) enabled; ==> I need to create and enable this repository; ????
c) The extents are managed by "dictionay"; ==> I want to be managed "local"; ????
d) The memory (sga: shared pool, cache buffer, java pool, big pool; pga) parts are managed manually; ==> I want 10g manage automatically. ????


If I could do these changes... I would not need to create a new db..., neither the SID... and still use this CM3.

Regards,

mson77
Re: change SID (label of a database) [message #257236 is a reply to message #257235] Tue, 07 August 2007 17:43 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
if you created database in 10g then all these options would be there
==========================================================


Quote:
It is not managed by OEM; ==> I want OEM manage this db; ????


CHECK readme file and portlist.ini in install folder.

Quote:
It does not have EM repository (sysaux) enabled; ==> I need to create and enable this repository; ????


sysaux is MANDATORY in 10g without this you cant make DATABASE.

Quote:
c) The extents are managed by "dictionay"; ==> I want to be managed "local"; ????

in 10g???
SQL> POST THE RESULT FOR THIS QUERY
  1*  select EXTENT_MANAGEMENT from dba_tablespaces
SQL>


Quote:
The memory (sga: shared pool, cache buffer, java pool, big pool; pga) parts are managed manually; ==> I want 10g manage automatically. ????



Use SGA_TARGET parameter in your spfile/pfile.

[Updated on: Tue, 07 August 2007 17:50]

Report message to a moderator

Re: change SID (label of a database) [message #257274 is a reply to message #257236] Tue, 07 August 2007 23:37 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Under Oracle 10g 10.2.0.1 Win32:
I have a database with SID=CM3.



a) It is not managed by OEM; ==> I want OEM manage this db; ????
First check your database is configure for dbconsole
ON windows command prompt.
set oracle_sid=<sid>
emctl getemhome

If you get result like below then you have to configure dbconsole for database

EM Configuration issue. C:\oracle\product\10.1.0\Db_1/hostname_domainname_SID not found.

Through DBCA or EMCA tools you can configure EM Repository.

http://dbataj.blogspot.com/2007/01/oc4j-configuration-issue.html


b) It does not have EM repository (sysaux) enabled; ==> I need to create and enable this repository; ????

Follow above step it will automatically created (SYSMAN Schama)


c) The extents are managed by "dictionay"; ==> I want to be managed "local"; ????


As you mention above your using 10G and in 10g Default Management is LOCAL.


Anyway go through below link and follow mention steps

http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/tspaces.htm#sthref1225


d) The memory (sga: shared pool, cache buffer, java pool, big pool; pga) parts are managed manually; ==> I want 10g manage automatically. ????
Set SGA_TARGET , SGA_MAX_SIZE, PGA_AGGERATE_TARGET parameter


Search on documentation for details of above parameters

Regards
Taj
















[Updated on: Tue, 07 August 2007 23:39]

Report message to a moderator

Re: change SID (label of a database) [message #257511 is a reply to message #257274] Wed, 08 August 2007 09:04 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hi Mohammad,

Thank you for your kind attention.

Mohammad Taj wrote on Wed, 08 August 2007 01:37

ON windows command prompt.
set oracle_sid=<sid>
emctl getemhome

If you get result like below then you have to configure dbconsole for database

EM Configuration issue. C:\oracle\product\10.1.0\Db_1/hostname_domainname_SID not found.

Through DBCA or EMCA tools you can configure EM Repository.




First of all, let me share that this post regards my sandbox experiences... because I am initiating on oracle world since last week... and I may ask for wrong/dumb questions.

Well... regarding this thread. Yes... my instance named CM3... has been created using extents managed by dictionary, memory managed manually supplying fixed values during dbca,... and during db creation with no management via OEM. Just a case that I want to change it further manually using command line tools (I like command like tools as in linux environment because I know each file and its behavior).

First:
Regarding "extents" managed by dictionary... and migrate them to local.
I got this paper and followed step-by-step with success:
http://www.akadia.com/services/ora_locally_managed_tablespaces.html
Now I already have my db extents locally managed.

Second:
Regarding "memory management"...
I created another db with memory managed automatically by oracle 10g. This new db, by default, 10g uses SPFILE. Then I created PFILE from SPFILE.
I took my CM3 instance, which is SPFILE managed, and create PFILE from SPFILE.
Now I compared PFILE from auto-memory mode with PFILE from manual-memory mode... and edit CM3 PFILE... applying "Use SGA_TARGET parameter in your spfile/pfile" as DreamzZ recommended.
Then, after editing I will create the SPFILE for CM3 which has been edited. (I did not apply the changes already.)

Meantime... I received you post... :))) with honor...
Now regarding dbconsole:
I tried initially by dbca... and it looked me so easy... and I gave up.
I tried using emca using this link:
http://www.akadia.com/services/ora_dbconsole.html

But I got this error message:

Quote:
08/08/2007 03:26:56 oracle.sysman.emcp.EMReposConfig invoke
SEVERE: Falha ao desbloquear todas as contas do relacionadas ao EM
08/08/2007 03:26:56 oracle.sysman.emcp.EMConfig perform
SEVERE: Falha ao desbloquear todas as contas do relacionadas ao EM

==> translating: failed trying unlock sysman account
==> I attached the log file from:
C:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\cm3



Then... I tried via dbca mode with same error message.
Maybe it is due I dis changes of extents (dict to local)... et cetera...

I will recreate again the dbs... and try again... and I will post here the results.

Thanks for reading.


mson77
Re: change SID (label of a database) [message #257556 is a reply to message #257511] Wed, 08 August 2007 11:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Mson Keep one thing in mind
if you have PFILE/SPFILE in default folder then oracle will startup automatically with SPFILE.
For example if you edit your PFILE and there is SPFILE then ORCALE WILL USE SPFILE your editing in PFILE is USELESS untill your delete or rename the SPFILE.

You converted your DMT to LMT. Sounds good.

Unlock sysman account by this command.

ALTER USER SYSMAN IDENTIFIED BY PASSWORD ACCOUNT UNLOCK;


can you post commands which you applied for DBCONSOLE?
Re: change SID (label of a database) [message #257559 is a reply to message #257556] Wed, 08 August 2007 11:31 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
CONFIG: Unlocking dbsnmp and sysman


YOu have to also unlock DBSNMP or SYSMAN account.



Regards
Taj
Re: change SID (label of a database) [message #257636 is a reply to message #257559] Wed, 08 August 2007 19:02 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello All,

1) the database configuration file: pfile x spfile
I understand that:
the naming format of these files for "xxx" instance is:
pfile ==> INITxxx.ORA
spfile ==> SPFILExxx.ORA

if 2 dbconfig file (pfile/spfile) exist... spfile has privilege over pfile... so if I want pfile to be used I have to hide spfile, for instance changing its name.

2) Regarding OEM DBConsole creation:
I tried to apply the recommendations:
SQL> ALTER USER SYSMAN ACCOUNT UNLOCK;
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK;
... but no success.

I decide to goggle for a while.
I find this tip:
http://www.idevelopment.info/data/Oracle/DBA_tips/Enterprise_Manager/OEM_3.shtml
then I understood the basics:
1) if I have "sysman" I have to drop this user;
2) I have to online tablespace SYSAUX;
If I don't have SYSAUX online... I will not be able to create the dbconsole repository. Reasonable.
Having these 2 requirements above... I apply the following commands:

Quote:
C:\> emca -repos create (or recreate... and this takes some time)
C:\> emca -config dbcontrol db



That all.
Attached you will find my "cmd" screen.

To drop the user SYSMAN:
Quote:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';

BEGIN
FOR r1 IN c1
LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
SQL> COMMIT;



Well... I went to services and I see OracleDBConsoleCM3 running.
Then I go to {ORACLE_HOME}\Install\portlist.ini and see in which tcp port this server is running: 5501
Then.. I open a browser... (I am at the server)... and type:
http://127.0.0.1:5501/em

and... happiness!!!
I logon as SYS/SYSDBA... and voilá...!!!!

Next step... start the DB using PFILE edited by me... (???)
I will post the result here! (It looks like a soup opera)

THANK YOU.


mson77
PS: In Oracle Standard Edition... do I have DBConsole feature?
icon14.gif  Re: change SID (label of a database) [message #257640 is a reply to message #257636] Wed, 08 August 2007 19:33 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
KEWL MAN Wink
Re: change SID (label of a database) [message #257912 is a reply to message #257640] Thu, 09 August 2007 10:24 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Today's chapter is: PFILE and SPFILE (continued) :))))

I as described... I created my PFILE as INITCM3.ORA from SPFILECM3.ORA.
Renamed SPFILECM3.ORA as SPFILECM3.OR_ (to hide it from oracle startup).

I edited INITCM3.ORA (initially states SPFILECM3.ORA configuration but in PFILE format)... removing the following lines:
Quote:
*.db_cache_size
*.java_pool_size
*.large_pool_size
*.shared_pool_size



and inserting sga_target with 500MBytes:
Quote:
*.sga_target=500m


Now a question: What about "pga_aggregate_target"? Is there an ideal value for it? How can I see... after db startup... the ideal pga value? Should I have an application running for a while to determine this pga value?

About SGA.. I can check sga parameter with:
Quote:
SQL> show sga;

Total System Global Area 524288000 bytes
Fixed Size 1249944 bytes
Variable Size 146804072 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes



A conclusion from this test is: When I use SPFILE mode... the system changes are updated in SPFILE. And next time I startup my database... I will face with the same db configuration.
But... if I use PFILE mode... oracle will not update PFILE (INITCM3.ORA) and I will restart database with original configuration as stated in PFILE.

I could check if my db is using sga issuing: (no value means pfile mode)

Quote:
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> select value from v$parameter where name='spfile';

VALUE
--------------------------------------------------------------------------------


SQL>



Well... now... after these tests... rises one more doubt:
How can I active "flash_recovery"? If I just insert the following lines...is it enough?
Quote:
*.db_recovery_file_dest='c:\cm3\flashrecovery'
*.db_recovery_file_dest_size=2g


Thanks...

mson77
Re: change SID (label of a database) [message #257917 is a reply to message #257912] Thu, 09 August 2007 10:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

What about "pga_aggregate_target"?
Yes,
Default value 10 MB or 20% of the size of the SGA, whichever is greater 




I could check if my db is using sga issuing: (no value means pfile mode)
Yes, it means oracle server not using SPFILE for startup.

How can I active "flash_recovery"? If I just insert the following lines...is it enough?
Yes

Regards
Taj
Re: change SID (label of a database) [message #257927 is a reply to message #257917] Thu, 09 August 2007 11:11 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello Mohammed,

I understood that when I insert:
*.sga_target=500m
oracle will use this 500m to manage allocation of the sga sub-components items value. Correct me please!

Regarding pga... I see in db creation using dbca (please see attached image) that oracle allocates for pga by 33% of sga amount. Will this pga setting change on fly... managed by oracle´s automatic memory management mechanism?

How pga affects oracle performance? Is pga value really important?
Regards,

mson77
Re: change SID (label of a database) [message #257941 is a reply to message #257927] Thu, 09 August 2007 11:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
oracle will use this 500m to manage allocation of the sga sub-components items value.


Yes

Quote:
I see in db creation using dbca (please see attached image) that oracle allocates for pga by 33% of sga amount. Will this pga setting change on fly... managed by oracle´s automatic memory management mechanism?


Depends On the type of Work.

Quote:
How pga affects oracle performance? Is pga value really important?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams157.htm
Re: change SID (label of a database) [message #258040 is a reply to message #257941] Thu, 09 August 2007 23:01 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

I am now using PFILE to startup my CM3 database.

May I use the commands below to active "flash_recovery_area"?
Quote:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='c:\cm3\myflashrecoveryarea';
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;


If "YES"... where these settings are stored, considering that "ALTER SYSTEM SET" is used to change SPFILE.

If "NO"... then the only way to accomplish (enable flash_recovery_area) is manually editing PFILE... and inserting the lines as below?
Quote:
*.db_recovery_file_dest='c:\cm3\myflashrecoveryarea'
*.db_recovery_file_dest_size=2g



Thanks,

mson77
Re: change SID (label of a database) [message #258217 is a reply to message #258040] Fri, 10 August 2007 10:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='c:\cm3\myflashrecoveryarea';
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;


As these are dynamic parameters it will reflect changes directly to PFILE no manual entries needed for these no shutdown required of the INSTANCE.


considering that "ALTER SYSTEM SET" is used to change SPFILE.


Dynamic parameters can take effect on PFILE as well like SPFILE but for STATIC parameters (startup/shutdown ) is necessary.

[Updated on: Fri, 10 August 2007 10:26]

Report message to a moderator

Re: change SID (label of a database) [message #258224 is a reply to message #258217] Fri, 10 August 2007 10:42 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hi DreamzZ,

Thank you for you attention.
Let me understand what you said:

"Dynamic parameters also affect PFILE and no manual entries in this pfile is need... but full shutdown/startup of this db is required to have this parameters to take effect."

Well... I did this. As my understanding... and the conclusion was:

1) I have a db running in pfile mode;
2) I can check via sqlplus and assure that is in pfile mode;
3) I renamed SPFILECM3.ORA file to SPFILECM3.OR_ file;

4) I issued via sqlplus commands to enable flash_recovery_area;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='c:\cm3\myflashrecoveryarea';

5) Went to InitCM3.ORA file... no change occurred in date/size... neither into its content. No lines as :
*.db_recovery_file_dest='c:\cm3\myflashrecoveryarea'
*.db_recovery_file_dest_size=2g

has been included (updated). (these lines were what I was expecting)
Where am I failing?
Regards,


mson77
Re: change SID (label of a database) [message #258227 is a reply to message #258224] Fri, 10 August 2007 10:50 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
"Dynamic parameters also affect PFILE and no manual entries in this pfile is need... but full shutdown/startup of this db is required to have this parameters to take effect."


No.
\
Shutdown your database and check these entries in PFILE, although these are effective in running instance.
Re: change SID (label of a database) [message #258235 is a reply to message #258227] Fri, 10 August 2007 11:35 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hi DreamzZ,

Beginning again... I did the test again... slowly... to not make any new error...
1) Applied the commands:
Quote:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
Sistema alterado.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='c:\cm3\flashrecovery';
Sistema alterado.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;
Sistema alterado.

SQL> commit;
Commit concluİdo.


I went to services... and restart the CM3 service.
Follows sqlplus command after restart:
Quote:
SQL> select decode(value,NULL,'PFILE','SPFILE') "Using" from v_$parameter where name='spfile';
Using
------
PFILE

SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0
6 linhas selecionadas.

SQL> select * from v$recovery_file_dest;
NAME
-------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------

0 0 0 0


Attached the pfile => INITCM3.ORA with no entries as expected.

Should I do it using SPFILE? Or by editing manually PFILE?
Regards,


mson77
  • Attachment: INITCM3.ORA
    (Size: 0.74KB, Downloaded 198 times)
Re: change SID (label of a database) [message #258244 is a reply to message #258235] Fri, 10 August 2007 12:14 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Should I do it using SPFILE? Or by editing manually PFILE?


SPFILE would be better than PFILE,because in some cases you need to restart the instance if ypour instance running by pFILE then you will manually change the PFILE but in SPFILE you just type

alter system set ......... scope=spfile;

shutdown
and
restart.
Re: change SID (label of a database) [message #258480 is a reply to message #258244] Sun, 12 August 2007 10:59 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello ALL,

Just to make a conclusion: "how I active flash_recovery_area".

First: Using PFILE I tried to active flash_recovery_area using the commands:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='c:\cm3\flashrecovery';
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 1440;
==> no success.

Second way: Using SPFILE and issuing the same commands above.
==> success

Third way: Using PFILE... and manually editing PFILE and inserting the following lines inside PFILE.
*.db_recovery_file_dest='c:\cm3\myflashrecoveryarea'
*.db_recovery_file_dest_size=2g
==> success
Regards,

mson77
Re: change SID (label of a database) [message #258499 is a reply to message #258480] Sun, 12 August 2007 16:22 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Practice makes the man perfect Wink
Previous Topic: strange problem in running reports
Next Topic: Btree index
Goto Forum:
  


Current Time: Sun Dec 11 02:24:48 CST 2016

Total time taken to generate the page: 0.05322 seconds