oracle 9i修改DBID_和DBNAME总结
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
9I修改DBID 和DBNAME总结
原创
作者:XIE3000
9I修改DBID 和DBNAME总结:
(一)说明:
环境:9I(9201)+WINDOWS2000
使用工具:NID(装数据库时已经装上拉直接用就可以)
参考
http://download-
/docs/cd/B10501_01/server.920/a96652/ch14.ht m#1004918
(二)修改步骤
(1).进行数据库完全备份以防万一修改失败
(2).将数据库正常关闭然后MOUNT
SQL> connect / as sysdba
已连接。
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
(3).运行NID指定一个具有SYSDBA权限的ORACLE用户
C:\> NID TARGET=SYS/SYSYTMF DBNAME=O9201NEW
注:改变DBID,新DBID值由工具自动生成不用指定,
O9201NEW是指定的新数据库NAME
也可以用NID TARGET=SYS/SYSYTMF@service_nam e DBNAME=O9201NEW
如果没有使用DBNAME参数则操作只改变DBID
也可以用操作系统验证如:NID TARTET=/
(4).修改提示成功后正常关闭数据库
SQL> shutdown immediate;
ORA-01109: ??????
已经卸载数据库。
ORACLE 例程已经关闭。
(5).创建密码文件(要求还用原来的密码文件名字PWDO9201.ORA如果生成
PWDO9201NEW.ORA则提示错误:原来文件找不到)
将原来的密码文件PWDO9201。
ORA随便改个名字然后执行:
c:\>orapwd file=f:\oracle\ora92\database\pwdo9201.ora password=sysyt m f enters=10
确认是否成功生成密码文件
(6),修改数据初始化参数文件中的DB_NAME 为新的的DB_NAME(O9201NEW)
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter system set db_name=o9201new scope=spfile;
系统已更改。
(如果使用PFILE就手工修改相应值)
(7)
SQL> shutdown immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。
(8)startup
SQL> startup;
ORACLE 例程已经启动。
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
数据库装载完毕。
ORA-01589: 要打开数据库则必须使用RESETLOGS 或NORESETLOGS 选项
(9)用RESETLOGS模式OPEN数据库(如果没有修改DBNAME就不必用RESETLOGS选项)SQL> alter database open resetlogs;
数据库已更改。
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
3677577861 O9201NEW
已选择 1 行。
xie3000 上传了这个图片:
Oracle by Example m odule(OTN)
Changing Your Database Name and Database Identifier
Purpose
This m odule describes how you can change the nam e of your database and your database identifier.
Topics
This m odule discusses the following:
Overview
Oracle9i Release 2: Using the DBNEWID Utility
Changing Your Database Identifier (DBID) Only
Changing Your Database Name (DBNAME) Only
Changing Both Your Database Name and Database Identifier
Oracle9i Release 1 and Earlier: Changing Your Database Name
Viewing Screenshots
Move your mouse over this icon to show all screenshots. You can also m ove your mouse over each individual icon to see only the screenshot associated with it.
Overview
Back to List of Topics
In this lesson you will learn how to change the nam e of your database.
If you are using Oracle9i Database Release 2 or a later release of Oracle Database, you can use the DBNEWID database utility to change the database nam e (DBNAME) and the internal database identifier (DBID) for an operational database.
If you are using a release prior to Oracle9i Database Release 2, you can change the database nam e (DBNAME) by re-creating the control file.
Refer to the appropriate section below depending on the release of Oracle you are using:
Using the DBNEWID Utility to Change the Database Name
Changing the Database Name
Note: Oracle Corporation recommends that your database nam e and ORACLE_SID
be set to the sam e value in a single instance environment.
Oracle9i Release 2: Using the DBNEWID Utility
Back to List of Topics
You can use the DBNEWID database utility to change the internal database identifier (DBID) and the database nam e (DBNAME) for an operational database. The DBNEWID database utility is available with Oracle9i Database Release 2.
Prior to the introduction of the DBNEWID utility, you could m anually create a copy of a database and give it a new database nam e (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID). The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the sam e RMAN repository. The DBNEWID utility solves this problem by allowing you to change any of the following:
Only the DBID of a database
Only the DBNAME of a database
Both the DBNAME and DBID of a database
When the DBID of a database is changed, all previous backups a nd archived logs of the database becom e unusable. After you change the DBID, you must open the database with the RESETLOGS option. This re-creates the online redo log files and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You m ust change the DB_NAME initialization parameter after a database nam e change to reflect the new nam e. Also, you m ay have to re-create the Oracle password file. If you restore an old backup of the control file (before the nam e change), then you should use the initialization parameter file and password file from before the database nam e change.
Changing Your Database Identifier (DBID) Only
Back to List of Topics
You can change the DBID by com pleting the following steps:
1. Make a whole database backup.
2. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3. Issue the following query to determine the current DBID:
SELECT dbid, nameFROM v$database;
4. Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE
5. Start the instance and m ount the database, specifying the parameter file if you are not using a server parameter file or the text initialization parameter file is not in the default location:
STARTUP MOUNT
6. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. The DBNEWID utility performs validations of the headers of the data files and control files before attem pting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation unless you specify a log file, changes the DBID for each data file (including offline normal and read-only data files), and then exits. The database is left m ounted but is not yet usable.
nid TARGET=SYS/secure@<service_name>
7. After DBNEWID successfully changes the DBID, shut down the instance: SHUTDOWN IMMEDIATE
8. Create a new password file using the ORAPWD utility:
orapwd file=orapw<SID> password=<your password> entries=
9. Start the instance and m ount the database:
STARTUP MOUNT;
10. Open the database with the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
11. Verify the change to the DBID by issuing the following query:
SELECT dbid, nameFROM v$database;
12. Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
Changing Your Database Name (DBNAME) Only
Back to List of Topics
You can change the DBNAME by completing the following steps:
1. Make a whole database backup.
2. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3. Issue the following query to determine the current database nam e:
SELECT nam eFROM v$database;
4. Shut down the instanc e using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE
5. Start the instance and m ount the database, specifying the parameter file if you are not using a server parameter file or the text initialization parameter file is not in the default location:
STARTUP MOUNT
6. Invoke the DBNEWID utility on the command line, specifying a valid user with the
SYSDBA privilege. You m ust specify the DBNAME parameter and supply your new database nam e. You m ust also specify the YES value for the SETNAME parameter to indicate that only the DBNAME is to be changed. DBNEWID performs validations of the headers of the control files, but not the data files, before attem pting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database nam e in the control files, and exits. After DBNEWID com pletes successfully, the database is left m ounted but is not yet usable.
nid TARGET=<username>/<password>@<servicename> DBNAME=<newname> SETNAME=YES
7. After DBNEWID successfully changes the database nam e, shut down the instance: SHUTDOWN IMMEDIATE
8. Change the DB_NAME initialization parameter to your new database nam e.
9. Create a new password file using the ORAPWD utility:
orapwd file=orapw<SID> password=<your password> entries=
10. Start the instance and open the database:
STARTUP
11. Verify the change to the database nam e by issuing the following query: SELECT nam eFROM v$database;
Changing Both Your Database Name and Database Identifie r
Back to List of Topics
You can change the DBNAME by completing the following steps:
1. Make a whole database backup.
2. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3. Issue the following query to determine the current database identifier and database nam e:
SELECT dbid, nameFROM v$database;
4. Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE
5. Start the instance and m ount the database, specifying the parameter file if you are not using a server parameter file or the text initialization parameter file is not in the default location:
STARTUP MOUNT
6. Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege. You m ust specify the DBNAME parameter and supply your new database nam e. DBNEWID performs validations of the headers of the control files, but not the data files, before attem pting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database nam e in the control files, and exits. After DBNEWID com pletes successfully, the database is left mounted but is not yet usable.
nid TARGET=<username>/<password>@<service nam e> DBNAME=<new name>
7. After DBNEWID successfully changes the database identifier and database nam e, shut down the instance:
SHUTDOWN IMMEDIATE
8. Change the DB_NAME initialization parameter to your new database nam e.
9. Create a new password file using the ORAPWD utility:
orapwd file=orapw<SID> password=<your password> entries=
10. Start the instance and m ount the database:
STARTUP MOUNT
11. Open the database with the RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
11. Verify the change to the database nam e and database identifier by issuing the following query:
SELECT nam e, dbidFROM v$database;
12. Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
Oracle9i Release 1 and Ea rlier: Changing the Database Name
Back to List of Topics
You can change the Database Name by com pleting the following steps:
1. Invoke SQL*Plus and connect as a user with SYSDBA privilege.
2. Issue the following query to determine the current database nam e:
SELECT nam eFROM v$database;
3. Issue the following command to force a checkpoint:
ALTER SYSTEM CHECKPOINT;
4. Issue the following command to create a trace file containing the statem ents to re-create the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
5. Locate the trace file created in step 4. The trace file will be in the location specified by the USER_DUMP_DEST initialization parameter. The file will be named ora_NNNN.trc. You can find the value of USER_DUMP_DEST by issuing the following command:
SHOW PARAMETER USER_DUMP_DEST
6. Change the value of the DB_NAME initialization parameter to your new database nam e.
7. Shut down the instance using the NORMAL, IMMEDIATE, or TRANSACTIONAL options:
SHUTDOWN IMMEDIATE
8. Copy the contents of the trace file beginning with the STARTUP NOMOUNT command to a new file. For the purposes of this example, assume you have named the new file ccf.sql.
9. Edit your ccf.sql file and m ake the following changes:
Remove all of the comment lines. Your script should begin with the CREATE
CONTROLFILE statem ent.
Change:
CREATE CONT ROLFILE REUSE DATABASE "olddbname" RESETLOGS
To:
CREATE CONT ROLFILE SET DATABASE "newdbname" RESETLOGS
Remove the RECOVER DATABASE USING BACKUP CO NT ROLFILE statem ent:
10. Rename your existing control files to provide a backup.
11. Invoke SQL*Plus and connect as a user with SYSDBA privileges.
12. Execute your ccf.sql script to re-create the control file. The script starts the instance in NOMOUNT m ode and re-creates the control file. The script opens the database.
@ccf
13. Change your global database nam e if required by issuing the following command.
ALTER DATABASE RENAME GLOBAL_NAME to <newdbname>.<domain>;
14. Verify your changes by issuing the following query to find the current value of DB_NAME:
SELECT nam e FROM v$database;
15. Make a whole consistent (full or com plete) database backup using Recovery Manager (RMAN) or operating system commands.
转贴8i 的
如何将Oracle数据库改名
在很多人看来Oracle数据库一旦建好后,数据库的名字就不能改变了,其实不然,本文将告诉你如何更改Oracle数据库的名字。
当然在开始这一系列动作之前,你必须确认已对你的数据库做了一个完整可用的备份,虽然我做过测试,不做任何备份完全可以成功,但是如果你也这样做,风险自负,可不要怪我没有提醒你噢!欢迎和我交流:gototop@。
1、打开Server Manager,用internal连接
$ svrmgrl
SVRMGR> connect internal
2、如果你的数据库跑在archived log mode下,你需要执行以下命令来强迫一次checkpoint 发生:
SVRMGR> alter system switch logfile;
3、为你的control file做一次备份:
SVRMGR> alter database backup controlfile to trace resetlogs;
4、用normal或immediate方式关闭数据库,记住千万不要用abort方式:SVRMGR> shutdown immediate
SVRMGR> exit
5、打开你最近一次的trace file(在"user_dump_dest"所定义的目录下),将从"CREATE CONTROLFILE"开始到分号结束的部分copy出来,新建成create_ctl.sql文件。
6、编辑此文件,将其中的
CREATE CONT ROLFILE REUSE DATABASE "old_db_name" RESETLOGS ...
改为:
CREATE CONT ROLFILE set DATABASE "new_db_name" RESETLOGS ...
即把"REUSE"改成"SET",把原来的DB_NAME改为新的DB_NAME。
7、备份你原来的control files,以防止被覆盖。
8、编辑你的初始化参数文件initSID.ora,将其中的db_name改为新的db_name。
9、执行如下操作:
$ svrmgrl
SVRMGR> connect internal
SVRMGR> @/data1/gototop/create_ctl.sql
此时如何系统有提示部分数据文件需要做恢复,则说明在第四步时没有正常关闭数据库。
此时你
将不得不使用当前logfile中的redo来恢复数据库:
SVRMGRL> recover database using backup controlfile until cancel;
10、下面用resetlogs方式打开数据库:
SVRMGR> alter database open resetlogs;
11、改变全局数据库名称:
SVRMGR> alter database rename global_name to <newdb_name>.<domain> SVRMGR> select * from v$database;
12、好了,到此为止你的数据库名字就改好了,现在我不说你都知道要做什么了,resetlogs 之后不要忘了对你的数据库执行新的全备份,因为你以前的备份都已经失效了。