| change SID (label of a database) [message #257219] |
Tue, 07 August 2007 15:48  |
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 #257235 is a reply to message #257233] |
Tue, 07 August 2007 17:37  |
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 |
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 |
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 |
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 |
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 #257636 is a reply to message #257559] |
Wed, 08 August 2007 19:02 |
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?
|
|
|
|
|
|
| Re: change SID (label of a database) [message #257912 is a reply to message #257640] |
Thu, 09 August 2007 10:24 |
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:
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 #257927 is a reply to message #257917] |
Thu, 09 August 2007 11:11 |
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 #258040 is a reply to message #257941] |
Thu, 09 August 2007 23:01 |
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 |
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 |
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 |
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 #258244 is a reply to message #258235] |
Fri, 10 August 2007 12:14 |
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 |
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;
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
Regards,
mson77
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
