메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

How to change the database name in 12c

명품관 2016.05.31 18:00 조회 수 : 46

How to change the database name in 12c

 

To change the database name is a common task for every Oracle Database Administrator, however with every version some things change and it is necessary to review the process that we have been using in order to confirm if it is still valid or not in a new version, in this case 12c. In this article I will show you that, I have done some test in my 12c environments and I will show you on which parts changing the database name changed. 

 

Pluggable Databases

Firstable, in 12c Oracle has introduced a new concept: Oracle Pluggable Databases (PDB)

Every PDB is an isolated database. Based on that, if this is a database then we should be able to rename it. How we can do it? that's what we will see in the next steps. 

 

For these examples, I am using a Container Database (CDB) with  2 Pluggable Databases (PDBs) as  you can see below:

 

SQL>select con_id, dbid, con_uid, guid, name, open_mode from v$containers

 

CON_ID DBID       CON_UID    GUID                             NAME
------ ---------- ---------- -------------------------------- ----------
1      2029615230 1          FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT
2      3232131500 3232131500 33E566D292696F62E0530401A8C0010D PDB$SEED
3      878113373  878113373  33E573FD368272A9E0530401A8C0AC1C PDB1
4      221610700  221610700  33E576C86F0F74FBE0530401A8C05574 PDB2

 

Now, I will proceed to change the name of PDB1. To do so, I have to be connected to the PDB that I want to change the name, in this case PDB1.

First I want to see where I am conntected to:

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

 

Before changing the PDB name, we have to put the PDB in restricted mode:

 

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open restricted;

Pluggable database altered.

 

NOTE: If you try to change the PDB name while the PDB is not in restricted mode you will receive the following error:

ORA-65045: pluggable database not in a restricted mode

 

Moving to PDB:

 

SQL> alter session set container=pdb1;

Session altered.

Confirming that in fact, I am connected to PDB1: 

SQL> show con_name;

CON_NAME
------------------------------
PDB1

 

And finally, let's change the PDB name:

 

SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdb1new;

Pluggable database altered.

 

After to change the PDB name, let's verify if any "ID" changed as well:

 

select con_id, dbid, con_uid, guid, name, open_mode,restricted from v$containers;

CON_ID DBID       CON_UID    GUID                             NAME       OPEN_MODE  RESTRICTED
------ ---------- ---------- -------------------------------- ---------- ---------- ---
1      2029615230 1          FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT   READ WRITE NO
2      3232131500 3232131500 33E566D292696F62E0530401A8C0010D PDB$SEED   READ ONLY  NO
3      878113373  878113373  33E573FD368272A9E0530401A8C0AC1C PDB1NEW    READ WRITE YES
4      221610700  221610700  33E576C86F0F74FBE0530401A8C05574 PDB2       MOUNTED

 

As you can see, CON_ID, DBID, CON_UID and GUID did not change. Only the PDB name changed, and after the rename the PDB, it will be open in restricted mode yet so we have to open it in normal mode.

 

SQL> alter pluggable database pdb1new close;

Pluggable database altered.

SQL> alter pluggable database pdb1new open;

Pluggable database altered.

Confirming:

SQL> select con_id, dbid, con_uid, guid, name, open_mode,restricted from v$containers;

CON_ID DBID       CON_UID    GUID                             NAME       OPEN_MODE  RES
------ ---------- ---------- -------------------------------- ---------- ---------- ---
1      2029615230 1          FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT   READ WRITE NO
2      3232131500 3232131500 33E566D292696F62E0530401A8C0010D PDB$SEED   READ ONLY  NO
3      878113373  878113373  33E573FD368272A9E0530401A8C0AC1C PDB1NEW    READ WRITE NO
4      221610700  221610700  33E576C86F0F74FBE0530401A8C05574 PDB2       MOUNTED

 

Container Database:

Now it's time to change the name for a Container Database where there are some pluggable databases. We will keep using the same environment that we have, 1 CDB and 2 PDBs.

In order to change the CDB name, the first step is to put the CDB in mount state. we cannot change the CDB name while the database is in read-write. 

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL>

 

NOTE: If we try to change the CDB name and the database is not in mount state we will receive the following error:

NID-00121: Database should not be open

 

Now let's take a look at some of the options that we have with NID tool:

 

[oracle@db12102 ~]$ nid -help

DBNEWID: Release 12.1.0.2.0 - Production on Sat May 28 06:51:36 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Keyword Description (Default)
----------------------------------------------------
TARGET  Username/Password            (NONE)
DBNAME  New database name            (NONE)
LOGFILE Output Log                   (NONE)
REVERT  Revert failed change         NO
SETNAME Set a new database name only NO
APPEND  Append to output log NO
HELP    Displays these messages NO

 

Now let's proceed to change it:

 

[oracle@db12102 ~]$ nid target=/ dbname=cdbnew

DBNEWID: Release 12.1.0.2.0 - Production on Sat May 28 06:55:33 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to database CDB (DBID=2029615230)

Connected to server version 12.1.0

Control Files in database:
/data/cdb/CDB/controlfile/o1_mf_cnlw9wn8_.ctl
/data/cdb/CDB/controlfile/o1_mf_cnlw9wpm_.ctl

Change database ID and database name CDB to CDBNEW? (Y/N) => y

Proceeding with operation
Changing database ID from 2029615230 to 4097604774
Changing database name from CDB to CDBNEW
Control File /data/cdb/CDB/controlfile/o1_mf_cnlw9wn8_.ctl - modified
Control File /data/cdb/CDB/controlfile/o1_mf_cnlw9wpm_.ctl - modified
Datafile /data/cdb/CDB/datafile/o1_mf_system_cnlw7bw3_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_sysaux_cnlw5xrj_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_undotbs1_cnlw934v_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_system_cnlwb479_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_users_cnlw922n_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_sysaux_cnlwb478_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E573FD368272A9E0530401A8C0AC1C/datafile/o1_mf_system_cnlwllgp_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E573FD368272A9E0530401A8C0AC1C/datafile/o1_mf_sysaux_cnlwllh0_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E573FD368272A9E0530401A8C0AC1C/datafile/o1_mf_users_cnlwlwgm_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E576C86F0F74FBE0530401A8C05574/datafile/o1_mf_system_cnlwn242_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E576C86F0F74FBE0530401A8C05574/datafile/o1_mf_sysaux_cnlwn249_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E576C86F0F74FBE0530401A8C05574/datafile/o1_mf_users_cnlwn24b_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/o1_mf_temp_cnlwb13f_.tm - dbid changed, wrote new name
Datafile /data/cdb/CDB/datafile/pdbseed_temp012016-05-28_06-22-34-AM.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E573FD368272A9E0530401A8C0AC1C/datafile/o1_mf_temp_cnlwllh1_.db - dbid changed, wrote new name
Datafile /data/cdb/CDB/33E576C86F0F74FBE0530401A8C05574/datafile/o1_mf_temp_cnlwn24b_.db - dbid changed, wrote new name
Control File /data/cdb/CDB/controlfile/o1_mf_cnlw9wn8_.ctl - dbid changed, wrote new name
Control File /data/cdb/CDB/controlfile/o1_mf_cnlw9wpm_.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to CDBNEW.
Modify parameter file and generate a new password file before restarting.
Database ID for database CDBNEW changed to 4097604774.
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 name and ID.
DBNEWID - Completed succesfully.

[oracle@db12102 ~]$

 

NOTE: Right after the DB name changed, we have to change also the parameter "db_name", otherwise when you try to mount the database you will receive the following error:

ORA-01103: database name 'CDBNEW' in control file is not 'CDB'

 

 

Based on that let's change the parameter (not dynamic):

 

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
SQL> 
SQL> show parameters db_name

NAME        TYPE VALUE
--------- ----------- ------------------------------
db_name string CDB

SQL> alter system set db_name='CDBNEW' scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

 

Let's start the database, now it should be mount:

 

SQL> startup mount;
ORACLE instance started.

Total System Global Area 767557632 bytes
Fixed Size 2929112 bytes
Variable Size 310382120 bytes
Database Buffers 448790528 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL>

 

And finally, we have to open the database with resetlogs:

 

SQL> alter database open resetlogs;

Database altered.

 

NOTE: if we don't open the database with resetlogs, when we try to open it in read-write and normally we will get the following error:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

Now, as we did when we changed a PDB name, let's verify if any "ID" changed after to rename the CDB's name:

 

SQL> select con_id, dbid, con_uid, guid, name, open_mode,restricted from v$containers

CON_ID DBID       CON_UID    GUID                             NAME       OPEN_MODE  RES
------ ---------- ---------- -------------------------------- ---------- ---------- ---
1      4097604774 1          FD9AC20F64D344D7E043B6A9E80A2F2F CDB$ROOT   READ WRITE NO
2      3232131500 3232131500 33E566D292696F62E0530401A8C0010D PDB$SEED   READ ONLY  NO
3      878113373  878113373  33E573FD368272A9E0530401A8C0AC1C PDB1NEW    MOUNTED
4      221610700  221610700  33E576C86F0F74FBE0530401A8C05574 PDB2       MOUNTED

SQL>

 

The only value that changed is the DBID  and only for the CDB$ROOT, al the others containers remains the same. This is because we used "RESETLOGS" when we open the database. 

 

 

출처 : http://www.toadworld.com/platforms/oracle/b/weblog/archive/2016/05/29/how-to-change-the-database-name-in-12c

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 12
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 50
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 112
» How to change the database name in 12c 명품관 2016.05.31 46
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 86
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 76
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 98
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 84
20 Quick tip on Function Based Indexes 명품관 2016.04.19 35
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 99
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 71
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 240
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 34
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 48
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 57
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 39
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 28
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 11
10 Oracle 12c SQL – Using JSON 명품관 2015.12.29 147
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 60
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 17
위로