Shulou(Shulou.com)05/31 Report--

This article mainly shows you "oracle 11g how to modify database names and instance names", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "oracle 11g how to modify database names and instance names" this article.

The first stage: change sid

1. Log in to the database to view the previous sid. There are three steps in total.

[javascript] view plain copy print?

[oracle@localhost ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 16:51:35 2011

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

Connected to an idle instance

SQL > startup

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 360711788 bytes

Database Buffers 171966464 bytes

Redo Buffers 5832704 bytes

Database mounted.

Database opened.

SQL > select instance from v$thread

INSTANCE

Orcl

2. Close the database

[sql] view plain copy print?

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

3. Edit the / etc/oratab file and replace all orcl with test. In most cases, there is only one item. -these steps don't need to be taken care of.

[sql] view plain copy print?

[oracle@localhost ~] $vim / etc/oratab

4. Change the .bash _ profile file of oracle users, and change orcl to test

[sql] view plain copy print?

[oracle@localhost ~] $vim .bash _ profile

5. Make the modified .bash_profile file effective.

[sql] view plain copy print?

[oracle@localhost] $. .bash _ profile

7. View system environment variables

[sql] view plain copy print?

[oracle@localhost ~] $env | grep ORACLE

ORACLE_UNQNAME=test

ORACLE_SID=test

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

The effect has been generated.

8. Go to $ORACLE_HOME/dbs to check the directory, see which ones have orcl, and change it to test.

[sql] view plain copy print?

[oracle@localhost ~] $cd $ORACLE_HOME/dbs

[oracle@localhost dbs] $ll

Total 24

-rw-rw---- 1 oracle oinstall 1544 10-01 16:55 hc_orcl.dat

-rw-r--r-- 1 oracle oinstall 2851 2009-05-15 init.ora

-rw-r- 1 oracle oinstall 24 09-28 20:57 lkORCL

-rw-r- 1 oracle oinstall 1536 09-29 09:42 orapworcl

Drwx- 2 oracle oinstall 4096 09-28 20:55 peshm_orcl_0

-rw-r- 1 oracle oinstall 2560 10-01 16:53 spfileorcl.ora

9. Change the file name orcr > > test,ORCL > > test. The command is as follows:

[sql] view plain copy print?

[oracle@localhost dbs] $mv hc_orcl.dat hc_test.dat

[oracle@localhost dbs] $mv orapworcl orapwtest

[oracle@localhost dbs] $mv lkORCL lkTEST

[oracle@localhost dbs] $mv peshm_orcl_0/ peshm_test_0/

[oracle@localhost dbs] $mv spfileorcl.ora spfiletest.ora

10. Re-generate the password file and view the

[sql] view plain copy print?

[oracle@localhost dbs] $orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y

[oracle@localhost dbs] $ls-lrt orap*

-rw-r- 1 oracle oinstall 2048 10-01 17:02 orapwtest

11. Log in to the database and check the instance name. The result shows that sid has changed from orcl to test.

[sql] view plain copy print?

[oracle@localhost dbs] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:03:25 2011

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

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 327157356 bytes

Database Buffers 205520896 bytes

Redo Buffers 5832704 bytes

Database mounted.

Database opened.

SQL > select instance from v$thread

2

INSTANCE

Test

The second part: do not need to log out, then start the second part, change the database name dbname

2.1 backup control files

[sql] view plain copy print?

SQL > alter database backup controlfile to trace resetlogs

Database altered.

2.2 close and exit the database

[sql] view plain copy print?

SQL >

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.3 the backup directory for the control files of orcale 11.2g is

/ u01/app/oracle/diag/rdbms/ORCL/test/trace # # in the alarm log, note that rdbms should be followed by ORCL, because the name of the database has not changed.

[sql] view plain copy print?

[oracle@localhost trace] $ls-lrt

Total 80

-rw-r- 1 oracle oinstall 256 10-01 17:03 test_ora_5286.trm

-rw-r- 1 oracle oinstall 14393 10-01 17:03 test_ora_5286.trc

-rw-r- 1 oracle oinstall 68 10-01 17:03 test_mman_5334.trm

-rw-r- 1 oracle oinstall 852 10-01 17:03 test_mman_5334.trc

-rw-r- 1 oracle oinstall 61 10-01 17:03 test_cjq0_5437.trm

-rw-r- 1 oracle oinstall 993 10-01 17:03 test_cjq0_5437.trc

-rw-r- 1 oracle oinstall 82 10-01 17:04 test_dbrm_5328.trm

-rw-r- 1 oracle oinstall 1172 10-01 17:04 test_dbrm_5328.trc

-rw-r- 1 oracle oinstall 90 10-01 17:05 test_vktm_5320.trm

-rw-r- 1 oracle oinstall 1233 10-01 17:05 test_vktm_5320.trc

-rw-r- 1 oracle oinstall 72 10-01 17:05 test_vkrm_5439.trm

-rw-r- 1 oracle oinstall 996 10-01 17:05 test_vkrm_5439.trc

-rw-r- 1 oracle oinstall 17410-01 17:05 test_ora_5419.trm

-rw-r- 1 oracle oinstall 5894 10-01 17:05 test_ora_5419.trc

-rw-r- 1 oracle oinstall 5969 10-01 17:05 alert_test.log

[oracle@localhost trace] $vim alert_test.log

[sql] view plain copy print?

A backup of contolfile can be found in alter_test.log. The latest one is trc,sid_ora_nnnn.trc.

There is a line in alter_test.log that tells you which is to control backup files.

[sql] view plain copy print?

Backup controlfile written to trace file / u01/app/oracle/diag/rdbms/orcl/test/trace/test_ora_5419.trc

2.5 make a copy

[sql] view plain copy print?

[oracle@localhost trace] $cp test_ora_5419.trc test.sql

Edit test.sql, that is, a copy of test_ora_5419.trc.

1) find the STARTUP NOMOUNT statement and delete all the lines above this line

2) find all lines that start with-- and delete them

3) find all orcl and change it to test, and change all ORCL to test # #. The purpose of this step is to comply with the rules. In fact, you can control the directory of files and data files at will.

4) find CREATE CONTROLFILE REUSE DATABASE... Statement to change the REUSE to SET

5) find the RECOVER DATABASE USING BACKUP CONTROLFILE statement and comment it out with a double horizontal line (- -)

The results are as follows:

[sql] view plain copy print?

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "test" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1'/ u01 SIZE SIZE BLOCKSIZE 512

GROUP 2'/ u01 SIZE SIZE BLOCKSIZE 512

GROUP 3'/ u01 SIZE SIZE BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/ u01 apprenticeship oradataUniple TestUniverse system01.dbf'

'/ u01 apprenticeship oradataUniplicatesysaux01.dbf'

'/ u01 apprenticeship oradataUniplicates01.dbf'

'/ u01hash appUnitionoracleandoradataUniplicateUsers01.dbf'

'/ u01 apprenticeship oradataUniple testUniverse example01.dbf'

CHARACTER SET ZHS16GBK

-- RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS

ALTER TABLESPACE TEMP ADD TEMPFILE'/ u01qqapp.oracle.oradataUniplicatetemp01.dbf'.

SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

2.7 generate profile

[sql] view plain copy print?

[oracle@localhost trace] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:12:48 2011

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

Connected to an idle instance.

SQL > create pfile from spfile

File created.

SQL > exit

Disconnected

2.8Directory changes, which are different from oracle 10g, should be noted.

[sql] view plain copy print?

[oracle@localhost ~] $cd / u01/app/oracle/

[oracle@localhost oracle] $ls

Admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle] $cd flash_recovery_area/

[oracle@localhost flash_recovery_area] $ls

Orcl ORCL

[oracle@localhost flash_recovery_area] $mv orcl/ test/

[oracle@localhost flash_recovery_area] $mv ORCL/ test/

[oracle@localhost flash_recovery_area] $cd..

[oracle@localhost oracle] $ls

Admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle] $cd oradata/

[oracle@localhost oradata] $ls

Orcl

[oracle@localhost oradata] $mv orcl/ test/

[oracle@localhost oradata] $ls

Test

[oracle@localhost oradata] $cd..

[oracle@localhost oracle] $ls

Admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle] $cd diag/

[oracle@localhost diag] $ls

Rdbms tnslsnr

[oracle@localhost diag] $cd rdbms/

[oracle@localhost rdbms] $ls

Orcl

[oracle@localhost rdbms] $mv orcl/ test/

[oracle@localhost rdbms] $ls

Test

[oracle@localhost rdbms] $cd test

[oracle@localhost test] $ls

I_1.mif test orcl

[oracle@localhost test] $cd..

[oracle@localhost rdbms] $cd..

[oracle@localhost diag] $ls

Rdbms tnslsnr

[oracle@localhost diag] $cd..

[oracle@localhost oracle] $cd admin/

[oracle@localhost admin] $ls

Orcl

[oracle@localhost admin] $cd orcl/

[oracle@localhost orcl] $ls

Adump dpdump pfile

[oracle@localhost orcl] $cd..

[oracle@localhost admin] $mv orcl/ test/

[oracle@localhost admin] $ls

Test

[oracle@localhost admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:18:02 2011

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

Connected to an idle instance.

SQL >

2.9 deleted control files.

[sql] view plain copy print?

[oracle@localhost oradata] $cd test/

[oracle@localhost test] $ls

Control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

Example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@localhost test] $mv control01.ctl control01.ctl.aaa

[oracle@localhost test] $ls

Control01.ctl.aaa redo01.log redo03.log system01.dbf undotbs01.dbf

Example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

[oracle@localhost test] $cd..

[oracle@localhost oradata] $ls

Test

[oracle@localhost oradata] $cd..

Bash: cd..: command not found

[oracle@localhost oradata] $cd..

[oracle@localhost oracle] $ls

Admin cfgtoollogs checkpoints diag flash_recovery_area oradata product

[oracle@localhost oracle] $cd flash_recovery_area/

[oracle@localhost flash_recovery_area] $ls

Test test

[oracle@localhost flash_recovery_area] $cd test/

[oracle@localhost test] $ls

Control02.ctl

[oracle@localhost test] $mv control02.ctl control02.ctl.aaa

2.10 Log in to oracle to generate spfile file

[sql] view plain copy print?

[oracle@localhost test] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 1 17:23:34 2011

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

Connected to an idle instance.

SQL > create spfile from pfile='?/dbs/inittest.ora'

File created.

2.11 call the modified test.sql in the previous step to generate link control files, etc.

[sql] view plain copy print?

SQL > @ / u01/app/oracle/diag/rdbms/test/test/trace/test.sql

ORACLE instance started.

Total System Global Area 539848704 bytes

Fixed Size 1337748 bytes

Variable Size 327157356 bytes

Database Buffers 205520896 bytes

Redo Buffers 5832704 bytes

Control file created.

Database altered.

Tablespace altered.

2.12 View results

[sql] view plain copy print?

SQL > select open_mode from v$database

OPEN_MODE

-

READ WRITE

SQL > show parameter name

NAME TYPE VALUE

-

Db_file_name_convert string

Db_name string test

Db_unique_name string test

Global_names boolean FALSE

Instance_name string test

Lock_name_space string

Log_file_name_convert string

Service_names string test.localdomain

SQL > select name from v$database

NAME

-

Test

SQL >

Indicates that the change was successful.

The above is all the contents of the article "how oracle 11g modifies database names and instance names". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.