tetsoukyl的Blog: Oracle DB - 11g 使用 DBNEWID utility (nid) 指定 new DBNAME db

舊 db_name: PRD
新 db_name: QAS

STEP 1: Backup the database.

STEP 2: Mount the database after a clean shutdown:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

STEP 3: 使用 DBNEWID utility (nid) 指定 new DBNAME from the command line using a user with SYSDBA privilege:

[oracle@testdb2 ~]$ nid TARGET=sys/xxxxxxxxx DBNAME=QAS

DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 21 16:05:19 2019

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

Connected to database PRD (DBID=2086780556)

Connected to server version 11.2.0

Control Files in database:
/u02/oradata/PRD/control01.ctl
/u01/app/oracle/fast_recovery_area/PRD/control02.ctl

Change database ID and database name PRD to QAS? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2086780556 to 2414354175
Changing database name from PRD to QAS
Control File /u02/oradata/PRD/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - modified
Datafile /u02/oradata/PRD/system01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/sysaux01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/undotbs01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/users01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/example01.db - dbid changed, wrote new name
Datafile /u02/oradata/QAS/test01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/test2/test2.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/temp01.db - dbid changed, wrote new name
Control File /u02/oradata/PRD/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to QAS.
Modify parameter file and generate a new password file before restarting.
Database ID for database QAS changed to 2414354175.
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.

STEP 5: Modify the DB_NAME parameter. The startup will result in an error but proceed anyway.

[oracle@testdb2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:11:10 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
ORA-01103: database name 'QAS' in control file is not 'PRD'

--這是因為spfile仍然記錄舊的db_name

SQL> alter system set db_name=QAS scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

STEP 6: Create a new password file: (選用)

orapwd file=$ORACLE_HOME/dbs/orapwQAS password=password entries=10

STEP 7: Rename the SPFILE to match the new DBNAME. (選用)

STEP 8:ORACLE_SID=QAS; export ORACLE_SID (選用)

STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

[oracle@testdb2 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@testdb2 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@testdb2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@testdb2 admin]$
[oracle@testdb2 admin]$ more tnsnames.ora

QAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QAS)
)
)

PRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRD)
)
)

DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)

CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)

lsnrctl reload

STEP 10: Open the database with RESETLOGS:

[oracle@testdb2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@testdb2 dbs]$ ls
hc_PRD.dat init.ora lkPRD orapwPRD spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:16:36 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

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

Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.

SQL>

STEP 11: Backup the database.

--一些觀察
-- DB Name 改為 QAS
SQL> select name from v$database;

NAME
---------
QAS

-- Instance Name 維持PRD
SQL> select instance from v$thread ;

INSTANCE
--------------------------------------------------------------------------------
PRD

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
PRD

[oracle@testdb2 dbs]$ ps -ef|grep ora_
oracle 3424 1 0 16:16 ? 00:00:00 ora_pmon_PRD
oracle 3448 1 0 16:16 ? 00:00:00 ora_smon_PRD

-- $ORACLE_HOME/dbs 多一個 lkQAS
[oracle@testdb2 admin]$ cd $ORACLE_HOME
[oracle@testdb2 db_1]$ cd dbs
[oracle@testdb2 dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Nov 21 16:17 hc_PRD.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Nov 18 16:29 lkPRD
-rw-r-----. 1 oracle oinstall 24 Nov 21 16:16 lkQAS
-rw-r-----. 1 oracle oinstall 1536 Nov 18 15:55 orapwPRD
-rw-r-----. 1 oracle oinstall 2560 Nov 21 16:18 spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:24:42 2019

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, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilePRD.ora
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/oradata/PRD/control01.ctl
, /u01/app/oracle/fast_recover
y_area/PRD/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/PRD/temp01.dbf

SQL> select DB_UNIQUE_NAME from v$database;

DB_UNIQUE_NAME
------------------------------
QAS

[oracle@testdb2 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-NOV-2019 16:52:39

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 21-NOV-2019 16:51:28
Uptime 0 days 0 hr. 1 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/testdb2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PRDXDB" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
Service "QAS" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
The command completed successfully

[root@testdb2 ~]# cd /u01/app/oracle

[root@testdb2 oracle]# ll
total 20
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:22 admin
drwxr-xr-x. 2 oracle oinstall 4096 Oct 30 16:41 checkpoints
drwxrwxr-x. 11 oracle oinstall 4096 Oct 30 16:39 diag
drwxr-xr-x. 4 oracle oinstall 4096 Nov 21 16:17 fast_recovery_area
drwxrwxr-x. 3 oracle oinstall 4096 Oct 30 15:43 product

[root@testdb2 oracle]# ll /u01/app/oracle/admin/PRD/
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:24 adump

[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:58 PRD
drwxr-x---. 3 oracle oinstall 4096 Nov 21 16:17 QAS

[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 21 16:17 onlinelog
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/onlinelog/

[root@testdb2 oracle]# su - oracle
[oracle@testdb2 ~]$ env|grep ORA
ORACLE_UNQNAME=PRD
ORACLE_SID=PRD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@testdb2 ~]$ cd $ORACLE_BASE
[oracle@testdb2 oracle]$ ls
admin checkpoints diag fast_recovery_area product
[oracle@testdb2 oracle]$ cd diag/rdbms/

[oracle@testdb2 rdbms]$ ll
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:14 prd
drwxr-xr-x. 3 oracle oinstall 4096 Nov 21 16:16 qas

[oracle@testdb2 rdbms]$ cd qas
[oracle@testdb2 qas]$ ll
total 4
-rw-r-----. 1 oracle oinstall 0 Nov 21 16:16 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Nov 21 16:16 PRD

[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 alert
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 hm
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incident
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 ir
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 lck
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 stage
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:23 trace

[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_*.log
-rw-r-----. 1 oracle oinstall 6758 Nov 21 16:22 /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_PRD.log
[oracle@testdb2 trace]$

tetsoukyl的Blog

2019年11月21日星期四

Oracle DB - 11g 使用 DBNEWID utility (nid) 指定 new DBNAME db_id

1 則留言:

2019年11月21日 星期四

Oracle DB - 11g 使用 DBNEWID utility (nid) 指定 new DBNAME db_id

1 則留言:

2019年11月21日星期四