Archive

Archive for February, 2010

Re-attach impdp job procedure

February 3, 2010 Leave a comment

1. Query datadump jobs

SQL>  select owner_name, state , JOB_NAME from DBA_DATAPUMP_JOBS ;

OWNER_NAME                     STATE                          JOB_NAME
—————————— —————————— ——————————
VI                             EXECUTING                      SYS_IMPORT_FULL_01
SYSTEM                         NOT RUNNING                    SYS_EXPORT_SCHEMA_01

2. Attach the job SYS_IMPORT_FULL_01

$ impdp vi/vi attach=SYS_IMPORT_FULL_01

Import: Release 10.2.0.3.0 – 64bit Production on Wednesday, 03 February, 2010 16:44:24

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

Job: SYS_IMPORT_FULL_01
Owner: VI
Operation: IMPORT
Creator Privs: FALSE
GUID: 7EAE2EB32B690359E0440000779B8480
Start Time: Wednesday, 03 February, 2010 15:29:44
Mode: FULL
Instance: viqa
Max Parallelism: 4
EXPORT Job Parameters:
CLIENT_COMMAND        vi/******** parfile=exp_vi_tab.par
IMPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        vi/******** parfile= imp_vi_tab.par
TABLE_EXISTS_ACTION   REPLACE
State: EXECUTING
Bytes Processed: 53,157,093,984
Percent Done: 99
Current Parallelism: 4
Job Error Count: 0
Dump File: /rcdb/oracle/bkup/dmp/VIM_TAB_2Geb2010.dmp

Worker 1 Status:
State: EXECUTING
Object Schema: VI
Object Name: PK_CP
Object Type: TABLE_EXPORT/TABLE/INDEX/INDEX
Worker Parallelism: 4

Worker 2 Status:
State: WORK WAITING

Worker 3 Status:
State: WORK WAITING

Worker 4 Status:
State: WORK WAITING

Advertisements
Categories: Administration

Fix mismatched backup pieces at RMAN

February 1, 2010 Leave a comment

Symptoms

RMAN-06207: WARNING: 119 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: ————— —————————————————
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_611_1_708325704.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_612_1_708325913.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_ctl_c-3621525582-20100115-00.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_627_1_708411652.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_628_1_708411719.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_629_1_708411786.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_630_1_708411852.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_631_1_708411918.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_632_1_708411985.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_633_1_708412041.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_634_1_708412098.dat
RMAN-06214: Backup Piece    /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_635_1_708412109.dat

Fixed Procedure

$ env|grep SID
ORACLE_SID=cviqa1
$ ORACLE_SID=sgptqa1;export ORACLE_SID
$ rman target /

Recovery Manager: Release 11.1.0.7.0 – Production on Mon Feb 1 12:33:04 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: SGPTQA (DBID=3621525582)

RMAN> crosscheck backup;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 instance=sgptqa1 device type=DISK
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_611_1_708325704.dat RECID=611 STAMP=708325707
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_612_1_708325913.dat RECID=612 STAMP=708325913
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_ctl_c-3621525582-20100115-00.dat RECID=613 STAMP=708325915
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_627_1_708411652.dat RECID=627 STAMP=708411653
crosschecked backup piece: found to be ‘EXPIRED’
backup piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_628_1_708411719.dat RECID=628 STAMP=708411720
crosschecked backup piece: found to be ‘EXPIRED’

Categories: TroubleShooting

Procedure to Fix ASM Disk Full caused by number of archived logs

February 1, 2010 Leave a comment

– Suppose the ASM diskgroup and database are SG_DG and sgcmsqa respectively

1. Start ASMCMD

export ORACLE_SID=+ASM1;export ORACLE
asmcmd

2. Change to ARCHIVELOG ( suppose the ASM diskgroup and database are SG_DG and sgcmsqa respectively)

ASMCMD> cd +SG_DG/SGCMSQA/ARCHIVELOG

ASMCMD> ls -la
Type  Redund  Striped  Time             Sys  Name
Y    2010_01_30/
Y    2010_01_31/
Y    2010_02_01/

3. Delete archived logs under 2010_01_30/

ASMCMD> cd 2010_01_30/
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y

ASMCMD> pwd
+SG_DG/SGCMSQA/ARCHIVELOG/2010_01_30
ASMCMD> ls
ASMCMD-08002: entry ‘2010_01_30’ does not exist in directory ‘+SG_DG/SGCMSQA/ARCHIVELOG/’

ASMCMD> cd +SG_DG/SGCMSQA/ARCHIVELOG
ASMCMD> ls
2010_01_31/
2010_02_01/

4. Perform archived log crsscheck by RMAN, otherwise the next RMAN backup fails with following errors

Starting backup at 01-FEB-10
current log archived
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/01/2010 10:41:43
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file +SG_DG/sgcmsqa/archivelog/2010_01_30/thread_1_seq_9177.5387.709605763
ORA-17503: ksfdopn:2 Failed to open file +SG_DG/sgcmsqa/archivelog/2010_01_30/thread_1_seq_9177.5387.709605763
ORA-15012: ASM file ‘+SG_DG/sgcmsqa/archivelog/2010_01_30/thread_1_seq_9177.5387.709605763′ does not exist

$ ORACLE_SID=sgcmsqa1;export ORACLE_SID
$ rman target /

Recovery Manager: Release 11.1.0.7.0 – Production on Mon Feb 1 10:46:41 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: SGCMSQA (DBID=2240214422)
RMAN> change archivelog all crosscheck;

5. Perform a full database up immediately

RMAN> run{
2> allocate channel ch1 type disk;
3> sql ‘alter system archive log current’;
4> backup
5> format ‘/bkuppool/oracle/bkup/databases/sgcmsqa/bkup_%d_%s_%p_%t.dat’
6> database plus archivelog;
7> delete obsolete;
8> }
9>



RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

February 1, 2010 Leave a comment

Symptoms

Starting backup at 01-FEB-10
current log archived
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/01/2010 09:45:23
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file +SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185
ORA-17503: ksfdopn:2 Failed to open file +SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185
ORA-15012: ASM file ‘+SG_DG/sgptqa/archivelog/2010_01_31/thread_1_seq_8972.6485.709712185’ does not exist

Fixed

1. Perform a crosscheck, run the following command from within RMAN:

RMAN> change archivelog all crosscheck;

2. Perform a full backup of the database at this point.

RMAN> run{
2> allocate channel ch1 type disk;
3> sql ‘alter system archive log current’;
4> backup
5> format ‘/bkuppool/oracle/bkup/databases/sgptqa/bkup_%d_%s_%p_%t.dat’
6> database plus archivelog;
7> delete obsolete;
8> }
9>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=257 instance=sgptqa1 device type=DISK

sql statement: alter system archive log current

Categories: TroubleShooting