Tuesday, 26 January 2016

Move database to another Mount point using RMAN

Move database to another Mount point(NOARCHIVE MODE DB).

Easiest:
1. Shutdown the DB.
2. Copy the data file using OS commands.
3. Start database in mount mode.
4. Get RMAN going:

oracle@localhost:/oradata/ALOK$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 27 06:08:23 2016

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

connected to target database: ALOK (not mounted)

RMAN> catalog start with '/oradata/ALOK';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 01/27/2016 06:08:34
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

RMAN> catalog start with '/oradata/ALOK';

searching for all files that match the pattern /oradata/ALOK

List of Files Unknown to the Database
=====================================
File Name: /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf
File Name: /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
File Name: /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
File Name: /oradata/ALOK/o1_mf_temp_cb1x7qrz_.tmp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf
File Name: /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
File Name: /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
File Name: /oradata/ALOK/o1_mf_temp_cb1x7qrz_.tmp
File Name: /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf"
datafile 3 switched to datafile copy "/oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf"
datafile 4 switched to datafile copy "/oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf"
datafile 6 switched to datafile copy "/oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf"

RMAN> recover database;

Starting recover at 27-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 27-JAN-16

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 01/27/2016 06:09:40
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open noresetlogs;

Statement processed

RMAN> report schema;

Report of database schema for database with db_unique_name ALOK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
3    710      SYSAUX               ***     /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf
4    60       UNDOTBS1             ***     /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
6    96       USERS                ***     /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /u01/app/oracle/oradata/ALOK/datafile/o1_mf_temp_cb1x7qrz_.tmp

RMAN>

RMAN>                                                                                                        

Note : Left the errors for reference, I started from no mount state. Recovery may not be required if db was gracefully shutdown.

Future protection for file creation:
alter system set db_create_file_dest='/oradata/ALOK';

Dropped the tempfile and restarted the database. That took care of the tempfile..got auto created.

oracle@localhost:/u01/app/oracle/oradata/ALOK$ rm /u01/app/oracle/oradata/ALOK/datafile/o1_mf_temp_cb1x7qrz_.tmp

oracle@localhost:/u01/app/oracle/oradata/ALOK$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 27 06:25:08 2016

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                  2289016 bytes
Variable Size            1056965256 bytes
Database Buffers          587202560 bytes
Redo Buffers                7061504 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@localhost:/u01/app/oracle/oradata/ALOK$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jan 27 06:25:28 2016

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

connected to target database: ALOK (DBID=3000882446)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ALOK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     /oradata/ALOK/o1_mf_system_cb1x1s1b_.dbf
3    710      SYSAUX               ***     /oradata/ALOK/o1_mf_sysaux_cb1wwmp1_.dbf
4    60       UNDOTBS1             ***     /oradata/ALOK/o1_mf_undotbs1_cb1x5jg0_.dbf
6    96       USERS                ***     /oradata/ALOK/o1_mf_users_cb1x5dl0_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    60       TEMP                 32767       /oradata/ALOK/ALOK/datafile/o1_mf_temp_cbjrpjgg_.tmp

RMAN>                          




No comments:

Post a Comment