Phạm Văn Hiếu: [ORACLE-DATABASE-11G] Using nid to rename an oracle database

1/ Check Database Status before rename

- Check tnsping

[oracle@oeldg01 ~]$ tnsping oldname

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-APR-2016 16:39:59

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oeldg01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oldname)))

OK (0 msec)

- Check Processes

[oracle@oeldg01 ~]$ ps -ef | grep -i ora_* | grep -v grep

oracle 4968 1 0 Apr28 ? 00:00:36 /u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/11.2.0/dbhome_1/bin/emwd.pl dbconsole /u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg/sysman/log/emdb.nohup

oracle 5010 4968 0 Apr28 ? 00:13:44 /u01/app/oracle/product/11.2.0/dbhome_1/bin/emagent

root 33292 33248 0 16:20 pts/0 00:00:00 su - oracle

oracle 33293 33292 0 16:20 pts/0 00:00:00 -bash

oracle 34008 4968 4 16:22 ? 00:01:13 /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 -Doracle.home=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg/sysman -DEMSTATE=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/java2.policy -Djavax.net.ssl.KeyStore=/u01/app/oracle/product/11.2.0/dbhome_1/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/11.2.0/dbhome_1 -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/u01/app/oracle/product/11.2.0/dbhome_1/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/server.xml

root 34376 33652 0 16:23 pts/1 00:00:00 su - oracle

oracle 34377 34376 0 16:23 pts/1 00:00:00 -bash

oracle 37509 1 0 16:35 ? 00:00:00 ora_pmon_oldname

oracle 37514 1 0 16:35 ? 00:00:00 ora_psp0_oldname

oracle 37520 1 5 16:35 ? 00:00:38 ora_vktm_oldname

oracle 37530 1 0 16:35 ? 00:00:00 ora_gen0_oldname

oracle 37534 1 0 16:35 ? 00:00:00 ora_diag_oldname

oracle 37537 1 0 16:35 ? 00:00:00 ora_dbrm_oldname

oracle 37539 1 0 16:35 ? 00:00:00 ora_dia0_oldname

oracle 37541 1 0 16:35 ? 00:00:00 ora_mman_oldname

oracle 37543 1 0 16:35 ? 00:00:00 ora_dbw0_oldname

oracle 37545 1 0 16:35 ? 00:00:00 ora_lgwr_oldname

oracle 37547 1 0 16:35 ? 00:00:00 ora_ckpt_oldname

oracle 37549 1 0 16:35 ? 00:00:00 ora_smon_oldname

oracle 37551 1 0 16:35 ? 00:00:00 ora_reco_oldname

oracle 37553 1 0 16:35 ? 00:00:01 ora_mmon_oldname

oracle 37555 1 0 16:35 ? 00:00:00 ora_mmnl_oldname

oracle 37557 1 0 16:35 ? 00:00:00 ora_d000_oldname

oracle 37559 1 0 16:35 ? 00:00:00 ora_s000_oldname

oracle 37601 1 0 16:36 ? 00:00:00 ora_qmnc_oldname

oracle 37624 1 0 16:36 ? 00:00:00 ora_cjq0_oldname

oracle 37637 1 0 16:36 ? 00:00:00 ora_q000_oldname

oracle 37641 1 0 16:36 ? 00:00:00 ora_q001_oldname

oracle 38781 1 0 16:41 ? 00:00:00 ora_smco_oldname

oracle 38783 1 0 16:41 ? 00:00:00 ora_w000_oldname

- Try to connect, check name

[oracle@oeldg01 ~]$ export ORACLE_SID=oldname

[oracle@oeldg01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 29 16:48:08 2016

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> select name from v$database;

NAME

---------

OLDNAME

2/ OK. Let’s start changing database name.

On tab 1

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 413372416 bytes

Fixed Size 2253784 bytes

Variable Size 327158824 bytes

Database Buffers 79691776 bytes

Redo Buffers 4268032 bytes

Database mounted.

SQL>

Open New Tab 2:

[oracle@oeldg01 ~]$ export ORACLE_SID=oldname

[oracle@oeldg01 ~]$ nid TARGET=sys/oracle@oldname DBNAME=newname

DBNEWID: Release 11.2.0.4.0 - Production on Fri Apr 29 16:54:40 2016

Connected to database OLDNAME (DBID=137874822)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/oldname/control01.ctl

/u01/app/oracle/oradata/oldname/control02.ctl

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

Proceeding with operation

Changing database ID from 137874822 to 2288653456

Changing database name from OLDNAME to NEWNAME

Control File /u01/app/oracle/oradata/oldname/control01.ctl - modified

Control File /u01/app/oracle/oradata/oldname/control02.ctl - modified

Datafile /u01/app/oracle/oradata/oldname/system01.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/oldname/sysaux01.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/oldname/undotbs01.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/oldname/users01.db - dbid changed, wrote new name

Datafile /u01/app/oracle/oradata/oldname/temp01.db - dbid changed, wrote new name

Control File /u01/app/oracle/oradata/oldname/control01.ctl - dbid changed, wrote new name

Control File /u01/app/oracle/oradata/oldname/control02.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to NEWNAME.

Modify parameter file and generate a new password file before restarting.

Database ID for database NEWNAME changed to 2288653456.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

- Copy Password file, Create new Parameter file.

[oracle@oeldg01 ~]$ cd $ORACLE_HOME/dbs

[oracle@oeldg01 dbs]$ cp orapwoldname orapwnewname

[oracle@oeldg01 ~]$ export ORACLE_SID=newname

[oracle@oeldg01 ~]$ sqlplus / as sysdba

SQL> create pfile='/setup/pfile_for_newname.ora' from spfile;

Open new tab 3:

- Modify Parameters for newname

[root@oeldg01 setup]# sed 's/oldname/newname/g' pfile_for_newname.ora > pfile_for_newname_run.ora

- Move Trace file, Datafile to new location

[oracle@oeldg01 ~]$ mv /u01/app/oracle/admin/oldname /u01/app/oracle/admin/newname

[oracle@oeldg01 ~]$ mv /u01/app/oracle/oradata/oldname /u01/app/oracle/oradata/newname

Return tab 2:

- Start mount, modify location of datafile to new.

SQL> startup mount pfile='/setup/pfile_for_newname_run.ora'

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/system01.dbf' to '/u01/app/oracle/oradata/newname/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/sysaux01.dbf' to '/u01/app/oracle/oradata/newname/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/undotbs01.dbf' to '/u01/app/oracle/oradata/newname/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/users01.dbf' to '/u01/app/oracle/oradata/newname/users01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/redo01.log' to '/u01/app/oracle/oradata/newname/redo01.log';

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/redo02.log' to '/u01/app/oracle/oradata/newname/redo02.log';

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/redo03.log' to '/u01/app/oracle/oradata/newname/redo03.log';

Open Resetlogs Database with new name of Database:

SQL> alter database open resetlogs;

SQL> create spfile='$ORACLE_HOME/dbs/spfilenewname.ora' from pfile='/setup/pfile_for_newname_run.ora';

SQL>exit;

3/ Check listener and Database status after rename

[oracle@oeldg01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-APR-2016 17:29:05

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oeldg01)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 28-APR-2016 06:44:28

Uptime 1 days 10 hr. 44 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/oeldg01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldg01)(PORT=1521)))

Services Summary...

Service "newname" has 1 instance(s).

Instance "newname", status READY, has 1 handler(s) for this service...

Service "newnameXDB" has 1 instance(s).

Instance "newname", status READY, has 1 handler(s) for this service...

Phạm Văn Hiếu

Thứ Hai, 6 tháng 6, 2016

[ORACLE-DATABASE-11G] Using nid to rename an oracle database

Không có nhận xét nào: