Tuesday, June 6, 2017

Rename database on ASM

Rename database is a very frequent task for a DBA. Here i'll demonstrate the steps to rename a database on ASM.

Existing Name : mydb
New Name : mypoc

Step1 : start database in no-mount mode



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes
Database mounted.



Step2 : Use NID to rename database



$ ./nid target=/ setname=yes dbname=mypoc
DBNEWID: Release 11.2.0.4.0 - Production on Sat Feb 13 11:31:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database MYDB (DBID=29293929)
Connected to server version 11.2.0
Control Files in database:
    +DG1/mydb/controlfile/current.320.903446127
    +REDO/mydb/controlfile/current.264.903446127
    +TEST/mydb/controlfile/current.256.903446127
Change database name of database MYDB to MYPOC? (Y/[N]) => Y
Proceeding with operation
Changing database name from MYDB to MYPOC
    Control File +DG1/mydb/controlfile/current.320.903446127 - modified
    Control File +REDO/mydb/controlfile/current.264.903446127 - modified
    Control File +TEST/mydb/controlfile/current.256.903446127 - modified
    Datafile +DG1/mydb/datafile/system.322.90344616 - wrote new name
    Datafile +DG1/mydb/datafile/sysaux.321.90344616 - wrote new name
    Datafile +DG1/mydb/datafile/undo_t01.324.90344617 - wrote new name
    Datafile +DG1/pocdgs/tempfile/temp_t02.323.90369692 - wrote new name
    Control File +DG1/mydb/controlfile/current.320.903446127 - wrote new name
    Control File +REDO/mydb/controlfile/current.264.903446127 - wrote new name
    Control File +TEST/mydb/controlfile/current.256.903446127 - wrote new name
    Instance shut down
Database name changed to MYPOC.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.



Step3 : Generate new spfile and password file



$ cp spfilemydb.ora spfilemypoc.ora

$ mv orapwmydb orapwmypoc

$ . oraenv
ORACLE_SID = [mydb] ? mypoc
ORACLE_HOME = [/oracle] ? /u01/app/oracle/product
The Oracle base has been set to /oracle

$ ./sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 13 11:53:41 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/dbs/spfilemypoc.ora

SQL> create pfile from spfile;
File created.

$ vi initmypoc.ora
>>CHANGES ALL THE OCCURANCE OF "MYDB" TO "MYPOC", INCLUDING CONTROL FILE LOCATION

SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> create spfile from pfile;
File created.

$ mkdir -p /oracle/mypoc

SQL> startup nomount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes



Step4 : Create new controlfiles from existing controlfile



$ ./rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 13 12:03:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYPOC (not mounted)

RMAN> restore controlfile from '+DG1/mydb/controlfile/current.320.903446127';

[This command will create new controlfiles in all those diskgroups where it was previously existing, it’ll just change the file location from +dg_name/mydb to +dg_name/mypoc]

Starting restore at 13-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=416 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DG1/mypoc/controlfile/current.325.903701019
output file name=+REDO/mypoc/controlfile/current.311.903701019
output file name=+TEST/mypoc/controlfile/current.257.903701019
Finished restore at 13-FEB-16

RMAN> shutdown
Oracle instance shut down
Recovery Manager complete.

$ ./sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 13 12:04:17 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             448793680 bytes
Database Buffers          381681664 bytes
Redo Buffers                2371584 bytes
Database mounted.

SQL> select name from v$controlfile;
NAME
+DG1/mypoc/controlfile/current.325.903701019
+REDO/mypoc/controlfile/current.311.903701019
+TEST/mypoc/controlfile/current.257.903701019



Step5 : Rename datafiles



$ ./rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 13 12:04:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYPOC (DBID=2114307438, not open)

RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name MYPOC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     +DG1/mydb/datafile/system.322.903446163
2    500      SYSAUX               ***     +DG1/mydb/datafile/sysaux.321.903446169
3    500      UNDO_T01             ***     +DG1/mydb/datafile/undo_t01.324.903446177
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP_T02             500         +DG1/mydb/tempfile/temp_t02.323.903696927

RMAN> LIST COPY OF DATABASE;
specification does not match any datafile copy in the repository

RMAN> BACKUP AS COPY DATABASE FORMAT '+DG1';
Starting backup at 13-FEB-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG1/mydb/datafile/system.322.903446163
output file name=+DG1/mypoc/datafile/system.328.903701701 tag=TAG20160213T121459 RECID=23 STAMP=903701701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG1/mydb/datafile/sysaux.321.903446169
output file name=+DG1/mypoc/datafile/sysaux.327.903701701 tag=TAG20160213T121459 RECID=24 STAMP=903701702
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG1/mydb/datafile/undo_t01.324.903446177
output file name=+DG1/mypoc/datafile/undo_t01.326.903701705 tag=TAG20160213T121459 RECID=25 STAMP=903701705
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG1/mypoc/controlfile/backup.331.903701707 tag=TAG20160213T121459 RECID=26 STAMP=903701707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-FEB-16
channel ORA_DISK_1: finished piece 1 at 13-FEB-16
piece handle=+DG1/mypoc/backupset/2016_02_13/nnsnf0_tag20160213t121459_0.332.903701709 tag=TAG20160213T121459 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-FEB-16

RMAN> LIST COPY OF DATABASE;
List of Datafile Copies
=======================
Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
23      1    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/system.328.903701701
        Tag: TAG20160213T121459
24      2    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/sysaux.327.903701701
        Tag: TAG20160213T121459
25      3    A 13-FEB-16       499369     13-FEB-16
        Name: +DG1/mypoc/datafile/undo_t01.326.903701705
        Tag: TAG20160213T121459

RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DG1/mypoc/datafile/system.328.903701701"
datafile 2 switched to datafile copy "+DG1/mypoc/datafile/sysaux.327.903701701"
datafile 3 switched to datafile copy "+DG1/mypoc/datafile/undo_t01.326.903701705"

RMAN> report schema;
Report of database schema for database with db_unique_name MYPOC
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    500      SYSTEM               ***     +DG1/mypoc/datafile/system.328.903701701
2    500      SYSAUX               ***     +DG1/mypoc/datafile/sysaux.327.903701701
3    500      UNDO_T01             ***     +DG1/mypoc/datafile/undo_t01.326.903701705
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    500      TEMP_T02             500         +DG1/pocdgs/tempfile/temp_t02.323.903696927

RMAN> alter database open;
database opened

$ ./sqlplus / as sysdba

SQL> alter tablespace TEMP_T02 add tempfile;
Tablespace altered.

SQL> alter tablespace TEMP_T02 drop tempfile '+DG1/pocdgs/tempfile/temp_t02.323.903696927';
Tablespace altered.

SQL> select name from v$database;
MYPOC

SQL> select instance_name from v$instance;
mypoc

SQL> select name from v$datafile;
+DG1/mypoc/datafile/system.328.903701701
+DG1/mypoc/datafile/sysaux.327.903701701
+DG1/mypoc/datafile/undo_t01.326.903701705



Step 6 :Re-create redo logfile



SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           CURRENT

SQL> ALTER DATABASE ADD LOGFILE group 3 ('+REDO');
Database altered.

SQL> ALTER DATABASE ADD LOGFILE group 4 ('+REDO');
Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           CURRENT
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            UNUSED
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           ACTIVE
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            CURRENT
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER SYSTEM CHECKPOINT;
System altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         1 +REDO/mydb/onlinelog/group_1.306.903446197                           INACTIVE
         2 +REDO/mydb/onlinelog/group_2.307.903446197                           INACTIVE
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            CURRENT
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
    GROUP# MEMBER                                                                 STATUS
---------- ---------------------------------------------------------------------- ----------------
         3 +REDO/mypoc/onlinelog/group_3.310.903702355                            CURRENT
         4 +REDO/mypoc/onlinelog/group_4.309.903702377                            UNUSED






No comments:

Post a Comment