How to fix RMAN-06026 & RMAN-06024 errors in RMAN during TSPITR

While Processing Tablespace Point-in-Time Recovery in RMAN, suddenly I got below errors.

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Cause:

This is the problem that rman can not identify the control file for the auxiliary database.

Error Example

RMAN> run

2> {

3> recover tablespace tspitr_tbs

4> until scn 965500

5> auxiliary destination '/u01/aux_dest';

6> }

Starting recover at 30-NOV-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='shta'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=shta_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u01/aux_dest

log_archive_dest_1='location=/u01/aux_dest'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes

Variable Size                100666596 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6381568 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until  scn 965500;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 30-NOV-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

Removing automatic instance

shutting down automatic instance

Oracle instance shut down

Automatic instance removed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 11/30/2015 17:57:01

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

Solution:

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

30      Full    9.36M      DISK        00:00:02     30-NOV-15

        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: TAG20151130T171313

        Piece Name: /u02/rman_bkp/control_bkp/c-1423639881-20151130-03.ctl

  Control File Included: Ckp SCN: 960296       Ckp time: 30-NOV-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

34      Full    9.36M      DISK        00:00:02     30-NOV-15

        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20151130T231314

        Piece Name: /u02/rman_bkp/control_bkp/c-1423639881-20151130-04.ctl

  Control File Included: Ckp SCN: 965398       Ckp time: 30-NOV-15

RMAN> catalog backuppiece '/u02/rman_bkp/control_bkp/c-1423639881-20151130-04.ctl';

cataloged backup piece

backup piece handle=/u02/rman_bkp/control_bkp/c-1423639881-20151130-04.ctl RECID=37 STAMP=897176040

Result :

RMAN> run

2> {

3> recover tablespace tspitr_tbs

4> until scn 965500

5> auxiliary destination '/u01/aux_dest';

6> }

Starting recover at 30-NOV-15

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='shta'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=shta_tspitr_ORCL

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/u01/aux_dest

log_archive_dest_1='location=/u01/aux_dest'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes

Variable Size                100666596 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6381568 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until  scn 965500;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 30-NOV-15

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

[--------------Output Trimmed--------------]

[--------------Output Trimmed--------------]

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  TSPITR_TBS read write';

sql 'alter tablespace  TSPITR_TBS offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace  TSPITR_TBS read write

sql statement: alter tablespace  TSPITR_TBS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_temp_c5s478w3_.tmp deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_3_c5s472fx_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_2_c5s47050_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/onlinelog/o1_mf_1_c5s46x2t_.log deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_sysaux_c5s42ldz_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_undotbs1_c5s42lh6_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/datafile/o1_mf_system_c5s42ldj_.dbf deleted

auxiliary instance file /u01/aux_dest/ORCL/controlfile/o1_mf_c5s427mf_.ctl deleted

Finished recover at 30-NOV-15

In my case, it worked. hope this will work in your case. If anyone have better options, please comment. :)

Thanks......

How to install ASM 11gr2 with Role separation (GRID & ORACLE user)

Installing ASM 11gr2 with Role separation (GRID & ORACLE user)

Create the grid & oracle OS users and oinstall, asmadmin, asmdba, asmoper, dba & oracle groups and create base directories ( Connect as ROOT user):

groupadd -g 1000 oinstall
groupadd -g 1100 asmadmin
groupadd -g 1200 dba
groupadd -g 1201 oper
groupadd -g 1300 asmdba
groupadd -g 1301 asmoper

grep oinstall /etc/group
grep asmadmin /etc/group
grep dba /etc/group
grep oper /etc/group
grep asmdba /etc/group
grep asmoper /etc/group

useradd -u 1100 -g oinstall -G asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c "Ur in GRID" grid

useradd -u 1101 -g oinstall -G dba,asmdba,oper -d /home/oracle -s /bin/bash -c "Ur in ORACLE" oracle

usermod -a -G dba grid

Note : If you omit this step, You will get “ORA-01031: insufficient privileges” during the database creation.

          Set shell limits for the oracle and grid user:

To improve the performance of the software on Linux systems, you must increase the shell limits for the

oracle user. (Do this in ROOT user)

vi  /etc/security/limits.conf

grid soft nproc 2047

grid hard nproc 16384

grid soft nofile 1024

grid hard nofile 65536

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

Connect as root user and execute the next commands:

id oracle
id grid
mkdir -p /u01/app/grid/product/11.2.0/grid
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
chown oracle:oinstall /u01
chmod -R 775 /u01

Creating .bash_profiles

#For Oracle user

su – oracle
vi .bash_profile

ORACLE_SID=ORCL; export ORACLE_SID

ORACLE_UNQNAME=ORCL; export ORACLE_UNQNAME

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”
export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

stty erase ^H

umask 022

#For Grid user

su – grid

vi .bash_profile

ORACLE_SID=+ASM; export ORACLE_SID

JAVA_HOME=/usr/local/java; export JAVA_HOME

ORACLE_BASE=/u01/app/grid; export ORACLE_BASE

ORACLE_HOME=/u01/app/grid/product/11.2.0/grid; export ORACLE_HOME

ORACLE_TERM=xterm; export ORACLE_TERM

NLS_DATE_FORMAT=”DD-MON-YYYY HH24:MI:SS”; export NLS_DATE_FORMAT

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11

PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH

THREADS_FLAG=native; export THREADS_FLAG

export TEMP=/tmp
export TMPDIR=/tmp

stty erase ^H

umask 022

Install the ASMLIB RPMs (Linux only):

Get the Kernel Version

[root@somoraasm /]# uname -r
2.6.18-164.el5PAE

Get these 3 ASMLib RPMs as per your OS kernel Version

oracleasm-support-2.1.3-1.el5.i386.rpm

oracleasmlib-2.0.4-1.el5.i386.rpm

oracleasm-2.6.18-164.el5PAE-2.0.5-1.el5.i686.rpm

Note : You can get RPMs in below Link

http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

Validate oracleasm RPMs were installed:

[root@somoraasm ~]# rpm -qa | grep oracleasm-support-2.1.3-1.el5
oracleasm-support-2.1.3-1.el5
[root@ somoraasm ~]# rpm -qa | grep oracleasmlib-2.0.4-1.el5
oracleasmlib-2.0.4-1.el5
[root@ somoraasm ~]# rpm -qa | grep oracleasm-2.6.18-164.el5PAE-2.0.5- 1.el5
oracleasm-2.6.18-164.el5PAE-2.0.5-1.el5

 Installing the RPM Packages

rpm -Uvh oracleasm-support-2.1.3-1.el5.i386.rpm

rpm -Uvh oracleasmlib-2.0.4-1.el5.i386.rpm

rpm -Uvh oracleasm-2.6.18-164.el5PAE-2.0.5-1.el5.i686.rpm

Configure ASM

/etc/init.d/oracleasm configure -i

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

List the disks available for ASM:

Add 2 Hard disks. We will use 2 SCSI disks of 10 GB each.

Before adding 2 hard disks of 10GB

[root@oraasmprod grid]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          13      104391   83  Linux

/dev/sda2              14        1925    15358140   83  Linux

/dev/sda3            1926        2562     5116702+  83  Linux

/dev/sda4            2563        3916    10876005    5  Extended

/dev/sda5            2563        3916    10875973+  8e  Linux LVM

After adding 2 hard disks of 10GB

[root@oraasmprod grid]# fdisk -l

Disk /dev/sda: 32.2 GB, 32212254720 bytes

255 heads, 63 sectors/track, 3916 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          13      104391   83  Linux

/dev/sda2              14        1925    15358140   83  Linux

/dev/sda3            1926        2562     5116702+  83  Linux

/dev/sda4            2563        3916    10876005    5  Extended

/dev/sda5            2563        3916    10875973+  8e  Linux LVM

Disk /dev/sdb: 10.7 GB, 10737418240 bytes

255 heads, 63 sectors/track, 1305 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

Disk /dev/sdc: 10.7 GB, 10737418240 bytes

255 heads, 63 sectors/track, 1305 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table

To avoid rebooting the machine to mount the hard disks

echo " - - - " >/sys/class/scsi_host/host0/scan

Note : You can restart the server or use the above command.

Now 2 Hard disks added:

fdisk /dev/sdb

[root@oraasmprod grid]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

The number of cylinders for this disk is set to 1305.

There is nothing wrong with that, but this is larger than 1024,

and could in certain setups cause problems with:

1) software that runs at boot time (e.g., old versions of LILO)

2) booting and partitioning software from other OSs

   (e.g., DOS FDISK, OS/2 FDISK)

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-1305, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):

Using default value 1305

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

2nd Disk

fdisk /dev/sdc

[root@oraasmprod grid]# fdisk /dev/sdc

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel. Changes will remain in memory only,

until you decide to write them. After that, of course, the previous

content won't be recoverable.

The number of cylinders for this disk is set to 1305.

There is nothing wrong with that, but this is larger than 1024,

and could in certain setups cause problems with:

1) software that runs at boot time (e.g., old versions of LILO)

2) booting and partitioning software from other OSs

   (e.g., DOS FDISK, OS/2 FDISK)

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-1305, default 1):

Using default value 1

Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):

Using default value 1305

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

Creating the ASM Disks

/etc/init.d/oracleasm start

Marking the 2 disks as ASM disks

/etc/init.d/oracleasm createdisk DATA /dev/sdb1

[root@oraasmprod grid]# /etc/init.d/oracleasm createdisk DATA /dev/sdb1

Marking disk "VOL1" as an ASM disk:                        [  OK  ]

/etc/init.d/oracleasm createdisk FRA /dev/sdc1

[root@oraasmprod grid]# /etc/init.d/oracleasm createdisk FRA /dev/sdc1

Marking disk "VOL2" as an ASM disk:                        [  OK  ]

Check the ASM disks

/etc/init.d/oracleasm listdisks

/etc/init.d/oracleasm querydisk DATA

/etc/init.d/oracleasm querydisk FRA

Copy the Oracle Database Software & Grid Infrastructure Software to location

[root@oraasmprod grid]# cd /u02

[root@oraasmprod u02]# ls

asm  linux_11gR2_database_1of2.zip  linux_11gR2_database_2of2.zip  linux_11gR2_grid.zip  lost+found

Unzip all RAR files:

[grid@oraasmprod u02]$ unzip linux_11gR2_grid.zip

[grid@oraasmprod u02]$ unzip linux_11gR2_database_1of2.zip

[grid@oraasmprod u02]$ unzip linux_11gR2_database_2of2.zip

Installing Grid Infrastructure Software:

[grid@oraasmprod ~]$ cd /u02

[grid@oraasmprod u02]$ ls

asm  grid  linux_11gR2_database_1of2.zip  linux_11gR2_database_2of2.zip  lost+found

[grid@oraasmprod u02]$ cd grid/

[grid@oraasmprod grid]$ ls

doc  install  response  rpm  runcluvfy.sh  runInstaller  sshsetup  stage  welcome.html

[grid@oraasmprod grid]$ ./runInstaller &

[1] 8980

[grid@oraasmprod grid]$ Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 3651 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 2783 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

[grid@oraasmprod grid]$ You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2015-08-10_01-54-41AM.log

Running 2 mandatory scripts

[root@oraasmprod u02]# /u01/app/oraInventory/orainstRoot.sh

[root@oraasmprod u02]# /u01/app/grid/product/11.2.0/grid/root.sh [Enter/y/y/y]

Installing Oracle Software and Database:

[oracle@oraasmprod ~]$ cd /u02/database/

[oracle@oraasmprod database]$ ls

doc  install  response  rpm  runInstaller  sshsetup  stage  welcome.html

[oracle@oraasmprod database]$ ./runInstaller &

[1] 16459

[oracle@oraasmprod database]$ Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB.   Actual 3302 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 2634 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

[oracle@oraasmprod database]$ You can find the log of this install session at:

 /u01/app/oraInventory/logs/installActions2015-08-10_02-53-54AM.log

Running 1 mandatory scripts

[root@oraasmprod u02]# /u01/app/oracle/product/11.2.0/dbhome_1/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/11.2.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

Finished product-specific root actions.