How to modify the database dbname directly without deleting the library in ORACLE
This article mainly introduces how to modify the database dbname directly without deleting the database in ORACLE. It is very detailed and has certain reference value. Interested friends must finish reading it.
Environment: Red 6.4 ORACLE:11.2.0.4
Original library dbname:mai
New library dname:prod
To verify that the data is modified after changing the dbname, do the following:
Original library: mai
SQL > alter user hr identified by oracle123 account unlock
SQL > conn hr/oracle
SQL > create table abc (id int,name varchar (20))
SQL > insert into abc (1 'ok',2' sdad')
SQL > commit
Commit complete.
SQL > create pfile='initprod.ora' from spfile
SQL >! nid target=sys/oracle123 dbname=prod logfile=/tmp/change.log
SQL > shu immediate
ORA-03135: connection lost contact
At this point, you need to exit sqlplus and reassign ORACLE_SID.
[oracle@node1 ~] $export ORACLE_SID=prod
[oracle@node1 ~] $sqlplus / as sysdba
Start the database to mount
SQL > startup mount
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1157628808 bytes
Database Buffers 3103784960 bytes
Redo Buffers 12107776 bytes
ORA-01103: database name 'PROD' in control file is not' MAI'
SQL > alter system set db_name=prod scope=spfile; # # an error was reported because spfile was not used
Alter system set db_name=prod scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
Create spfile
SQL > create spfile from pfile
File created.
Close the library to make it use spfile
SQL > shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL > startup mount
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1157628808 bytes
Database Buffers 3103784960 bytes
Redo Buffers 12107776 bytes
ORA-01103: database name 'PROD' in control file is not' MAI'
Modify dbname
SQL > alter system set db_name=prod scope=spfile
System altered.
Restart the database
SQL > shu immediate
ORA-01507: database not mounted
New library: prod
ORACLE instance shut down.
SQL > startup mount
ORACLE instance started.
Open the database
SQL > alter database open resetlogs
Database altered.
SQL > show parameter name
NAME TYPE VALUE
-
Cell_offloadgroup_name string
Db_file_name_convert string
Db_name string PROD
Db_unique_name string PROD
Global_names boolean FALSE
Instance_name string prod
Lock_name_space string
Log_file_name_convert string
Processor_group_name string
Service_names string PROD
Verify that data is lost
SQL > select USERNAME,ACCOUNT_STATUS from dba_users
USERNAME ACCOUNT_STATUS
SYS OPEN
SYSTEM OPEN
HR OPEN
OUTLN EXPIRED & LOCKED
Connect hr users
SQL > conn hr/oracle
SQL > select * from abc
ID NAME
--
1 ok
2 sdad
The above is all the contents of the article "how to modify the database dbname directly without deleting the database in ORACLE". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
