=======================修改数据库dbid======================
1、查看目前数据库dbid以及dbname
[root@rac01 ~]# su - oracle
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:23:24 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL> select dbid from v$database;
DBID
----------
2、将群集参数修改为false,否则修改不成功
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、停止群集数据库,并在一个节点启动到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:34:34 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
4、修改数据库dbid
[oracle@rac01 ~]$ nid target=sys/oracle@orcl
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:37:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1466421350)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database ID of database ORCL? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1466421350 to 1470887377
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - dbid changed
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - dbid changed
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - dbid changed
Datafile +DATA/orcl/datafile/users.259.93864038 - dbid changed
Datafile +DATA/orcl/datafile/example.267.93864064 - dbid changed
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - dbid changed
Datafile +DATA/orcl/tempfile/temp.266.93864063 - dbid changed
Control File +DATA/orcl/controlfile/current.261.938640615 - dbid changed
Control File +DATA/orcl/controlfile/current.260.938640615 - dbid changed
Instance shut down
Database ID for database ORCL changed to 1470887377.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
5、重建密码文件
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:41:10 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
6、以resetlogs模式打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
ORCL READ WRITE 1470884038 14-MAR-17
7、将数据库集群参数修改为true,重启数据库
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac01 dbs]$ srvctl stop database -d orcl
[oracle@rac01 dbs]$ srvctl start database -d orcl
[oracle@rac01 dbs]$ su - grid
[grid@rac01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.LISTENER.lsnr
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.VOTE.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.asm
ONLINE ONLINE rac01 Started
ONLINE ONLINE rac02 Started
ora.gsd
OFFLINE OFFLINE rac01
OFFLINE OFFLINE rac02
ora.net1.network
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.ons
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.registry.acfs
ONLINE ONLINE rac01
ONLINE ONLINE rac02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
ONLINE ONLINE rac01
ora.cvu
ONLINE ONLINE rac02
ora.oc4j
ONLINE ONLINE rac02
ora.orcl.db
ONLINE ONLINE rac01 Open
ONLINE ONLINE rac02 Open
ora.rac01.vip
ONLINE ONLINE rac01
ora.rac02.vip
ONLINE ONLINE rac02
ora.scan1.vip
ONLINE ONLINE rac01
==========================修改数据库dbname=======================
1、查看数据库dbid及数据库dbname
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:49:36 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
2、修改数据库集群参数为false,并创建pfile文件
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、关闭数据库并在一个节点启动到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ cd /tmp/
[oracle@rac01 tmp]$ ls -l pfil*
-rw-r--r-- 1 oracle oinstall 1374 May 4 17:51 pfile.ora
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
SQL> quit
4、修改数据库dbname为db11g
[oracle@rac01 dbs]$ nid TARGET=SYS/oracle@orcl DBNAME=db11g SETNAME=Y
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:57:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1470887377)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database name of database ORCL to DB11G? (Y/[N]) => y
Proceeding with operation
Changing database name from ORCL to DB11G
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - wrote new name
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - wrote new name
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - wrote new name
Datafile +DATA/orcl/datafile/users.259.93864038 - wrote new name
Datafile +DATA/orcl/datafile/example.267.93864064 - wrote new name
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - wrote new name
Datafile +DATA/orcl/tempfile/temp.266.93864063 - wrote new name
Control File +DATA/orcl/controlfile/current.261.938640615 - wrote new name
Control File +DATA/orcl/controlfile/current.260.938640615 - wrote new name
Instance shut down
Database name changed to DB11G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
5、尝试startup无法启动,因为在参数文件中记录的dbname仍然是orcl
[oracle@rac01 dbs]$ cat initorcl1.ora
SPFILE='+DATA/orcl/spfileorcl.ora'
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
ORA-01103: database name 'DB11G' in control file is not 'ORCL'
6、修改pfile中的dbname参数,修改为新的dbname
[oracle@rac01 dbs]$ vi /tmp/pfile.ora
change *.db_name='orcl'
to *.db_name='db11g'
7、使用pfile启动
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:03:20 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
Database altered.
8、创建spfile,需指定路径,因为spfile在ASM磁盘中,否则会在本地生成一个spfile
SQL> create SPFILE='+DATA/orcl/spfileorcl.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
9、重启数据库
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string db11g
db_unique_name string db11g
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string db11g
10、修改群集参数为true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
1470887377 DB11G READ WRITE 1470884038 14-MAR-17
11、重建密码文件
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10 force=y
[oracle@rac01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-MAY-2017 18:24:57
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 04-MAY-2017 17:17:18
Uptime 0 days 1 hr. 7 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "db11g" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
12、修改tnsname.ora文件的service_name为db11g
[oracle@rac01 dbs]$ cd ../network/admin/[oracle@rac01 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
)
)
13、尝试连接
[oracle@rac01 admin]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:25:53 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
连接成功。
14、将新的数据库名称加入群集进行管理,删除旧的数据库名称
[oracle@rac01 ~]$ srvctl add database -d db11g -o $ORACLE_HOME
[oracle@rac01 ~]$ exit
logout
[root@rac01 ~]# su - grid
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type OFFLINE OFFLINE
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora.orcl.db ora....se.type ONLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl1 -n rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl2 -n rac02
[oracle@rac01 ~]$ srvctl remove database -d orcl
Remove the database orcl? (y/[n]) y
[oracle@rac01 ~]$ srvctl start database -d db11g
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type ONLINE ONLINE rac01
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01