Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How a BACKUP AS COPY ... CONTROLFILE can be used to OPEN Database

JuanMOct 22 2015 — edited Nov 11 2015

I am creating a document like a “How To” to move a controlfile location for a Junior DBA.

But it seems I am the Junior because I am facing the following…

Action plan:

Moving/Renaming a Controlfile

Database version: 11.2.0.3

Moving controlfiles from:

/goldengate/ORCL/ORADATA/

To:

/goldengate/ORCL/CONTROLFILE

Step 1: Setting the environment variables

$> export ORACLE_SID=ORCL1

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

$> echo $ORACLE_SID

ORCL1

$> export ORACLE_BASE=/u01/app/oracle

$> echo $ORACLE_BASE

/u01/app/oracle

$> export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/dbhome_1

$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0.3/dbhome_1

Step 2: Checking control_files parameter

$> echo "SHOW PARAMETERS control_files" | sqlplus -S "/as sysdba"

NAME TYPE VALUE

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

control_files string /goldengate/ORCL/ORADATA/contr

ol01.ctl, /goldengate/ORCL/ORA

DATA/control02.ctl

Step 3: Shutting down the opened database

$> echo "SHUTDOWN IMMEDIATE;" | sqlplus -S "/as sysdba"

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 4: Mounting the database

$> echo "STARTUP MOUNT;" | sqlplus -S "/as sysdba"

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2235208 bytes

Variable Size 822084792 bytes

Database Buffers 3439329280 bytes

Redo Buffers 12132352 bytes

Database mounted.

Step 5: Creating a copy of the current controlfile

$> echo "BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/goldengate/ORCL/CONTROLFILE/control01.copy.ctl';" | rman target / nocatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 22 17:03:27 2015

connected to target database: ORCL (DBID=1420762587, not open)

using target database control file instead of recovery catalog

RMAN>

Starting backup at 22-OCT-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=58 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/goldengate/ORCL/CONTROLFILE/control01.copy.ctl tag=TAG20151022T170329 RECID=1 STAMP=893783011

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 22-OCT-15

RMAN>

Recovery Manager complete.

Step 6: Changing control_files parameter

$> echo "ALTER SYSTEM SET control_files='/goldengate/ORCL/CONTROLFILE/control01.copy.ctl' SCOPE=SPFILE;" | sqlplus -S "/as sysdba"

System altered.

Step 7: Shutting down the mounted database

$> echo "SHUTDOWN IMMEDIATE;" | sqlplus -S "/as sysdba"

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step 8: Mounting the database

$> echo "STARTUP MOUNT;" | sqlplus -S "/as sysdba"

ORACLE instance started.

Total System Global Area 4275781632 bytes

Fixed Size 2235208 bytes

Variable Size 822084792 bytes

Database Buffers 3439329280 bytes

Redo Buffers 12132352 bytes

Database mounted.

Step 9: Checking control_files parameter

$> echo "SHOW PARAMETERS control_files" | sqlplus -S "/as sysdba"

NAME TYPE VALUE

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

control_files string /goldengate/ORCL/CONTROLFILE/c

ontrol01.copy.ctl

Step 10: Open the mounted database

$> echo "ALTER DATABASE OPEN;" | sqlplus -S "/as sysdba"

ALTER DATABASE OPEN

ERROR at line 1:

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

$> echo "ALTER DATABASE OPEN NORESETLOGS;" | sqlplus -S "/as sysdba"

ALTER DATABASE OPEN NORESETLOGS

ERROR at line 1:

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Then…

$> sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 22 17:14:43 2015

SQL> CONNECT /as sysdba

Connected.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 621941 generated at 10/22/2015 16:57:33 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/arch1_11_892981851.dbf

ORA-00280: change 621941 for thread 1 is in sequence #11

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

SQL> ALTER DATABASE OPEN;

ALTER DATABASE OPEN

ERROR at line 1:

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

Questions:

What I am misunderstanding? BACKUP AS COPY it’s not really a COPY.

Why I can’t use the “copy” of the controlfile created by RMAN?

Note:

If I just copy the controlfile to new location when database is shutting down everything works fine.

Thanks in advance.

Juan M

This post has been answered by Dude! on Oct 22 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 9 2015

Added on Oct 22 2015

#general-database-discussions

31 comments

9,225 views