Re: restore controlfile from tag with no recovery catalog

From: max scalf <oracle.blog3_at_gmail.com>
Date: Wed, 4 Nov 2015 08:14:09 -0600
Message-ID: <CAKoJ+qBKOd6POzpUkP5cfsp6V5x6XSzw3X-kAa_PZ6OJcajowA_at_mail.gmail.com>

Here are the logs, as you can see i do not see anything that points to which one is my control file...

RMAN> RUN {
1> BACKUP database FILESPERSET 6 FORMAT '/Backup/host/SID/with_tag/%d_TAG_FILES_2015_11_04_07_58_%s_%p_%t_%U'

2>     tag 'TAG_FILES' keep until time 'sysdate+20';
3> # show all rman configuration settings
4>   show all;
5>   }

executing command: SET COMMAND ID

Starting backup at 04-nov-2015 07:58:19

using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=365 device type=DISK backup will be obsolete on date 14-dec-2015 07:58:19 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/oracle/SID/data2/sid_data02.dbf
input datafile file number=00007 name=/oracle/SID/data3/sid_data03.dbf
input datafile file number=00004 name=/oracle/SID/data1/sid_data01.dbf
input datafile file number=00003 name=/oracle/SID/data888/undotbs01.dbf
input datafile file number=00001 name=/oracle/SID/data777/system01.dbf
input datafile file number=00002 name=/oracle/SID/data777/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 04-nov-2015 07:58:20

channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00008 name=/oracle/SID/data2/sid_data04.dbf
input datafile file number=00009 name=/oracle/SID/data3/sid_data05.dbf
input datafile file number=00005 name=/oracle/SID/data1/users01.dbf
input datafile file number=00010 name=/oracle/SID/data2/sid_data06.dbf

channel ORA_DISK_2: starting piece 1 at 04-nov-2015 07:58:20 channel ORA_DISK_2: finished piece 1 at 04-nov-2015 07:58:23 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_81_1_894873500_2hqldbss_1_1 tag=TAG_FILES comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00011 name=/oracle/SID/data3/sid_data07.dbf channel ORA_DISK_2: starting piece 1 at 04-nov-2015 07:58:23 channel ORA_DISK_2: finished piece 1 at 04-nov-2015 07:58:26 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_82_1_894873503_2iqldbsv_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: finished piece 1 at 04-nov-2015 07:58:30 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_80_1_894873500_2gqldbss_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:10 current log archived

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 14-dec-2015 07:58:30 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=33 RECID=11 STAMP=894873154

channel ORA_DISK_1: starting piece 1 at 04-nov-2015 07:58:30
channel ORA_DISK_2: starting archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=34 RECID=12 STAMP=894873510 channel ORA_DISK_2: starting piece 1 at 04-nov-2015 07:58:30 channel ORA_DISK_1: finished piece 1 at 04-nov-2015 07:58:31 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_83_1_894873510_2jqldbt6_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_2: finished piece 1 at 04-nov-2015 07:58:31 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_84_1_894873510_2kqldbt6_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 14-dec-2015 07:58:31 archived logs required to recover from this backup will be backed up 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 04-nov-2015 07:58:31 channel ORA_DISK_1: finished piece 1 at 04-nov-2015 07:58:32 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_85_1_894873511_2lqldbt7_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
using channel ORA_DISK_2
backup will be obsolete on date 14-dec-2015 07:58:32 archived logs required to recover from this backup will be backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 04-nov-2015 07:58:34 channel ORA_DISK_1: finished piece 1 at 04-nov-2015 07:58:35 piece
handle=/Backup/host/SID/with_tag/SID_TAG_FILES_2015_11_04_07_58_86_1_894873513_2mqldbt9_1_1 tag=TAG_FILES comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 04-nov-2015 07:58:35

RMAN configuration parameters for database with db_unique_name SID are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/Backup/host/SID/database/%d_ctl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/Backup/host/SID/database/%d_%s_%p_%t_%U'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

On Wed, Nov 4, 2015 at 8:00 AM, Ahmed Aangour <ahmed.aangour_at_gmail.com> wrote:

> Hi max,
>
> Do you have the backup log? You will find in it the backup piece
> corresponding to your controlfile.
> Le 4 nov. 2015 2:56 PM, "max scalf" <oracle.blog3_at_gmail.com> a écrit :
>
>> Hi Hemanth,
>>
>> That is what i was going to do, but for whatever reason i cant find my
>> controlfile backup piece. I thought it would be in a format like below
>> from my autoback format(%d_ctl_%F')...
>>
>> SID_ctl_c-4868747647-20151103-08
>>
>> but all i see in my backup directory(speical backup directory) are in
>> this format "%d_TAG_NAME_%s_%U", so its hard to identify which one is my
>> control file backup peiece...
>>
>> On Tue, Nov 3, 2015 at 8:17 PM, Chitale, Hemant K <
>> Hemant-K.Chitale_at_sc.com> wrote:
>>
>>> You’d have to start with RESTORE CONTROLFILE FROM
>>> ‘<explicit_name_of_backuppiece_containing_the_controlfile>’
>>>
>>>
>>>
>>> If you don’t know which backup piece it is you could discern it from
>>> the piecename (for an autobackup) and size.
>>>
>>>
>>>
>>>
>>>
>>> Hemant K Chitale
>>>
>>>
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *max scalf
>>> *Sent:* Wednesday, November 04, 2015 7:06 AM
>>> *To:* Oracle Mailing List
>>> *Subject:* restore controlfile from tag with no recovery catalog
>>>
>>>
>>>
>>> Hello list,
>>>
>>>
>>>
>>> this is on 11.2.0.4 with "No Recovery catalog" in place. I used the
>>> below command to create a special backup, so that it is not deleted by
>>> delete backup script(as our retention policy is set to 7 days).
>>>
>>>
>>>
>>> BACKUP database FILESPERSET 1 FORMAT
>>> '/path/to/backup/file/%d_TAG_NAME_%s_%U' tag 'TAG_NAME' keep until time
>>> 'sysdate+50';
>>>
>>>
>>>
>>> Now I want to restore using this specific backup from this TAG. I know
>>> if i have recover catalog in place, i can connect to that, and do restore
>>> controlfile from tag='TAG_NAME' but how do i do the same when no recovery
>>> catalog.
>>>
>>>
>>>
>>> Lets says i lost everything(datafiles, redo logs, control files,
>>> spfile)...from what i under about the "keep until" clause it should
>>> encapsulate everything(datafile, controlfile, spfile if its in place)... so
>>> now if i want to restore, as my control file is gone, how do i restore from
>>> these tags ?
>>>
>>>
>>>
>>> I tried something like below but that does not seem to work...
>>>
>>>
>>>
>>> run {
>>>
>>> set controlfile autobackup format for device type disk to
>>> '/path/to/backup/file/%d_TAG_NAME_%s_%p_%t_%U';
>>>
>>> restore controlfile from autobackup validate;
>>>
>>> }
>>>
>>>
>>>
>>>
>>>
>>> run {
>>>
>>> set controlfile autobackup format for device type disk to
>>> '/path/to/backup/file/%d_TAG_NAME_%s_%p_%t_%U';
>>>
>>> restore controlfile from tag='TAG_NAME';
>>>
>>> }
>>>
>>>
>>>
>>> here are the RMAN config(show all) that i am using..
>>>
>>>
>>>
>>> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
>>>
>>> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
>>>
>>> CONFIGURE CONTROLFILE AUTOBACKUP ON;
>>>
>>> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
>>> '/path/to/backup/file/%d_ctl_%F';
>>>
>>> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
>>>
>>> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
>>> '/path/to/backup/file/%d_%s_%p_%t_%U';
>>>
>>>
>>>
>>> This email and any attachments are confidential and may also be
>>> privileged. If you are not the intended recipient, please delete all copies
>>> and notify the sender immediately. You may wish to refer to the
>>> incorporation details of Standard Chartered PLC, Standard Chartered Bank
>>> and their subsidiaries at
>>> https://www.sc.com/en/incorporation-details.html
>>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 04 2015 - 15:14:09 CET