ORA-04063: package body "SYS.KUPW$WORKER" has errors

Import: Release 10.2.0.4.0 - 64bit Production on Sunday, 25 October, 2009 21:11:16

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-04063: package body "SYS.KUPW$WORKER" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPW$WORKER"

ORA-39097: Data Pump job encountered unexpected error -6508


Login as SYS and follow the steps


SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS
2 AS SELECT * FROM sys.ku_noexp_view
3 /

Table created.

SQL> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC
2 /

Grant succeeded.

SQL> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC
2 /

Grant succeeded.

SQL> alter package KUPW$WORKER compile BODY;

Package body altered.

Recover the complete databse onto new server

Make the backups available on the new server

export ORACLE_SID

nykrs5033:/apps/oracle> rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Oct 20 10:28:44 2009

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

connected to target database (not started)

RMAN> set dbid 3090674905

executing command: SET DBID

-------------------------------------------------
This will start a dummy instance

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/apps/oracle/BSTUSPRD/1020/dbs/initBSTUSPRD.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2071296 bytes
Variable Size 67110144 bytes
Database Buffers 83886080 bytes
Redo Buffers 6316032 bytes


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

RMAN> restore spfile to pfile '/tmp/initBSTUSPRD.ora' from '/data/oracle/backup/BSTUSPRD/c-3090674905-20091015-00';

Starting restore at 20-OCT-09
using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: /data/oracle/backup/BSTUSPRD/c-3090674905-20091015-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-OCT-09

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

If required change the file system locations in init.ora and then restart the instance in nomount. Then restore the controlfile

RMAN> restore controlfile from '/data/oracle/backup/BSTUSPRD/c-3090674905-20091015-00';

Starting restore at 20-OCT-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/apps/oracle/BSTUSPRD/oradata/control01.ctl
output filename=/apps/oracle/BSTUSPRD/oradata/control02.ctl
output filename=/apps/oracle/BSTUSPRD/oradata/control03.ctl
Finished restore at 20-OCT-09


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

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1


-------------------------------------------------
RMAN> restore database;

Starting restore at 20-OCT-09
Starting implicit crosscheck backup at 20-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 20-OCT-09

Starting implicit crosscheck copy at 20-OCT-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-OCT-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /data/oracle/backup/BSTUSPRD/backupset/2009_10_15/c-3090674905-20091015-00
File Name: /data/oracle/backup/BSTUSPRD/c-3090674905-20091015-00

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /data/oracle/data/BSTUSPRD/system01.dbf
restoring datafile 00002 to /data/oracle/data/BSTUSPRD/undotbs01.dbf
restoring datafile 00003 to /data/oracle/data/BSTUSPRD/sysaux01.dbf
restoring datafile 00004 to /data/oracle/data/BSTUSPRD/users01.dbf
restoring datafile 00005 to /data/oracle/data/BSTUSPRD/BSTUSPRD_DATA01.dbf
restoring datafile 00006 to /data/oracle/data/BSTUSPRD/STARBASE_DATA01.dbf
channel ORA_DISK_1: reading from backup piece /data/oracle/backup/BSTUSPRD/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T020008_5ffgm9v9_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/data/oracle/backup/BSTUSPRD/backupset/2009_10_15/o1_mf_nnndf_TAG20091015T020008_5ffgm9v9_.bkp tag=TAG20091015T020008
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 20-OCT-09

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

RMAN> recover database ;

Starting recover at 20-OCT-09
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=325
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/20/2009 11:08:57
RMAN-06054: media recovery requesting unknown log: thread 1 seq 325 lowscn 6062341

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN> exit


Recovery Manager complete.

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

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> create spfile from pfile;

File created.

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 2074768 bytes
Variable Size 369100656 bytes
Database Buffers 234881024 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 BSTUSPRD
nykrs5033
10.2.0.3.0 20-OCT-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> exit

How to move ASM file from one diskgroup to another in STANDBY database

[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:46:04 2009

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

connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog

RMAN> backup as copy datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' format '+SHARED_FRA_DG01';

Starting backup at 05-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=620 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=619 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00021 name=+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277
output filename=+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855 tag=TAG20091005T204734 recid=41 stamp=699482959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
Finished backup at 05-OCT-09

Starting Control File and SPFILE Autobackup at 05-OCT-09
piece handle=/ora01/orabkup/P477/rman/cf_c-3052489846-20091005-00.bak comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-09


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' offline drop;

Database altered.

SQL> exit
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:57:39 2009

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

connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog

RMAN> SWITCH DATAFILE "+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277" to copy;

datafile 21 switched to datafile copy "+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855"

RMAN> exit


Recovery Manager complete.
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> s

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 5 20:58:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


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


SQL> alter database datafile '+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855' online;

Database altered.

SQL> select name from v$datafile where name like '%tigerdata01%' order by creation_time;

NAME
--------------------------------------------------------------------------------
+SHARED_DATA_DG01/c477/datafile/tigerdata01.263.697412327
+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855

SQL>
SQL>
SQL> recover standby database;
ORA-00279: change 48204120380 generated at 10/05/2009 16:43:26 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204120380 for thread 1 is in sequence #8107


Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 48204122646 generated at 10/05/2009 17:31:03 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204122646 for thread 1 is in sequence #8108
ORA-00278: log file
'+SHARED_AUXFILES_DG01/c477/archivelog/2009_10_05/thread_1_seq_8107.552.69947108
9' no longer needed for this recovery


SQL> alter system set standby_file_management=auto;

System altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

Connect to ASM Instance

SQL> alter diskgroup SHARED_DATA_DG01 drop file '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277';

Diskgroup altered.