Restore Archive logs from Disk Based Backups gives RMAN-06026: some targets not found - aborting restore RMAN-06102: no channel to restore a backup or copy of log thread 1 seq

One of our DG has gone Out of SYNC. Primary database is in MAXIMUM AVAILABILITY mode and we have specified standby location as an alternate destination.

Now our Primary archive diskgroup got 100% full and one of our team member has ran backup of archive logs to disk (Our Backup Tapes are not Working at that Moment) with the delete input option which has backed up all the Archives and deleted from Primary. 

Now Standby database is looking for archive log which is not in Diskgroup but in RMAN disk backup. 

##########################

#   Task 
##########################

Restore Archive logs from Disk Based Backups.

So our team member has started to make it sync by trying to restore the archives from the backup and got the below errors.

##########################
#   Command Used  
##########################

Command to Restore Archive logs to different location.


run
{
allocate channel c2 device type disk;
set archivelog destination to '/opt/oracle/backup/oralin/arch_bkp/';
restore archivelog from logseq 105408 until logseq 105413;
}

When running the restore got the below errors, 

##########################
#   Errors  
##########################

RMAN> run
{
allocate channel c2 device type disk;
set archivelog destination to '/opt/oracle/backup/oralin/arch_bkp/';
restore archivelog from logseq 105408 until logseq 105413;
}
2> 3> 4> 5> 6> 7>
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_SBT_TAPE_4
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=862 devtype=DISK
allocated channel: c2
channel c2: sid=1309 devtype=DISK
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 27-FEB-13
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/27/2013 22:28:14
RMAN-06026: some targets not found - aborting restore
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 105413 lowscn 13117372976201
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 105412 lowscn 13117372752915
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 105411 lowscn 13117372598071
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 105410 lowscn 13117372461415
RMAN-06102: no channel to restore a backup or copy of log thread 1 seq 105408 lowscn 13117372091300
RMAN>

We spent more time on investigating this error and to resolve the above error. But later on further investigation i found the problem made by the team member and the solution is very simple.

##########################
#   Pre checks 
##########################

1.  Check whether backup of Archive Logs available. 
2.  Is the Backup Stored in TAPE or in DISK. 

##########################

#  Solution
##########################

 Check whether backup of Archive Logs available. 

RMAN> list backup of archivelog from logseq 105408 until logseq 105413 thread 1;
List of Backup Sets
===================
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
56800656 577.25M    SBT_TAPE    00:07:21     27-FEB-13
        BP Key: 56800662   Status: AVAILABLE  Compressed: NO  Tag: oralin_FULL_0
        Handle: bk_11040_1_808444905   Media: O00100
  List of Archived Logs in backup set 56800656
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    105408  13117372091300 26-FEB-13 13117372283649 26-FEB-13
  1    105409  13117372283649 26-FEB-13 13117372461415 26-FEB-13
  1    105410  13117372461415 26-FEB-13 13117372598071 26-FEB-13
  1    105411  13117372598071 26-FEB-13 13117372752915 26-FEB-13
  1    105412  13117372752915 26-FEB-13 13117372976201 26-FEB-13
  1    105413  13117372976201 26-FEB-13 13117373012970 27-FEB-13

From the above output if you can see the Backup is stored in SBT_TAPE, but our team member has allocated disk Channel because he took backup of archives to disk.

But the archives which needed to restore are already backed up in TAPE and its his understanding that the archives are stored in DISK Backup.

So After removing the Disk channel allocation command, the restore was successful.

RMAN>  run

{
set archivelog destination to '/opt/oracle/backup/oralin/arch_bkp/';
restore archivelog logseq 105409 thread 1;
}
2> 3> 4> 5>

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 27-FEB-13
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=862 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.1 (2011020313)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1309 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.1 (2011020313)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=617 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.1 (2011020313)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=576 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 7.1 (2011020313)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=359 devtype=DISK
channel ORA_SBT_TAPE_1: starting archive log restore to user-specified destination
archive log destination=/opt/oracle/backup/oralin/arch_bkp/
channel ORA_SBT_TAPE_1: restoring archive log
archive log thread=1 sequence=105409
channel ORA_SBT_TAPE_1: reading from backup piece bk_11063_1_808494442
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_11063_1_808494442 tag=TAG20130227T134717
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:37
Finished restore at 27-FEB-13

So the moral of this story is listen what your team member says but always start with your own investigation path. :)

Check DATAPUMP Dump File is corrupted or not

Some times we may be in a situation, to check whether the dump file exported long time back is VALID or not.

Or Application Team is saying that the dumpfile provided by us is corrupted.

Easiet way to check whether a DATAPUMP dump file is corrupted or not.

##########################

#  Task 
##########################

Check Whether a DATAPUMP Export DUMPFILE is corrupted or valid

##########################
#   Use  
##########################

 Use SQLFILE Parameter to Detect Corruption 

##########################

## Solution
##########################

 impdp system/*** directory=dump_dir dumpfile=expdp.dmp logfile=corruption_check.log sqlfile=corruption_check.sql 
                                                                                                                                         
What does this sqlfile do? 

Here we have used sqlfile option, which will not import any data to the database.

This will write all DDL statements (which will be executed if a import is performed) into the file which we mentioned in the command. 

So this will read the entire DATAPUMP Export dump file and will report if a corruption is detected.

Find Time Taken(Elapsed) by Objects Types During DATAPUMP Export

Some times we might have thought to know how much time the datapump utility took to export different Object Types.

We have a undocumented parameter in DATAPUMP to check how much it took to export different Object Types.

##########################

#  Task 
##########################

Find Time Taken(Elapsed) by Objects Types During DATAPUMP Export

##########################
#   Parameter  
##########################

 Metrics = Y 

##########################

## Solution
##########################

 Using Metrics Parameter in export, it will list out how much time elapsed to take export of specific object and this information will be recorded in the logfile used in the export command. 

Shrink Temporary Tablespace 11g New Features

In 11g, enhancements has been made to Temporary tablespace.

In Previous Versions, if tempfile has grown big and needs to be resized, the procedure we used to follow is to create a new temporary tablespace and drop the bigger one.

In 11g onwards, Shrinking of temp files can be done and also we have new data dictionary table to find the Free space in the temporary tablespace.

##########################

#  Task 
##########################

Shrinking of Bigger Temp File.

##########################
#   Commands 
##########################

1.  Alter Tablespace temp shrink tempfile '+DATA/mydb/tempfile/temp.264.798205781' keep 5G; 
2.  Alter Tablespace temp shrink space; 

New Data Dictionary Table to Check Temporary Tablespace Free Space.

Select * from dba_temp_free_space;

##########################

## Solution
##########################

Check if oracle database Service is running in windows

Most of Oracle DBA'S who works and gets accustomed with Linux Environments, says that working in windows environment is bit challenging as a DBA. 

One command which DBA's use on a DAILY basis is to find whether oracle database is running in the server or not by using ps -ef command.

In Windows also there is a simple command to check this. 

##########################

#  Command To Check 
##########################

  Tasklist /svc | find "oracle"  

Here it showing that two database service is running,

1) ORA10G
2)ORAWIN

See Below link for How to Start Database Service & Database in Windows Environment?

http://stepintooracledba.blogspot.in/2010/05/there-are-two-methods-to-start-database.html

ORA-23319: parameter value "DD-MON-YYYY HH24:MI:SS" is not appropriate

When i tried to alter a job's Next Date, 

##########################
#  Error
##########################

##########################
#  Error Occurred
##########################

Error occurs when tried to alter a job's next date

##########################
## Cause
##########################

 To_date function is missed. 

##########################

## Solution
##########################

After Modifying the command with To_Date function, the command succeeded.

ORA-02095 MisLeading and its not true always

We used to get ORA-02095 error, if we are trying to alter a STATIC parameter when issuing ALTER SYSTEM command with Scope=both.

I have tried to alter control_files parameter with SCOPE=SPFILE when database is using SPFILE. I was confused why its throwing error when i use scope=spfile option.

Later Found that ORA-02095 error is misleading. Check below for the scenario i faced.

##########################
#   Error
##########################

##########################

#   Error Occurred
##########################

Error occurs when tried to alter control_files parameter using Alter system command with scope=spfile option

##########################
##  Cause
##########################

 ORA-02095 error is misleading, the actual problem is the Brackets '( )' i used in Alter system Command 

##########################

##  Solution
##########################

When setting Controlfiles parameter dont use Brackets, multiple control files can be given using Single Quotes and commas.

Alter system set control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/oracle/oradata/mydb/control02.ctl' scope=spfile;

See Below, when we issue the Command without Brackets it worked like a charm, but the Error which gave was misleading and confusing us.

For ORA-02095 Error, Please look into,

http://stepintooracledba.blogspot.com/2013/01/ora-02095-specified-initialization.html

Find OS Blocksize in Windows & Unix/Linux

In Oracle We say that Database Block Size should be multiple of OS Block Size.

So how to find Blocksize in Windows & Unix/Linux?

##########################
#   Task 
##########################

Find OS Block Size in Windows & Unix/Linux Environments.

##########################
#   In Windows 
##########################

 fsutil fsinfo ntfsinfo c: | find /i "bytes" 

So My Windows Block size is  4096 


##########################
#   In Solaris 
##########################

 df -g  

 df -g /tmp  

So My Solaris Block size is  8192 

By Default Solaris OS Block Size will be  8192  and it may vary from File system to File System

##########################
#   In Unix/Linux 
##########################


 tune2fs -l  

Move datafile from File system to ASM (or) Move datafile from NON-ASM to ASM

One of our application team has deployed new version of application, and we gave privilege to create tabelspace for their deployment.

As usual application team created the datafile in File System but our database is using ASM. So We need to Move datafile from File system to ASM.

##########################

#  Task 
##########################

Move Datafile From File system to ASM Diskgroup.

##########################
#  Pre checks 
##########################

1. File Name and Tablespace Name where Datafiles are Created.
2. Check Whether Diskgroup is having Sufficient Space
3. Intimate Application team that data's will not be available during this maintenance.

##########################

## Solution
##########################

File Name and Tablespace Name where Datafiles are Created.

set lines 200
col file_name for a60
col "size MB" for 9,99,999
col tablespace_name for a20

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where file_id=69;

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='TBS_NEW';

TABLESPACE_NAME         FILE_ID FILE_NAME                                                    Size in GB AUT STATUS     MAx in GB
-------------------- ---------- ------------------------------------------------------------ ---------- --- --------- ----------
TBS_NEW                   69 /u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF          1 YES AVAILABLE         32

Check Whether Diskgroup is having Sufficient Space

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';

NAME                             TOTAL_MB    FREE_MB

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

ORALIN_DATA                      2294348     495983

ORALIN_RECO                       184335     161864

Now Place the Tablespace OFFLINE.

SQL> alter tablespace TBS_NEW offline;

Tablespace altered.

Check Tablespace Status  

SQL> Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS
-------------------- ---------
TBS_NEW           OFFLINE


Copy the Datafile from File System to ASM using RMAN command.

Copy Datafile 69 to '+ORALIN_DATA';

ORALIN_1 @ host01:/u01/app/oracle/diag/rdbms/ORALIN/ORALIN_1/trace

> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Feb 4 00:03:30 2013

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

connected to target database: ORALIN (DBID=4269551271)

RMAN> copy datafile 69 to '+ORALIN_DATA';

Starting backup at 04-FEB-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4848 instance=ORALIN_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00069 name=/u01/app/oracle/product/11.2/db_5/dbs/TBS_NEW.DBF

output file name=+ORALIN_DATA/ORALIN/datafile/TBS_NEW.352.806457841 tag=TAG20130204T000400 RECID=68 STAMP=806457842

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

Finished backup at 04-FEB-13

Starting Control File and SPFILE Autobackup at 04-FEB-13

piece handle=+ORALIN_RECO/ORALIN_gvl/autobackup/2013_02_04/s_806457844.487.806457845 comment=NONE

Finished Control File and SPFILE Autobackup at 04-FEB-13

RMAN>

Now Switch the Datafile to Update this new location in controlfile.

switch datafile 69 to copy;

RMAN> switch datafile 69 to copy;
datafile 69 switched to datafile copy "+ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841"

Place Tablespace ONLINE.

SQL> alter tablespace TBS_NEW online;

Tablespace altered.

Check Tablespace Status  & Datafile Location :

SQL>  Select tablespace_name,status from dba_tablespaces where tablespace_name='TBS_NEW';

TABLESPACE_NAME      STATUS

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

TBS_NEW           ONLINE

SQL> Select file_id,file_name,status from dba_data_files where tablespace_name='TBS_NEW';

   FILE_ID FILE_NAME                                                    STATUS
---------- ------------------------------------------------------------ ---------
        69 +ORALIN_DATA/ORALIN_gvl/datafile/TBS_NEW.352.806457841  AVAILABLE