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

This article mainly introduces how to modify DB_NAME and SID in the database, the article is very detailed, has a certain reference value, interested friends must read it!

After the database is recovered on a different machine, you want to modify its db_name and SID. Here are two ways to modify it.

Method 1: reconstruct the control file mode

-- Log archiving

Alter system archive log current

-- backup control files

Alter database backup controlfile to trace resetlogs

-- controls the path of file backup generation

Show parameter user_dump_dest

-- controls the file path

Show parameter control_files

Create pfile from spfile

Shutdown immediate

Cd / u01/diag/rdbms/prod/prod/trace

Cp prod_ora_3163.trc create_cf.sql

Vi create_cf.sql

-- Editing files

-- (1) the beginning of "-" can be deleted.

-- (2) deletion above "STARTUP NOMOUNT"

-- (3) the REUSE of the sentence "PROD" RESETLOGS FORCE LOGGING ARCHIVELOG of CREATE CONTROLFILE REUSE DATABASE is changed to SET.

Change PROD to the name you want, such as test (if you only modify dbname or something, the following paths such as data files do not need to be modified)

-- (4) the sentence "RECOVER DATABASE USING BACKUP CONTROLFILE" can be used-- comment it out. There is no recover here.

-- backup the original control file

Mv / u01/oradata/prod/control01.ctl / u01/oradata/prod/control01.ct.bak

Mv / u01/oradata/prod/control02.ctl / u01/oradata/prod/control02.ctl.bak

Cp / u01/oracle/db_1/dbs/initprod.ora / u01/oracle/db_1/dbs/inittest.ora

Vi / u01/oracle/db_1/dbs/initprod.ora

-- both db_name and db_unique_name have been modified.

* .db_name='test'

* .db_unique_name='test'

Export ORACLE_SID=test

Startup nomout pfile=' / u01qqoracle.ora'

Create spfile from pfile

Shutdown immediate

Orapwd file=$ORACLE_HOME/dbs/orapwtest password=oracle entries=5

-execute the rebuild control file

Sqlplus / as sysdba

@ create_cf.sql

Note: this method ends with alter database open resetlogs

Method 2: change the database name with nid, a tool that comes with oracle.

Shutdown immediate

-- nid can only be done in mount state.

Startup mount

Exit

Nid modifies dbname= 's new name to know the sys password

Nid target=sys/password dbname=test

Change database ID and database name PROD to TEST? (y / [N]) = > y

Orapwd file=$ORACLE_HOME/dbs/orapwtest password=oracle entries=5

Cp / u01/oracle/db_1/dbs/initprod.ora / u01/oracle/db_1/dbs/inittest.ora

Vi / u01/oracle/db_1/dbs/initprod.ora

-- both db_name and db_unique_name have been modified.

* .db_name='test'

* .db_unique_name='test'

Export ORACLE_SID=test

Startup nomout pfile=' / u01qqoracle.ora'

Create spfile from pfile

Shutdown immediate

Startup mount

Alter database open resetlogs

The above is all the contents of the article "how to modify DB_NAME and SID in the database". Thank you for reading! Hope to share the content to help you, more related 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.