How to Unregister the database from RMAN catalog

Login as rman catalog owner.

SQLPLUS> select * from rc_database where dbid = DBID;

SQLPLUS> exec dbms_rcvcat.unregisterdatabase(DBKEY, DBID);

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.

How to drop corrupt or missing logfiles

When logfiles are not present in the actual locations. may have been dropped by accident.

16:33:41 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log'

I tried to add a new one, but it failed as controlfile had the information. Logs were UNUSED in v$log

Elapsed: 00:00:00.68
16:33:49 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;
alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' - file already part of database


UNUSED logfiles can be dropped straight

Elapsed: 00:00:00.10
16:34:24 SYS@eqdd011g SQL> alter database drop logfile group 1;

Database altered.

Elapsed: 00:00:00.38

Then added those files again

16:34:41 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;

Database altered.

Elapsed: 00:00:00.66

One more attempt to open the database
16:34:46 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log'


Elapsed: 00:00:00.13

2nd loggroup this time

16:34:49 SYS@eqdd011g SQL> alter database drop logfile group 2;

Database altered.

Elapsed: 00:00:00.33
16:34:58 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log' size 5m;

Database altered.

Elapsed: 00:00:00.55
16:35:09 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17

Log was current and unable to drop, it was not even archived since it was current

16:35:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance eqdd011g (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.05
16:35:20 SYS@eqdd011g SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


Elapsed: 00:00:00.02

16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;

Database altered.

16:39:11 SYS@eqdd011g SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------ ------------- --------------------
2 1 0 5242880 1 YES UNUSED 0
1 1 91 5242880 1 NO CURREN 3220382 24-SEP-2009 16:38:10

2 rows selected.

Elapsed: 00:00:00.06
16:39:28 SYS@eqdd011g SQL> alter database open;

Database altered.

MMON encountered error 959, clearing the error

Poor mmon !!! is looking for a nonexistent TS and alerting for a threshold. God save him.

This is what is happening.... 959 error is legitimate and may be due to a BUG. DB restart will not take the error away. Error is coming every 10 mins since 2008 in MMON trace file.


Detailed Analysis:

Find out MMON spid. That is available in MMON trace file in bdump

Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)


-- Attach this SPID above

09:07:37 SYS@gptprd1 SQL> oradebug setospid 25361;
Oracle pid: 16, Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)

09:08:02 SYS@gptprd1 SQL> oradebug unlimit
Statement processed.

09:09:49 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack level 3
Statement processed.


-- From MMON trace file in background_dump_dest
Received ORADEBUG command 'unlimit' from process Unix process pid: 12666, image:
*** 2009-09-01 09:10:17.882
Received ORADEBUG command 'Event 959 trace name errorstack level 3' from process Unix process pid: 12666, image:


-- WAIT UP TO 10 MINS FOR ERROR TO BE GENERATED
-- Then ... From MMON trace file in background_dump_dest

*** 2009-09-01 09:15:14.758
ksedmp: internal or fatal error
ORA-00959: tablespace 'STREAMS_TBS' does not exist
----- Call Stack Trace -----


SQL> -- Disable trace

09:10:17 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack off
Statement processed.


-- From MMON trace file in background_dump_dest

*** 2009-09-01 09:19:37.882
Received ORADEBUG command 'Event 959 trace name errorstack off' from process Unix process pid: 12666, image:


MMON is looking for ts STREAMS_TBS which does not exist. It fetches it from ts$ table.

09:22:11 SYS@gptprd1 SQL> select * from dba_tablespaces where tablespace_name = 'STREAMS_TBS';

no rows selected

Elapsed: 00:00:00.01
09:22:30 SYS@gptprd1 SQL> select * from ts$ where name = 'STREAMS_TBS';

TS# NAME OWNER# ONLINE$ CONTENTS$ UNDOFILE# UNDOBLOCK# BLOCKSIZE INC# SCNWRP SCNBAS DFLMINEXT DFLMAXEXT DFLINIT DFLINCR DFLMINLEN
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DFLEXTPCT DFLOGGING AFFSTRENGTH BITMAPPED PLUGGED DIRECTALLOWED FLAGS PITRSCNWRP PITRSCNBAS OWNERINSTANCE BACKUPOWNER GROUPNAME
---------- ---------- ----------- ---------- ---------- ------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
SPARE1 SPARE2
---------- ----------
SPARE3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE4
--------------------
14 STREAMS_TBS 0 3 0 0 0 8192 2 0 0 1 2147483645 8 128 8
0 1 0 8 0 1 33 6 3112294409
0 0



Workaround:
1. Lets create a tablespace STREAMS_TBS again, remove the thresholds manually for that TS and then drop it.

Solution
1. Contact Oracle with this analysis and check if its a BUG (6922680) that ts is removed and still present in TS$

Clone Database in ASM Warning

While cloning database on a different server, we may get a warning when using ASM filesystem.

We should not set (dblog)_file_name_convert as Note:380449.1 says. According to Oracle,as long as diskgroup name is not changed between primary and standby (e.g. remains as DATA or FRA)and OMF is used, the duplicate command automatically creates path +diskgroup/db_unique_name, e.g.,+DATA/BOSTON. Furthermore, if OMF is used, control_files does not need to be specified in standbypfile or spfile; duplicate will figure out the path.

So make sure, you have correct db_unique_name in init.ora file and rman will take care of putting in the right location.

To migrate CONTROLFILE from cooked file system to ASM

Initial control file will be on filesystem

*.control_files='/oracle/product/10.2.0/db_2/dbs/C481.ctl'
#*.control_files='+SHARED_DATA_DG01'

"initC481.ora" 57 lines, 1776 characters

Database is mounted

[cltpbgscdb04:oracle]/oracle/product/10.2.0/db_2/dbs> s

17:05:47 SYS@C481 SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
17:05:55 SYS@C481 SQL> startup nomount
ORACLE instance started.

Total System Global Area 775946240 bytes
Fixed Size 2141696 bytes
Variable Size 249975296 bytes
Database Buffers 469762048 bytes
Redo Buffers 54067200 bytes
17:06:01 SYS@C481 SQL> alter database mount standby database;

Database altered.

Elapsed: 00:00:04.37

Connect to RMAN and copy controlfile to ASM

[cltpbgscdb04:oracle]/oracle/product/10.2.0/db_2/dbs> rman target / catalog rmanadm1/rmanadm1@infra

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 17 17:06:30 2009

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

connected to target database: P481 (DBID=2656166842, not open)
connected to recovery catalog database

RMAN> copy current controlfile to '+SHARED_DATA_DG01/c481/controlfile/control01.ctl';

Starting backup at 17-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=323 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=654 devtype=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+SHARED_DATA_DG01/c481/controlfile/control01.ctl tag=TAG20090817T170830 recid=88 stamp=695149715
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 17-AUG-09

Starting Control File Autobackup at 17-AUG-09
piece handle=/ora01/orabkup/P481/rman/cf_c-2656166842-20090817-06.bak comment=NONE
Finished Control File Autobackup at 17-AUG-09

RMAN> exit


Recovery Manager complete.

Shutdown the database, modify the controlfile to new file in init.ora or spfile

17:08:56 SYS@C481 SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

#*.control_files='/oracle/product/10.2.0/db_2/dbs/C481.ctl'
*.control_files='+SHARED_DATA_DG01/c481/controlfile/control01.ctl'

"initC481.ora" 57 lines, 1807 characters

Startup the database

17:09:57 @ SQL> startup nomount
ORACLE instance started.

Total System Global Area 775946240 bytes
Fixed Size 2141696 bytes
Variable Size 249975296 bytes
Database Buffers 469762048 bytes
Redo Buffers 54067200 bytes
17:10:03 @ SQL> alter database mount standby database;

Database altered.

09:12:37 SYS@C481 SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ------------ ------------------------------
control_file_record_keep_time integer 7
control_files string +SHARED_DATA_DG01/c481/control
file/control01.ctl

Unable to start service

Synopsis of the issue was.
1. ges_taf service was unable to start on node2 and status UNKNOWN on node3
2. internal service ges_ges was writing message "ORA-44305: service ges_ges is running" in imon log every 10 minutes

[wbeqdrac08p:oracle]/u01/app/oracle/product/10.2.0/log/wbeqdrac08p/racg> tail -f imon_ges_ges.log
2009-04-29 11:13:04.626: [ RACG][1228056896] [28246][1228056896][ora.ges_ges.ges2.inst]: clsrcsnstartsrv: service name ges_ges already exists
2009-04-29 11:23:05.015: [ RACG][1228056896] [28246][1228056896][ora.ges_ges.ges2.inst]: clsrcsnstartsrv: service name ges_ges already exists
2009-04-29 11:23:05.015: [ RACG][1228056896] [28246][1228056896][ora.ges_ges.ges2.inst]: CLSR-0002: Oracle error encountered while executing clsrcsnstartsrv : execute2

2009-04-29 11:23:05.016: [ RACG][1228056896] [28246][1228056896][ora.ges_ges.ges2.inst]: ORA-44305: service ges_ges is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 444
ORA-06512: at "SYS.DBMS_SERVICE", line 365
ORA-06512: at line 1


Resolution
1. when service is added using srvctl, netmgr or any other tool, it gets added in spfile with service_names parameter. This is not required 10204 onwards, meaning parameter service_names to be kept NULL. I also verified this with Oracle consultant over the phone
2. If we keep service_names to NULL, some process (I believe either pmon or racg) updates the parameter every 10 mins with db_unique_name value but in CAPITAL. So case does matter. I set it as lower case and it stopped putting ORA-44305 messages in imon log files.

How to move a datafile to different diskgroup in ASM

rman target / nocatalog cmdfile=rman_move_ts.cmd

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 26 09:56:03 2009

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

connected to target database: GES (DBID=1576423539)
using target database control file instead of recovery catalog

RMAN> run {
2> allocate channel c1 device type disk;
3> backup as copy tablespace USERS format '+DATA01';
4> release channel c1;
5> }
6>
7> sql 'alter tablespace users offline';
8> switch tablespace users to copy;
9> recover tablespace users;
10> sql 'alter tablespace users online';
11>
allocated channel: c1
channel c1: sid=1070 instance=ges1 devtype=DISK

Starting backup at 26-MAR-09
channel c1: starting datafile copy
input datafile fno=00006 name=+SYS01/ges/datafile/users.265.675517679
output filename=+DATA01/ges_ges/datafile/users.260.682509367 tag=TAG20090326T095606 recid=20 stamp=682509394
channel c1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 26-MAR-09

Starting Control File and SPFILE Autobackup at 26-MAR-09
piece handle=/apps/warehouse2/rman/wbeqdrac07p_ges/cf_c-1576423539-20090326-00.bak comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-09

released channel: c1

sql statement: alter tablespace users offline

datafile 6 switched to datafile copy "+DATA01/ges_ges/datafile/users.260.682509367"

Starting recover at 26-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1070 instance=ges1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1065 instance=ges1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1052 instance=ges1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1049 instance=ges1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 5.5.1.0

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 26-MAR-09

sql statement: alter tablespace users online

Recovery Manager complete.

After this you may also need to delete the old file.

> asmcmd
ASMCMD [+] > cd SYS01/GES/DATAFILE
ASMCMD [+SYS01/GES/DATAFILE] > ls
SYSAUX.261.675517645
SYSTEM.259.675517625
UNDOTBS1.260.675517635
UNDOTBS2.263.675517655
UNDOTBS3.264.675517665
USERS.265.675517679
ASMCMD [+SYS01/GES/DATAFILE] > rm USERS.265.675517679

PRKP-1001 : Error starting instance

srvtl start instance or start database gives error, but sqlplus can start it without any errors.

srvctl start instance -d FDRPRD -i FDRPRD1
PRKP-1001 : Error starting instance fdrprd1 on node ls1orafdrr1sb
CRS-0215: Could not start resource 'ora.fdrprd.fdrprd1.inst'.

Issue was instance name in spfile and CRS was not having the same CASE

1) run crs_stat (this is customized script ) this should show the contents in lower case

HA Resource Target State
ora.fdrlup.db OFFLINE OFFLINE
ora.fdrlup.fdrlup1.inst ONLINE OFFLINE
ora.fdrlup.fdrlup2.inst ONLINE OFFLINE
ora.fdrlup.fdrlup3.inst ONLINE OFFLINE
ora.ls1orafdrr1sb.gsd ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr1sb.ons ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr1sb.vip ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr2sb.gsd ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr2sb.ons ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr2sb.vip ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr3sb.gsd ONLINE ONLINE on ls1orafdrr3sb
ora.ls1orafdrr3sb.ons ONLINE ONLINE on ls1orafdrr3sb
ora.ls1orafdrr3sb.vip ONLINE ONLINE on ls1orafdrr3sb


2) check the imon file for error
/opt/oracle/product/10.2.0/log/ls1orafdrr1sb/racg/imon_FDRPRD.log ,
you should an error similar like
SQL> ORA-01506: missing or illegal database name Disconnected


ora.FDRPRD.FDRPRD1.inst.log

Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle. All rights reserved.
2009-02-20 15:08:10.022: [ RACG][3086904064] [2840][3086904064][ora.default]: Cannot read string from /opt/oracle/product/10.2.0/racg/init/ls1orafdrr1sb/FDRPRD/.connect
2009-02-20 15:08:12.450: [ RACG][3086904064] [2840][3086904064][ora.FDRPRD.FDRPRD1.inst]: clsrcremaction: clsrcrpcreq message returned: 1 '
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Feb 20 15:08:11 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: Connected to an idle instance.
SQL> ORA-01506: missing or illegal database name
SQL> Disconnected


3) Remove the instance and database

srvctl remove instance -d FDRPRD -i FDRPRD1
Remove instance FDRPRD1 from the database FDRPRD? (y/[n]) y
srvctl remove instance -d FDRPRD -i FDRPRD2
Remove instance FDRPRD2 from the database FDRPRD? (y/[n]) y
srvctl remove instance -d FDRPRD -i FDRPRD3
[ls1orafdrr1sb:oracle]/opt/oracle/product/10.2.0/log/ls1orafdrr1sb/racg>

srvctl remove database -d FDRPRD
Remove the database FDRPRD? (y/[n]) y

4) Check the ocrdumpfile by running ocrdump command .You shouldn't see
any reference for the instance or db.


5) Add the instance and database


srvctl add database -d FDRPRD -o $ORACLE_HOME -p
/dev/raw/spfileFDRPRDRD -r PHYSICAL_STANDBY -s mount
[ls1orafdrr1sb:oracle]/opt/oracle/product/10.2.0/log/ls1orafdrr1sb/racg>

srvctl add instance -d FDRPRD -i FDRPRD3 -n ls1orafdrr3sb
[ls1orafdrr1sb:oracle]/opt/oracle/product/10.2.0/log/ls1orafdrr1sb/racg>

srvctl add instance -d FDRPRD -i FDRPRD2 -n ls1orafdrr2sb
[ls1orafdrr1sb:oracle]/opt/oracle/product/10.2.0/log/ls1orafdrr1sb/racg>

srvctl add instance -d FDRPRD -i FDRPRD1 -n ls1orafdrr1sb


6) recheck the ocrdumpfile by running ocrdump command and You should
see any reference for the instance or db.



7) rerun the crs_stat to check the contents


HA Resource Target State
----------- ------ -----
ora.FDRPRD.FDRPRD1.inst OFFLINE OFFLINE
ora.FDRPRD.FDRPRD2.inst OFFLINE OFFLINE
ora.FDRPRD.FDRPRD3.inst OFFLINE OFFLINE
ora.FDRPRD.db OFFLINE OFFLINE
ora.ls1orafdrr1sb.gsd ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr1sb.ons ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr1sb.vip ONLINE ONLINE on ls1orafdrr1sb
ora.ls1orafdrr2sb.gsd ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr2sb.ons ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr2sb.vip ONLINE ONLINE on ls1orafdrr2sb
ora.ls1orafdrr3sb.gsd ONLINE ONLINE on ls1orafdrr3sb
ora.ls1orafdrr3sb.ons ONLINE ONLINE on ls1orafdrr3sb
ora.ls1orafdrr3sb.vip ONLINE ONLINE on ls1orafdrr3sb


8) srvctl start database -d FDRPRD

How to drop Lobs

Lobs can be dropped using alter table command.
A particular column which is of LOB type, can be dropped using

ALTER TABLE DROP COLUMN ;

It can also be dropped using DROP TABLE command

10g onwards, if you drop a table, it goes to the recyclebin. So dropped lob objects are still visible.

I dropped all the objects using,

select 'DROP ' object_type ' ' owner '.' object_name ';'
from dba_objects
where owner in (upper('&UserName'))
and object_type in ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SEQUENCE', 'TRIGGER')
order by object_id desc;

But then, lobs were still present.


select count(1) from dba_objects where owner = 'RCUSER';
COUNT(1)
----------
84
1 row selected.

These were coming from recyclebin. So to clear the recycle bin for all users


SQL> purge dba_recyclebin;
DBA Recyclebin purged.

After this lobs were not found.


SQL> select count(1) from dba_objects where owner = 'RCUSER';
COUNT(1)
----------
0
1 row selected.

NFS and Oracle Backups

Oracle while taking backup using datapump or RMAN autobackup, checks for options set on the mount point if it is enaled for backup or not. If correct options are not set, it does not dump the backup file. Same script can backup on a cooked file system or even on NFS log file is written. Issue comes only in case of dump files.

Following error appears in case of incorrect mount point options.


In case of datapump

Datapump export unable to write to dump file NFS

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/u01/app/oracle/admin/ges/export/dmp/expdp.ges1.full.xrows.20090126.11244801.dm
p"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
In case of RMAN backup

RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 02/12/2009 13:41:26
ORA-19504: failed to create file "/apps/warehouse2/rman/wbeqdrac07p_ges/cf_c-1576423539-20090212-03.bak"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3



To correct the problem,
set the following options in /etc/fstab file and umount the mount point and again mount it.

rw,bg,nfsvers=3,tcp,timeo=600,rsize=32768,wsize=32768,hard,nolock,intr,noac,addr=10.110.116.243

Mandetory options are
hard, rsize>=32768 and wsize>=32768

noac is required on RAC servers.

Options can be checked using
mount -v

Also it is necessary that following services to be running on NFS server and NFS client



> service nfs status
rpc.mountd (pid 25815) is running...
nfsd (pid 25812 25811 25810 25809 25808 25807 25806 25805) is running...

> service portmap status
portmap (pid 16735) is running...

> service nfslock status
rpc.statd (pid 16774) is running...

> ps -efgrep rpc.rquotad
root 6593 5730 0 11:46 pts/0 00:00:00 grep rpc.rquotad
root 23692 1 0 11:26 ? 00:00:00 rpc.rquotad
root 23727 1 0 11:26 ? 00:00:00 rpc.rquotad
root 25801 1 0 11:31 ? 00:00:00 rpc.rquotad


After these options are set, backup was successful for RMAN as well as datapump

Resolving GAP for Standby

Missing Archives and Standby Recovery

Introduction

Sometimes it is required to ship the archivelogs from one server to another. It becomes difficult when archivelogs are sitting on ASM file system. ASM files can not be directly accessed. RMAN can come to the rescue in this.

I had an issue of missing archives on STANDBY server (Log GAP) and somehow those archives got backedup to tape and mrp could not access it. Now I had to restore it and then get it shipped to STANDBY server. Since the GAP was too big, I decided to restore it and manually ship it rather than MRP to handle it. This is the process I followed. I am sure there may be many ways. But this one worked for me.
Audience
This document can be used in case of standby recovery because of missing logs on ASM. It is intended for DBAs

Approach
" I identified log GAP using query on Standby database

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
FROM V$LOG_HISTORY
GROUP BY THREAD#
/

" Then copy/restore the ASM logs to the cooked file system.

RMAN> run {
allocate channel c2 device type 'sbt_tape';
set archivelog destination to '/u01/app/oracle/admin/infra/arch';
restore archivelog from sequence 8852 until sequence 8856 thread 2;
release channel c2;
}
2> 3> 4> 5> 6>
allocated channel: c2
channel c2: sid=2228 instance=infra1 devtype=SBT_TAPE
channel c2: Data Protection for Oracle: version 5.3.3.0
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 22-JAN-09
archive log thread 2 sequence 8856 is already on disk as file /u01/app/oracle/admin/infra/arch/infra_2_8856_641059495.arc
channel c2: starting archive log restore to user-specified destination
archive log destination=/u01/app/oracle/admin/infra/arch
channel c2: restoring archive log
archive log thread=2 sequence=8852
channel c2: restoring archive log
archive log thread=2 sequence=8853
channel c2: restoring archive log
archive log thread=2 sequence=8854
channel c2: restoring archive log
archive log thread=2 sequence=8855
channel c2: reading from backup piece qek5f68i_1_1
channel c2: restored backup piece 1
piece handle=qek5f68i_1_1 tag=TAG20090122T161057
channel c2: restore complete, elapsed time: 00:00:55
Finished restore at 22-JAN-09
released channel: c2
RMAN> exit

" Once the logs are restored, scp or sftp it to the standby destination. This will again be a cooked file system
# scp infra_2_*.arc <stby serverName>:/tmp
infra_2_8853_641059495.arc 100% 131MB 11.0MB/s 00:12
infra_2_8854_641059495.arc 100% 267MB 12.2MB/s 00:22
infra_2_8855_641059495.arc 100% 60MB 8.5MB/s 00:07
infra_2_8856_641059495.arc 100% 542KB 541.5KB/s 00:00
" Now if standby database log recovery destination is ASM file system or different than the scp destination, manually recover the database. Or at the recovery prompt manually feed the file name with a full path.
SQL> alter database recover managed standby database cancel;
SQL> recover from '/tmp' standby database;
" Start the managed recovery
SQL> alter database recover managed standby database disconnect from session;
" Verify the log shipping and log apply and That's it

Database Growth Monitor

The following package monitors the growth for the database. It can be used for Capacity Planning.

1. Create the necessary tables

CREATE GLOBAL TEMPORARY TABLE SEGMENT_START_INFO AS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, BYTES START_SIZE, SYSDATE START_DATE
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE GLOBAL TEMPORARY TABLE SEGMENT_CURRENT_INFO AS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, BYTES CURRENT_SIZE, SYSDATE CURRENT_DATE
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE TABLE GROWTH_METRIC_LOG(STAGE VARCHAR2(255), RUN_DATE DATE) TABLESPACE USERS;
CREATE PUBLIC SYNONYM GROWTH_METRIC_LOG FOR GROWTH_METRIC_LOG;
GRANT SELECT ON GROWTH_METRIC_LOG TO PUBLIC;
CREATE TABLE SEGMENT_GROWTH_METRICS
(
OWNER VARCHAR2(30 BYTE),
SEGMENT_NAME VARCHAR2(81 BYTE),
PARTITION_NAME VARCHAR2(30 BYTE),
SEGMENT_TYPE VARCHAR2(18 BYTE),
BYTES NUMBER,
EXTENTS NUMBER,
ADD_DATE DATE,
DEL_DATE DATE
)
/
CREATE TABLE TABLESPACE_GROWTH_METRICS AS SELECT SYSDATE RUN_DATE, TABLESPACE_NAME, BYTES DAILY_AVG_GROWTH_MB
FROM DBA_SEGMENTS WHERE 1=2
/
CREATE PUBLIC SYNONYM TABLESPACE_GROWTH_METRICS FOR TABLESPACE_GROWTH_METRICS
/
GRANT SELECT ON TABLESPACE_GROWTH_METRICS TO PUBLIC
/

2. Create the Package which will monitor the growth


GRANT SELECT ON DBA_SEGMENTS TO OEM;
--@CREATE_TABS.SQL
CREATE OR REPLACE PACKAGE PKG_DATABASE_GROWTH_METRIC IS
PROCEDURE SP_DATABASE_GROWTH_METRIC;
PROCEDURE SP_CAPTURE_SEGMENT_GROWTH;
PROCEDURE SP_ANALYZE_SEGMENT_GROWTH;
END PKG_DATABASE_GROWTH_METRIC;
/

CREATE OR REPLACE PACKAGE BODY PKG_DATABASE_GROWTH_METRIC IS
/***********************************************************************
**CREATED BY: MAKARAND G
**CREATE DATE: 10/28/2004
**OVERVIEW: RUNS ALL OTHER PROCEDURES
**VERSION 01.01.00
***********************************************************************/
PROCEDURE SP_DATABASE_GROWTH_METRIC IS
BEGIN
INSERT INTO GROWTH_METRIC_LOG VALUES ('START SP_CAPTURE_SEGMENT_GROWTH', SYSDATE);
COMMIT;
SP_CAPTURE_SEGMENT_GROWTH;
INSERT INTO GROWTH_METRIC_LOG VALUES ('END SP_CAPTURE_SEGMENT_GROWTH', SYSDATE);
COMMIT;
END SP_DATABASE_GROWTH_METRIC;
PROCEDURE SP_ANALYZE_SEGMENT_GROWTH IS
BEGIN
--FIND OUT THE CURRENT SIZE FOR EACH OBJECT
INSERT INTO SEGMENT_CURRENT_INFO (OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, CURRENT_SIZE, CURRENT_DATE)
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, SUM(BYTES) CURRENT_SIZE, MAX(ADD_DATE) CURRENT_DATE
FROM SEGMENT_GROWTH_METRICS A
WHERE DEL_DATE IS NULL
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME;
--FIND OUT THE BASE SIZE FOR EACH OBJECT
INSERT INTO SEGMENT_START_INFO (OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, START_SIZE, START_DATE)
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, SUM(BYTES) START_SIZE, MIN(ADD_DATE) START_DATE
FROM SEGMENT_GROWTH_METRICS A
WHERE DEL_DATE IS NULL
AND ADD_DATE = (
SELECT MIN(ADD_DATE) FROM SEGMENT_GROWTH_METRICS B
WHERE DEL_DATE IS NULL
AND A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**'))
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME;

INSERT INTO TABLESPACE_GROWTH_METRICS
--FIND OUT TABLESPACE DAILY AVG GROWTH, WHICH WOULD COME FROM THE INDIVIDUAL SEGMENTS
SELECT SYSDATE, TABLESPACE_NAME, ROUND(SUM(DAILY_AVG_GROWTH)/1024/1024) DAILY_AVG_GROWTH_MB FROM (
SELECT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.PARTITION_NAME, C.TABLESPACE_NAME, (B.CURRENT_SIZE - A.START_SIZE)/ROUND(B.CURRENT_DATE-A.START_DATE) DAILY_AVG_GROWTH
FROM SEGMENT_START_INFO A, SEGMENT_CURRENT_INFO B, TEMP_DBA_SEGMENTS C
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND A.OWNER = C.OWNER
AND A.SEGMENT_NAME = C.SEGMENT_NAME
AND A.SEGMENT_TYPE = C.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(C.PARTITION_NAME, '**')
AND ROUND(B.CURRENT_DATE-A.START_DATE) > 0)
GROUP BY TABLESPACE_NAME
HAVING ROUND(SUM(DAILY_AVG_GROWTH)/1024/1024) > 0
ORDER BY TABLESPACE_NAME;
COMMIT;
END SP_ANALYZE_SEGMENT_GROWTH;
PROCEDURE SP_CAPTURE_SEGMENT_GROWTH IS
CURSOR C1 IS
SELECT A.OWNER, A.SEGMENT_NAME,
A.PARTITION_NAME,
A. SEGMENT_TYPE
FROM TEMP_DBA_SEGMENTS A, (
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
SUM(BYTES) PREV_SEG_BYTES,
SUM(EXTENTS) PREV_SEG_EXTS
FROM SEGMENT_GROWTH_METRICS
WHERE DEL_DATE IS NULL
GROUP BY OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE) B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND (A.EXTENTS < B.PREV_SEG_EXTS OR A.BYTES < B.PREV_SEG_BYTES);
CURSOR C2 IS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL
MINUS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM TEMP_DBA_SEGMENTS ;


BEGIN
/* CREATE A SNAPSHOT OF DBA_SEGMENTS, WHICH WILL IMPROVE THE PROCESSING TIME, ALSO IT CAN BE USED FOR BACKTRACKING TILL NEXT RUN
*/
EXECUTE IMMEDIATE 'DROP TABLE TEMP_DBA_SEGMENTS';
EXECUTE IMMEDIATE 'CREATE TABLE TEMP_DBA_SEGMENTS PARALLEL AS SELECT * FROM DBA_SEGMENTS';
EXECUTE IMMEDIATE 'ANALYZE TABLE TEMP_DBA_SEGMENTS ESTIMATE STATISTICS';
EXECUTE IMMEDIATE 'ANALYZE TABLE SEGMENT_GROWTH_METRICS ESTIMATE STATISTICS';
/* FIRST FOR ALL THE OBJECTS WHICH ARE SHRUNK DUE THE REBUILD OR MOVE OR ANY OTHER ACTION SOFT DELETE THEM
** THESE OBJECTS WILL AGAIN BE INSERTED AT A LATER STAGE
*/
FOR X IN C1
LOOP
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE A.OWNER = X.OWNER
AND A.SEGMENT_NAME = X.SEGMENT_NAME
AND NVL(A.PARTITION_NAME,'**') = NVL(X.PARTITION_NAME, '**')
AND A.SEGMENT_TYPE = X.SEGMENT_TYPE
AND DEL_DATE IS NULL;
END LOOP;
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP1:TOTAL4', SYSDATE);
COMMIT;
/* INSERT OBJECTS WHICH ARE NEW/WHO ARE NOT MARKED FOR SOFT DELETE.
** THESE WERE NEVER FOUND IN GROWTH METRIC SO FAR OR SOFT DELETED EARLIER
*/
INSERT INTO SEGMENT_GROWTH_METRICS
SELECT OWNER ,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
BYTES,
EXTENTS,
SYSDATE ADD_DATE,
NULL
FROM TEMP_DBA_SEGMENTS A
WHERE NOT EXISTS (SELECT 1 FROM SEGMENT_GROWTH_METRICS B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND B.DEL_DATE IS NULL)
AND SEGMENT_TYPE NOT LIKE '%UNDO%'
AND SEGMENT_TYPE != 'TEMPORARY';
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP2:TOTAL4', SYSDATE);
COMMIT;
/* INSERT THE EXISTING OBJECTS GROWTH. WE ONLY CONSIDER THE # OF EXTENTS AND BYTES ADDED SINCE THE LAST TIME AND CONSIDERS IT AS A GROWTH
*/
INSERT INTO SEGMENT_GROWTH_METRICS
SELECT A.OWNER,
A.SEGMENT_NAME,
A.PARTITION_NAME,
A.SEGMENT_TYPE,
A.BYTES - B.PREV_SEG_BYTES BYTES_ADDED,
A.EXTENTS - B.PREV_SEG_EXTS EXTENTS_ADDED,
SYSDATE ADD_DATE,
NULL DEL_DATE
FROM TEMP_DBA_SEGMENTS A, (
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
SUM(BYTES) PREV_SEG_BYTES,
SUM(EXTENTS) PREV_SEG_EXTS
FROM SEGMENT_GROWTH_METRICS
WHERE DEL_DATE IS NULL
GROUP BY OWNER,
SEGMENT_NAME,
PARTITION_NAME,
SEGMENT_TYPE) B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND (A.EXTENTS > B.PREV_SEG_EXTS OR A.BYTES > B.PREV_SEG_BYTES);
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP3:TOTAL4', SYSDATE);
COMMIT;
/* THESE ARE THE OBJECTS WHICH WERE PRESENT LAST TIME BUT DROPPED NOW, SO NOT PART OF GROWTH ANYMORE
*/
/*
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE NOT EXISTS (SELECT 1 FROM TEMP_DBA_SEGMENTS B
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**'))
AND DEL_DATE IS NULL;
*/
FOR B IN C2
LOOP
UPDATE SEGMENT_GROWTH_METRICS A
SET DEL_DATE = SYSDATE
WHERE A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.SEGMENT_NAME
AND A.SEGMENT_TYPE = B.SEGMENT_TYPE
AND NVL(A.PARTITION_NAME, '**') = NVL(B.PARTITION_NAME, '**')
AND DEL_DATE IS NULL;
END LOOP;
INSERT INTO GROWTH_METRIC_LOG VALUES ('STEP4:TOTAL4', SYSDATE);
COMMIT;
END SP_CAPTURE_SEGMENT_GROWTH;

END PKG_DATABASE_GROWTH_METRIC;
/
GRANT EXECUTE ON PKG_DATABASE_GROWTH_METRIC TO PUBLIC
/
CREATE PUBLIC SYNONYM PKG_DATABASE_GROWTH_METRIC FOR PKG_DATABASE_GROWTH_METRIC
/

3. Schedule a growth monitoring script by calling

exec PKG_DATABASE_GROWTH_METRIC.SP_DATABASE_GROWTH_METRIC;

4. Analyze the growth on-demand

exec PKG_DATABASE_GROWTH_METRIC.SP_ANALYZE_SEGMENT_GROWTH;

5. Interpret the result


SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_SPACE) TOTAL_SPACE_MB, ROUND(SUM(B.BYTES)/1024/1024) FREE_SPACE_MB, ROUND((SUM(B.BYTES)/(1024*1024)/A.TOTAL_SPACE)*100,2) PERCENT_FREE, C.DAILY_AVG_GROWTH_MB, ROUND(SUM(B.BYTES)/1024/1024/C.DAILY_AVG_GROWTH_MB) "#OF DAYS TO FAIL"
FROM (
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
DBA_FREE_SPACE B, TABLESPACE_GROWTH_METRICS C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
AND C.RUN_DATE = (SELECT MAX(RUN_DATE) FROM TABLESPACE_GROWTH_METRICS)
GROUP BY A.TABLESPACE_NAME, A.TOTAL_SPACE, C.DAILY_AVG_GROWTH_MB
ORDER BY 6
/

6. Additional queries to troubleshoot


SELECT EXECUTIONS FROM V$SQLAREA WHERE SQL_TEXT LIKE 'UPDATE SEGMENT_GROWTH_METRICS%'
SELECT COUNT(1) FROM (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL
MINUS
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME FROM TEMP_DBA_SEGMENTS)


SELECT COUNT(1),SUM(BYTES)/1024/1024 DB_SIZE FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL;

SELECT COUNT(1),SUM(BYTES)/1024/1024 DB_SIZE FROM TEMP_DBA_SEGMENTS WHERE SEGMENT_TYPE NOT LIKE '%UNDO%' AND SEGMENT_TYPE != 'TEMPORARY' ;

--PROBLEM SEGMENTS
SELECT SEGMENT_NAME, SUM(BYTES) FROM SEGMENT_GROWTH_METRICS WHERE DEL_DATE IS NULL GROUP BY SEGMENT_NAME
MINUS
SELECT SEGMENT_NAME, SUM(BYTES) FROM TEMP_DBA_SEGMENTS WHERE SEGMENT_TYPE NOT LIKE '%UNDO%' GROUP BY SEGMENT_NAME
/

Copy Tablespace to ASM

In order to copy a tablespace to ASM diskgroup, RMAN is the only utility that can be used. Here is an example on how to do it.

--Take the tablespace offline
sql 'alter tablespace name offline';

--Back it up as copy onto ASM diskgroup. This creates an image copy
backup as copy tablespace name format '+DATA02';

--Now just tell the controlfile that you will use new copy as your tablespace
switch tablespace name to copy;

--Bring the tablespace back online
sql 'alter tablespace name online';


Same can be done for datafiles as well. Also this can be used to copy or move a tablespace or datafile from one diskgroup to another.

To remove ASM Files

In order to delete RMAN backup files, which will have a type of "ARCHIVELOG"
and "BACKUPSET" the following two commands can be run and their output
spooled to separate files

COL gsql FORMAT a300
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON

--generates file list for ARCHIVELOG
SELECT 'alter diskgroup 'dg.name' drop file
''+'dg.name''SYS_CONNECT_BY_PATH(al.name,'/')''';'
FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
WHERE al.file_number = fi.file_number(+)
AND al.group_number = dg.group_number
AND fi.type = 'ARCHIVELOG'
START WITH alias_index = 0
CONNECT BY PRIOR al.reference_index = al.parent_index
/

--generates file list for BACKUPSET
SELECT 'alter diskgroup 'dg.name' drop file
''+'dg.name''SYS_CONNECT_BY_PATH(al.name,'/')''';'
FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
WHERE al.file_number = fi.file_number(+)
AND al.group_number = dg.group_number
AND fi.type = 'BACKUPSET'
START WITH alias_index = 0
CONNECT BY PRIOR al.reference_index = al.parent_index
/

--get database name
ACCEPT database PROMPT 'Enter the database name: '
--generates file list
SELECT 'ALTER DISKGROUP 'gname' DROP FILE '''full_path''';' gsql FROM
(SELECT CONCAT('+'gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path, gname FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY adir
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
WHERE adir='N'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
WHERE full_path LIKE UPPER('%&database%');

Restoring the stats

Sometimes it is required to restore the stats from history. Oracle 10g provides automatic retention of stats in dictionary. By default it keeps it for 31 days. And you can get it back anytime.


History information is available in dba_tab_stats_history.

Two functions can tell the retention and availability of the history

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
1 row selected.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
08-JAN-09 10.01.52.927345000 PM -05:00
1 row selected.

****************
Now to restore the stats for any day, we just have to use the restore_schema_stats, restore_table_stats etc functions


Case Study

13:22:42 SYS@ptdev SQL> create table maktest.a (id int);
Table created.
Elapsed: 00:00:00.14

13:22:54 SYS@ptdev SQL> insert into maktest.a values(1);
1 row created.
Elapsed: 00:00:00.00
13:23:06 SYS@ptdev SQL> /
1 row created.
Elapsed: 00:00:00.00
13:23:06 SYS@ptdev SQL> /
1 row created.
Elapsed: 00:00:00.00
13:23:07 SYS@ptdev SQL> commit;
Commit complete.
Elapsed: 00:00:00.01

13:23:14 SYS@ptdev SQL> exec dbms_stats.gather_schema_stats('MAKTEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.83

13:24:28 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 3
2 rows selected.
Elapsed: 00:00:00.12

13:24:46 SYS@ptdev SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = 'MAKTEST' and table_name = 'A';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
A 09-FEB-09 01.24.28.094433 PM -05:00
1 row selected.
Elapsed: 00:00:00.03

13:25:46 SYS@ptdev SQL> truncate table maktest.a;
Table truncated.
Elapsed: 00:00:00.05

13:25:58 SYS@ptdev SQL> exec dbms_stats.gather_schema_stats('MAKTEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.42

13:26:05 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 0
2 rows selected.
Elapsed: 00:00:00.15

13:26:09 SYS@ptdev SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = 'MAKTEST' and table_name = 'A';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
A 09-FEB-09 01.24.28.094433 PM -05:00
A 09-FEB-09 01.26.05.034477 PM -05:00
2 rows selected.
Elapsed: 00:00:00.00

13:26:20 SYS@ptdev SQL> exec dbms_stats.restore_schema_stats(ownname=>'MAKTEST', as_of_timestamp=>sysdate-2/1400);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27

13:27:27 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 3
2 rows selected.
Elapsed: 00:00:00.06



Conclusion
We do not need to enable any manual script to cature the stats history. Its
present by default in 10g for 31 days and can be restored at anytime.

Install 9208 on RHEL 4 x86_64

Install 9208 64bit On Linux X86_64

Scope and Application
The intended audience for this note is Oracle DBA's and customers wanting to install Oracle 9iR2 64-bit on Red Hat Enterprise Linux 4.0 x86-64 (AMD64/EM64T)

Software Download
Install 9204 successfully then Patch it to 9208

If you have a valid OTN account, you can download the base 9.2.0.4 software from the following URL:
http://www.oracle.com/technology/software/products/oracle9i/index.html

Requirements for Installing Oracle 9iR2 64-bit on RHEL 4 x86-64 (AMD64/EM64T)
1. Install the required OS components
This list is based upon a "default-RPMs" installation of RHEL AS/ES 4 update 1. When a newer "update" level is used, the RPM release numbers (such as 2.4-9.1.87) may be slightly higher (such as 2.4-9.1.93 or 2.4-9.2.37). This is fine so long as you are still using RHEL AS/ES 4 RPMs.
glibc-kernheaders-2.4-9.1.87.x86_64.rpm
glibc-headers-2.3.4-2.9.x86_64.rpm
glibc-devel-2.3.4-2.9.x86_64.rpm << both ARCH's are required. See below.
glibc-devel-2.3.4-2.9.i386.rpm << both ARCH's are required. See above.
compat-gcc-32-3.2.3-47.3.x86_64.rpm
compat-gcc-32-c++-3.2.3-47.3.x86_64.rpm
libstdc++-devel-3.4.3-22.1.x86_64.rpm
libaio-0.3.103-3.x86_64.rpm

Check RPMs.
rpm --query --qf '%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n' binutils compat-db control-center gcc gcc-c++ glibc glibc-common gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver postfix libaio libaio-devel xorg-x11-deprecated-libs-devel glibc-devel sort

Output I got

binutils-2.15.92.0.2-24 (x86_64)
compat-db-4.1.25-9 (i386)
compat-db-4.1.25-9 (x86_64)
control-center-2.8.0-12.rhel4.5 (x86_64)
gcc-3.4.6-9 (x86_64)
gcc-c++-3.4.6-9 (x86_64)
glibc-2.3.4-2.39 (i686)
glibc-2.3.4-2.39 (x86_64)
glibc-common-2.3.4-2.39 (x86_64)
glibc-devel-2.3.4-2.39 (i386)
glibc-devel-2.3.4-2.39 (x86_64)
gnome-libs-1.4.1.2.90-44.1 (x86_64)
libaio-0.3.105-2 (i386)
libaio-0.3.105-2 (x86_64)
libaio-devel-0.3.105-2 (x86_64)
libstdc++-3.4.6-9 (i386)
libstdc++-3.4.6-9 (x86_64)
libstdc++-devel-3.4.6-9 (i386)
make-3.80-6.EL4 (x86_64)
package postfix is not installed
pdksh-5.2.14-30.6 (x86_64)
sysstat-5.0.5-16.rhel4 (x86_64)
xorg-x11-deprecated-libs-devel-6.8.2-1.EL.33.0.2 (i386)
xorg-x11-deprecated-libs-devel-6.8.2-1.EL.33.0.2 (x86_64)
xscreensaver-4.18-5.rhel4.14 (x86_64)


Packages version required by Oracle x86-64
binutils (2.15.92.0.2-10.EL4)
compat-db 4.1.25-9
control-center (2.8.0-12)
gcc (3.4.3-9.EL4)
gcc-c++ (3.4.3-9.EL4)
glibc (2.3.4-2)
glibc 2.3.4-2
glibc-common (2.3.4-2)
gnome-libs 1.4.1.2.90-44.1
libstdc++ (3.4.3-9.EL4)
libstdc++-devel (3.4.3-9.EL4)
make 3.80.5
pdksh (5.2.14-30)
sysstat 5.0.5-1
xscreensaver (4.18-5.rhel4.2)

2. Configure the Unix environment
The first critical environment item is related to the gcc v3.2 and g++ v3.2 RPMs that were installed above. Run these commands:
mv /usr/bin/gcc /usr/bin/gcc.orig
mv /usr/bin/g++ /usr/bin/g++.orig
ln -s /usr/bin/x86_64-redhat-linux-gcc32 /usr/bin/gcc
ln -s /usr/bin/x86_64-redhat-linux-g++32 /usr/bin/g++

Check RH version:
rpm -qa redhat-release redhat-release-4ES-4.1

Next, the following environment settings are required for the Unix user performing the installation (eg. oracle):
% umask 0022
% echo $LD_ASSUME_KERNEL 2.4.19

Next, as root, modify your kernel settings in /etc/sysctl.conf as follows:
fs.file-max = 327679
kernel.shmmax = 4294967296
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000

The Unix user performing the installation (eg. oracle) should not have the Oracle install related variables set by default. For example setting ORACLE_HOME, PATH, LD_LIBRARY_PATH to include Oracle binaries in .profile, .bash_profile, .login file and /etc/profile.d should be completely avoided

3. Configure the Unix user process and file limits
Assuming that the "oracle" Unix user will perform the installation, do the following:
- Add the following settings to /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

- Add or edit the following line in the /etc/pam.d/login file, if it does not already exist: session required pam_limits.so

- Add the following lines to /etc/profile:
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Start the Install
Software Download
It is about 1.5Gb and is of 3 Zipfiles

1. Download to the local drive and then upload to the destination
It is in format

2. Gunzip

3. Then cpio –idmv < filename.cpio for all the 3 files
It will create three directories called Disk1 Disk2 and Disk3
Important Oracle Variables to be set in .bash_profile
umask 022
TMPDIR=/tmp

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi



export EDITOR=vi
PS1="[`hostnamecut -d"." -f1`:$LOGNAME]"'$PWD> '
export PS1

ORACLE_BASE=/opt/oracle; export ORACLE_BASE
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_TERM=xterm
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
LD_ASSUME_KERNEL=2.4.19; export LD_ASSUME_KERNEL

Once set, run .bash_profile once
export DISPLAY=:0

Start xWindows client
Go to the install directory and check xclock
Then change the directory to the install directory
cd [lb4oraca1dq:oracle]/opt/oracle/backup/stage/9204/Disk1>
./runInstaller
……………
………………
……………………

Upgrade
Once 9204 is done, apply the 9208 patch.
Unzip the file from 9208 folder.
unzip p4547809_92080_Linux-x86-64.zip
cd to Disk1,
./runInstaller.

Shipping ASM archivelogs

Sometimes it is required to ship the archivelogs from one server to another. It becomes difficult when archivelogs are sitting on ASM file system. ASM files can not be directly accessed. RMAN can come to the rescue in this.

I had an issue of missing archives on STANDBY server (Log GAP) and somehow those archives got backedup to tape and mrp could not access it. Now I had to restore it and then get it shipped to STANDBY server. Since the GAP was too big, I decided to restore it and manually ship it rather than MRP to handle it. This is the process I followed. I am sure there may be many ways. But this one worked for me.

• I identified log GAP using query

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
FROM V$LOG_HISTORY
GROUP BY THREAD#
/

• Then copy/restore the ASM logs to the cooked file system.

RMAN> run {
allocate channel c2 device type 'sbt_tape';
set archivelog destination to '/u01/app/oracle/admin/infra/arch';
restore archivelog from sequence 8852 until sequence 8856 thread 2;
release channel c2;
}
2> 3> 4> 5> 6>
allocated channel: c2
channel c2: sid=2228 instance=infra1 devtype=SBT_TAPE
channel c2: Data Protection for Oracle: version 5.3.3.0
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 22-JAN-09
archive log thread 2 sequence 8856 is already on disk as file /u01/app/oracle/admin/infra/arch/infra_2_8856_641059495.arc
channel c2: starting archive log restore to user-specified destination
archive log destination=/u01/app/oracle/admin/infra/arch
channel c2: restoring archive log
archive log thread=2 sequence=8852
channel c2: restoring archive log
archive log thread=2 sequence=8853
channel c2: restoring archive log
archive log thread=2 sequence=8854
channel c2: restoring archive log
archive log thread=2 sequence=8855
channel c2: reading from backup piece qek5f68i_1_1
channel c2: restored backup piece 1
piece handle=qek5f68i_1_1 tag=TAG20090122T161057
channel c2: restore complete, elapsed time: 00:00:55
Finished restore at 22-JAN-09
released channel: c2
RMAN> exit

• Once the logs are restored, scp or sftp it to the standby destination. This will again be a cooked file system

scp infra_2_*.arc :/tmp
infra_2_8853_641059495.arc 100% 131MB 11.0MB/s 00:12
infra_2_8854_641059495.arc 100% 267MB 12.2MB/s 00:22
infra_2_8855_641059495.arc 100% 60MB 8.5MB/s 00:07
infra_2_8856_641059495.arc 100% 542KB 541.5KB/s 00:00

• Now if standby database log recovery destination is ASM file system or different than the scp destination, manually recover the database. Or at the recovery prompt manually feed the file name with a full path.

SQL> alter database recover managed standby database cancel;

SQL> recover from '/tmp' standby database;

• Start the managed recovery

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

• Verify the log shipping and log apply and That's it

Modify service error - CRS-0211 PRKP-1029

When I tried to modify a service to add available instances and preferred instances, I did couple of times and it errors out with

PRKP-1029 : Failed to register the service ges_taf.
CRS-0211: Resource 'ora.ges.ges_taf.cs' has already been registered.

I removed the service using srvctl and it got removed successfully.

[oracle]> srvctl remove service -d ges -s ges_taf
ges_taf PREF: AVAIL: ges1 ges2 ges3
Remove service ges_taf from the database ges? (y/[n]) y

Then I tried to add it again, it failed with a similar error

[oracle]> srvctl add service -d ges -s ges_taf -r ges1,ges2,ges3 -P BASIC
PRKP-1029 : Failed to register the service ges_taf.
CRS-0211: Resource 'ora.ges.ges_taf.cs' has already been registered.

After some research found that the only way to get rid of this error is to unregister the resource using crs_unregister. Oracle does not recommend it. But in the past I had to wait long time to get an answer from Oracle on this. So this time I did following

First check if the service is registered with OCR and not running

[oracle]> crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora...._taf.cs application OFFLINE OFFLINE
ora....es1.srv application OFFLINE OFFLINE
ora....es2.srv application OFFLINE OFFLINE
ora....es3.srv application OFFLINE OFFLINE

[oracle]> srvctl config service -d ges

logged in as root

cd $CRS_HOME

Make sure first unregister the srv and then cs, else it will give errors while unregistering

[oracle]>crs_stat -p > /tmp/1
[root]> grep srv /tmp/1
NAME=ora.ges.ges_taf.ges1.srv
NAME=ora.ges.ges_taf.ges2.srv
NAME=ora.ges.ges_taf.ges3.srv
[root]> ./crs_unregister ora.ges.ges_taf.ges1.srv
[root]> ./crs_unregister ora.ges.ges_taf.ges2.srv
[root]> ./crs_unregister ora.ges.ges_taf.ges3.srv
[root]> grep ".cs" /tmp/1
NAME=ora.ges.ges_taf.cs
[root]> ./crs_unregister ora.ges.ges_taf.cs

Check this time if its not with CRS anymore

[oracle]> crs_stat -t
[oracle]> srvctl add service -d ges -s ges_taf -r ges1,ges2,ges3 -P BASIC
[oracle]> srvctl config service -d ges
ges_taf PREF: ges1 ges2 ges3 AVAIL: