enq: FB - contention

Was working on splitting a partition from max (about 300 Million Records) to month wise and happened to notice this wait event.

What is this event means?
This is associated with block formatting and it ensures that only one process format the blocks in an ASSM tablespace.
As I mentioned earlier, I was splitting a partition with 8 parallels and each sessions doing insert and could be that each session tried to format the block which caused the contention.
Below query will give you the details
select session_id, sql_id, event, p1, p1text, p2 "TS#", p3 "Block", wait_time, session_state, time_waited
  from gv$active_session_history
 where event like '%FB%'
   and sql_id = 'dg8ksy7wvv1nd'; --replace sqlid here

Query v$tablespace for the returned TS# to find out which tablespace.
Use below query to find the block and database file associated with it.

select dbms_utility.data_block_address_block(p3 value from above query) "BLOCK",
         dbms_utility.data_block_address_file(p3 value from above query) "FILE"
  from dual;

ORA-39168: Object path TABLE was not found. ORA-31655: no data or metadata objects selected for job

ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job

Was getting the above error while trying to export using expdp (not my cup of coffee with this beast).
Basically, I was trying to export selected tables from a schema using INCLUDE=TABLE:"IN (select query

Problem was I removed SCHEMAS param in the parameter and thus it was throwing this error.

Correct Syntax (Unix):
SCHEMAS=schema_name_here
INCLUDE=TABLE:"IN (select table_name from other_schema.final_export_tabs where schema_name = 'schema_name_here' and notes is null)"

I am still trying to find a way to pass schema name along with the table so that I can try getting one export dump with multiple schemas and selected tables only... if some can find it please reply to me with the solution. Thanks in advance :)

Never mind, I figured that out.
Here is the syntax:
SCHEMAS=schema_name1,schema_name2,schema_name3
INCLUDE=TABLE:"IN (select table_name from other_schema.final_export_tabs where notes is null)"

I was worried that it might fail as there are tables which have same name in multiple schemas but no issues as expdp got them from both schemas without any issues.

OGG-00516 - ORA-01031: insufficient privileges SQL alter synonym

OK, this one took me a while to understand what's going on with one of my Golden Gate Replicat process.

Its abending with the above error. It seems to be pretty simple statement and not sure how this can happen.

Oracle says (check metalink id:1435898.1) alter synonym compile is "un-documented" command and therefore they wont recommend running that sql.
Actually, you can run alter synonym compile with in your schema synonyms with no issue.
If you want to compile other synonyms which dont belong in your schema then you will get that error. In this case you need to log in as SYSDBA. Makes perfect sense.

But wait, why is Golden Gate fails though?

Check below:
19:39:45 SQL> connect UserDBA@SOURCE
Enter password:
Connected.
19:39:59 SQL> alter session set current_schema=User2;

Session altered.

19:40:14 SQL> alter synonym User2.pkg_gg_test compile;
alter synonym User2.pkg_gg_test compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

If you notice above, even though you set your current session to the actual user where the synonym exists it wont work. This is exactly what is happening with in Golden Gate.

Golden Gate starts with GGADMIN user and whenever there is a DDL operation, it will set the current session to that particular user to execute the DDL and this alter synonym fails due to that.

Only way that I can see to by pass this error is to include exception "ddlerror 1031 ignore" at replicat param and continue with these errors (will have to restart the replicat without this exception once DDL passes).

Here is the complete Test Case to reproduce that error in Golden Gate:

C:\Users\eqt6tiw>sqlplus User1@SOURCE

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 27 18:53:01 2013

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

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> grant connect,resource,dba to UserDBA identified by UserDBAPW;

Grant succeeded.

SQL> grant connect,resource to User2 identified by User2PW;

Grant succeeded.

Creating UserDBA and User2 at TARGET (User1 already exists and setup in GG for REPLICAT)

SQL> connect User1@TARGET

Enter password:

Connected.

SQL> grant connect,resource,dba to UserDBA identified by UserDBAPW;

Grant succeeded.

SQL> grant connect,resource to User2 identified by User2PW;

Grant succeeded.

Create synonym at SOURCE

SQL> connect UserDBA@SOURCE

Enter password:

Connected.

SQL> create synonym User2.pkg_gg_test for User1.pkg_gg_test;

Synonym created.

Verified at target that synonym got created on this package under User1 schema

Now running that alter synonym by login as User2

SQL> connect User2@SOURCE

Enter password:

Connected.

19:17:18 SQL>

19:17:45 SQL>

19:17:45 SQL> alter synonym User2.pkg_gg_test compile;

Synonym altered.

19:17:46 SQL>

GG Error:

2013-11-27 18:17:35  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  REPLICAT RPCS99 started.

2013-11-27 18:17:52  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL found, operation [alter synonym User2.pkg_gg_test compile  (size 43)].

2013-11-27 18:17:52  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL is of mapped scope, after mapping new operation [alter synonym User2.pkg_gg_test compile  (size 43)].

2013-11-27 18:17:52  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [SYNONYM], objowner [User1], objname [PKG_GG_TEST].

2013-11-27 18:17:52  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Setting current schema for DDL operation to [USER2].

2013-11-27 18:17:52  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Executing DDL operation.

2013-11-27 18:17:52  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  Fatal error executing DDL replication: error [Error code [1031], ORA-01031: insufficient privileges SQL alter synonym user2.pkg_gg_test compile  /* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].

2013-11-27 18:17:52  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RPCS99.prm:  PROCESS ABENDING.

IMPDP Error ORA-39002: invalid operation ORA-39165:

Forgot to add this the other day when I came across this interesting feature ( :-/ ) oracle.
Import (impdp) was failing with:

ORA-39002: invalid operation
ORA-39165: Schema SCHEMA_NAME was not found.

Took me a while to figure out why I was getting that error.
Went thru EXPDP log and find that the similar error in there too:
ORA-39165: Schema SCHEMA_NAME was not found.

Interestingly, That schema has no objects and thus EXPDP was erroring with that error and thus the IMPDP too.

Removed these schema(s) from the IMPDP list and import went thru fine...

Fatal error executing DDL replication: error [Error code [24344], ORA-24344: success with compilation error SQL

One of my Golden Gate Replict group failed today with:

Fatal error executing DDL replication: error [Error code [24344], ORA-24344: success with compilation error SQL

Well, time to add exceptions kinda list to rpm file.

Added "ddlerror 24344 ignore" to that group rpm and am back in business.

Note: please pay attention to those objects are which are compiled with errors to make sure its valid not because some other dependencies in that object are missed in your replication environment...

ERROR OGG-01444 Oracle GoldenGate Delivery for Oracle, RPCS03.prm: Error in replicating sequence value [ORA-20783:

One of the group started abending with sequence failure as you see below:

2013-11-11 14:43:21  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Aborted grouped transaction on 'SCHEMA.SEQUENCE_NAME', Database error 20783 ().
2013-11-11 14:43:21  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Repositioning to rba 1850715874 in seqno 15.
2013-11-11 14:43:22  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  SQL error 20783 mapping SCHEMA.SEQUENCE_NAME to SCHEMA.SEQUENCE_NAME.
2013-11-11 14:43:22  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Repositioning to rba 1850730331 in seqno 15.
2013-11-11 14:43:22  ERROR   OGG-01444  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  Error in replicating sequence value [ORA-20783:
ORA-08004: sequence SEQUENCE_NAME.NEXTVAL exceeds MAXVALUE and cannot be instantiated
ORA-06512: at "GGADMIN.REPLICATESEQUENCE", line 418
ORA-06512: at line 1 SQL BEGIN ggadmin .replicateSequence (TO_NUMBER(91900), TO_NUMBER(20), TO_NUMBER(1), 'SCHEMA', TO_NUMBER(10000), 'SEQUENCE_NAME', UPPER('ggadmin'), TO_NUMBER(2), TO_NUMBER (0), ''); END;].
2013-11-11 14:43:22  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RPCS03.prm:  PROCESS ABENDING.

Compared this sequence at the source and target and I see the maxvalue is set to same in Source and Target.

BTW, flush sequence is not an issue as this was already taken care in the beginning of group setup.

Only thing I can find so far on this kind of issue is that it might be a bug that I am hitting (BUG:17300028 - REPLICAT ABENDS WITH ORA-20783 WHILE REPLICATING SEQUENCE). 

Anyways, I took a chance with a workaround that I thought will work to by pass this issue after making sure that this sequence is not used in the database anywhere and knowing very well that maxvalue for this Sequence is set at Source and even if it is used it wont be crossing that maxvalue.

So, what i did is recreate that sequence at Target with higher maxvalue than it is defined and started replicat and its running fine now.

ORA-01628: max # extents (32765) reached for rollback segment

OK, Finally after spending weeks of time to figure out what was going wrong with my import (IMPDP), it turns out to be a bug in 11gR2.

Was trying to import about 80G dumpfiles and impdp keeps failing on the last table with

ORA-39171: Job is experiencing a resumable wait.

ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU499_3908321086$

Followed Metalink Doc: 1434643.1 (Data Pump (or other Oracle process) Reports ORA-01628: Max # Extents (32765) For Rollback Segment _SYSSMUx$) steps by shrinking and creating a new UNDO TS and etc., etc., but no use. That error kept poping up.

The thing is the table it was getting stuck is only about 9.5G with 93M records and UNDO still has about 30G left. It loads up 99% and then hangs with the wait event "wait for unread message on broadcast channel".
Note that this wait event is not just waiting on that continuously though. It only says waited a second or less than a second but never changes anything apart from that wait event.
Finally impdp will fail like 20-30 hours later with that above said error!!!!

Ok, enough blogging on this.

Reason for the error is, It's an Oracle Bug they recently identified and confirmed.
So, need to apply a patch "Patch 17306264: ORA-1628: MAX # EXTENTS (32765) REACHED FOR ROLLBACK SEGMENT - OFTEN ENCOUNTERE" and see how if the issue will be resolved or not.

Will know in couple of days.

ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01

May be I need to RTFM but found this in a hard way.

I was trying to initiate an EXPDP in our 10g (2 node RAC) database and I was getting this error.

ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user EQT6TIW
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39147: cannot migrate Data Pump queue table ownership to this instance

Basically, there was another EXPDP already running on 2nd node instance and my EXPDP session load balanced and connected to 1st node instance and tried to initiate EXPDP and throwing this error.

Try making the connection to 2nd node where the first EXPDP is already running and initiate another session of EXPDP and you will be fine.

Run the below query to see which instance and who's running the EXPDP:

select * from gv$datapump_job;
OR

select * from gv$session

 where program like 'ude%';

ude - Expdp
udi - Impdp

OGG-01161: Bad column index () specified for table...

If you are getting the error below, then that means there are some DDL Changes occurred at Source and Target is kicking you out...
Main problem is DDL Replication is not enabled from Source to Target.

Here is how to resolve (if DDL Replication was not enabled at source):
1. Perform the DDL that occurred at source (could be a new column or dropping a column etc.,) in the target.
2. Login into ggsci (of-course by changing direcotry to GG Home)
3. Run @ddl_enable

That will enable all DDL changes to replicate from source to target.
Data will by synced for the other DDL's that occurred prior to this enable (because you took care of them manually) so dont worry...

Finding most recent nologging operation that occurred in the database....

Going thru my old notes and found this to blog :)

Here is the sql to find most recent "nologging" operation occurred tablespace:

select name, unrecoverable_change#,                

       to_char (unrecoverable_time,'DD-MON-YYYY HH:MI:SS') 

  from v$datafile;

GoldenGate - Cleaning/Dropping a group...

In case if you ever want restart the whole Extract/Replicat group:
Here are the steps to cleanup or drop or delete a group in golden gate:
1. stop group_name
2. cleanup group_name
3. delete group_name

You can also use EXTRACT/REPLICAT after stop, cleanup, delete words but just a group_name is sufficient too.

GoldenGate not able to stop extract...

I was trying to stop Extract Process and I was getting this error (not going to blog more on this but just some commands).
There are open, long-running transactions. Before you stop Extract, make the archives containing data for those transactions available for when Extract restarts. To force Extract to stop, use the SEND EXTRACT EPCS04, FORCESTOP command.
Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 36376, SCN 69.4281137724 (300633881148),                             RBA 1572231184
Redo Thread 2, Redo Log Sequence Number 35408, SCN 69.4272252393 (300624995817),                             RBA 838206480.

Basically, there are some long running transactions (Active) in process and thus its not able to stop.

 send extract epcs04, showtrans

The above command shows the transaction which is active with more info.

Find out the transaction using:

select s.saddr,s.sid, s.username, s.osuser, s.status, s.program, t.start_time

  from v$session s, v$transaction t 

 where s.saddr = t.ses_addr

 order by t.start_time;

Skip the transaction: 

SEND EXTRACT , SKIPTRANS : 

could lose data if there are subsequent operations in this txn that need to be captured.

Force GG to "capture" the txn as-is: 

SEND EXTRACT , FORCETRANS : 

remains open as far as the database is concerned, but GG writes it to the trail & will no longer track it in the logs. Data could be lost if more operations occur that would need to be captured.

Or Try this:
ggsci> send extract EPCS04, report
Check the report file under dirrpt/ and find out long running transaction id:
eg.,
WARNING OGG-01027  Long Running Transaction: XID 1090.15.75000, Items 0, Extract EPCS04, Redo Thread 1, SCN 98.2643258821 (423550053829), Redo Seq #123329, Redo RBA 2649730064.

select hash_value, address, executions,buffer_gets, disk_reads,
       round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
       round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
       last_load_time, module, sql_fulltext
  from v$sqlarea
 where sql_id = (
                 select --sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action
                        sql_id --prev_sql_id
                   from gv$session
                  where taddr = (
                                 select addr from gv$transaction where xidusn=1090 --this is the first part of XID from the report file
                                )
                )

ORA-00376: file 50 cannot be read at this time

No need to say anything but following is the error you may get and below is the way to solve it.
How and why this happens? In our case, whenever there is a SAN Scan occurs we see couple of datafiles getting corrupted here and there.

ORA-12801: error signaled in parallel query server P001, instance ndhdbp4 ay_p4 (2)
ORA-00376: file 50 cannot be read at this time
ORA-01110: data file 50: '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf'
when trying to selete from table I_SUBSCRIPTION_SUMMARY


SQL> select file#, ts#, status from gv$datafile where status!='ONLINE';

     FILE#        TS# STATUS
---------- ---------- -------
         1          0 SYSTEM
        28          0 SYSTEM
        36          7 RECOVER
        50          6 RECOVER

SQL> select file_name, file_id from dba_data_files where file_id in (36,50);

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID
----------
/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf
        36

/ora07/oradata/pay_p/payer_hosting_8k_16.dbf
        50


SQL> recover datafile '/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf';
Media recovery complete.
SQL> alter database datafile '/ora19/oradata/pay_p/pcs_fs_data_8k_13.dbf' online;

Database altered.

SQL> recover datafile '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf';
Media recovery complete.
SQL> alter database datafile '/ora07/oradata/pay_p/payer_hosting_8k_16.dbf' online;

Database altered.

SQL>

ORA-01114: IO error writing block to file 8 (block # 3307) ORA-29701: unable to connect to Cluster Manager

Was getting that error for any ddl operation.

crsctl check crs throws this error:

>crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon

vxfs: msgcnt 2849 mesg 001: V-2-1: vx_nospace - /dev/vx/dsk/local_ora/oracle_lv                                                           file system full (1 block extent)Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manager

That tells me that ORA-1114 is nothing to worry as this is just file system is full and oracle was not able to write any traces.

Clear up the mount point and you should be good.

Statistics reply buffer exceeded. Results truncated...

In GoldenGate when using "stats extract group_name" you may get this error when the ouptut is too big to fit in the buffer:

"Statistics reply buffer exceeded.  Results truncated..."

You can write the statistics to a report file like below for the entire group stats:

GGSCI (ndhdbp3) 18> send epcs07, report

Sending REPORT request to EXTRACT EPCS07 ...
Request processed.


Or use stats extract group_name, table owner.table_name(or *) to get stats on individual table or schema.

Failed to get next ts for EXTRACT PPCS07 (error 109, Invalid pointer retrieving GGS logtrail timestamp in )

GGSCI (dbsrv1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EPCS07      00:48:12      00:00:08
Failed to get next ts for EXTRACT  PPCS07     (error 109, Invalid pointer retrieving GGS logtrail timestamp in )


GGSCI (ndhdbp4) 3> info *

EXTRACT    EPCS07    Last Started 2013-08-20 14:05   Status RUNNING
Checkpoint Lag       00:57:05 (updated 00:00:10 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-08-20 14:19:58  Thread 1, Seqno 34114, RBA 1065005856
                     SCN 69.2532298994 (298885042418)
Log Read Checkpoint  Oracle Redo Logs
                     2013-08-20 14:20:00  Thread 2, Seqno 33665, RBA 268387208
                     SCN 69.2532299819 (298885043243)

EXTRACT    PPCS07    Last Started 2013-08-20 15:02   Status RUNNING
ERROR: Error retrieving current checkpoint timestamp.

That message keeps coming for every Extract process we run so thought of nailing it down to see whats causing this message to popup when the Extract and Pump process is running fine.

Well, the reason was simple:
When creating Pump process, we used "Threads 2" (just like for Extract process) and that "Threads 2" option is not valid with Pump process. Thats why we were getting this message.

As you see "info *" also errors out when it tries to show info for thread 2.

Recreate the pump process without this parameter and you wont see this coming up again...

Golden Gate Initial Load Methods...

GoldenGate (Rel.11) provides multiple choices on performing Initial Load for any Migration or DR setup apart from other Legacy methods such as RMAN backup/restore, cloning etc.,

I am working on migrating 10g database from HP unix to 11gR2 Linux. Pretty simple migration one can think...
The catch is we are dealing with databases ranging from 2TB to 40TB.
So, XTTS (Cross Platform Transportable Tablespaces) is also not an option with 40TB database.

So, went with GoldenGate and trying to chose the best Initial Load methodology and thought of sharing that info here.

Below are the Initial Load GoldenGate methods:

Opt#	Option	Concept	Pros	Cons	Met#
1	Loading Data from File to Replicat	Initial load extract writes the records to an external file which Replicat uses to apply the changes to the target site.		Quite a slow initial-load and cannot be used due to limitations such as column size/data-type limitations.	1441172.1
1.a	Loading Data from Trail to Replicat	Initial load extract writes the records to a series of external files which Replicat uses to apply the changes to the target site.	Faster than #1 because replication can begin while extraction is already started. It can also process a near unlimited amount of tables.		1195705.1
2	Loading Data from File to Database Utility	Initial load extract writes the records to an external ASCII files. These files are uses as datafiles at target by a data bulk load utility native to the database such as SQLLoader			1457989.1
3	Loading Data with an Oracle GoldenGate Direct Load	Initial load extract extracts the records and sends them directly to a Replicat initial-load task.	Faster	No support for LOBs, LONGs and UTD's and any other data-type larger than 4K.	1457164.1
4	Loading Data with a Direct Bulk Load to SQL*Loader	Initial load extract extracts the source records and sends them directly to an initial-load Replicat task, which is dynamically started by manager. The initial load Replicat task interfaces with the SQL*Loader API to load the data as a direct-bulk load		No support for LOBs, LONGs. Only works with Oracle's SQLLOADER.	146185.1

As you see none of the above, method 1, 1a, 3 and 4 are not going to work for my situation as we have plenty of CLOB's, LONG's and UTD's (User Defined Datatype).
So only option left is #2 and I really did not want to use that method as our databases are not in GB range but TB's and trying to extract the data into ASCII file and storing them on source and transferring to target over network and load them... no. not really an option to chose from.

So, what's my option for Initial load then?

I propose pure legacy method using EXP(dp)/IMP(dp)  as this will be lot faster than option#2 and merely there is no other option left.

Check the note below:

999

EXP/IMP

Big UNDO as this will be with SCN

1276058.1

Also planned on moving more than 50% of 40TB data to Target prior to starting Extract process to reduce the overall migration time.

Reach me out for more details on this monster migration.

ORA-01031: insufficient privileges while rebuilding index online

When rebuilding an index online Oracle will create a journal table in the schema where the index resides.

Thus said, you will get this error if you are trying to rebuilding an index thru a procedure using execute immediate even though you are the owner of the table/index/procedure.

You have to have direct grants on every operation when executing using execute immediate.

So, have "grant create table" "grant alter any index" and you should be good.

ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'

Got the following warning while upgrading 11.2.0.3.0 to 11.2.0.3.6
Oracle NoteID:1448337.1 says don't worry about it :)

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'


Composite patch 16056266 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-07-23_16-15-40PM_1.log

Excerpt from Oracle Metalink note 1448337.1:

CAUSE

This warning is independent of the version of opatch used. 

Targets are defined more than once within the makefiles and this is just a warning that the second (later) definition is being used:

ins_srvm.mk:71: warning: overriding commands for target `libsrvmocr11.so'
ins_srvm.mk:34: warning: ignoring old commands for target `libsrvmocr11.so'

ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
env_precomp.mk:2115: warning: ignoring old commands for target `pcscfg.cfg'

If you check the $OH/install/make.log you will see that these warnings existed before patching i.e. after the original installation.  This is not an issue which the patch you are applying has introduced, simply a warning which opatch has correctly captured and is reporting back to the user.

SOLUTION

This is a warning only which opatch is reporting.  The Patch has applied successfully and the warning output can be safely ignored.

REFERENCES

Increasing VirtualBox VDI size...

Run the command vboxmanage with "modifyhd" parameter and the size in MB as follow if you ever want to increase the virtual size of your virtual box vdi size.

c:\VirtualBox>"c:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd OL6-112-Rac1.vdi --resize 51200
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Oracle Patches and Patchsets...

Refer to this Metalink ID 753736.1 for Quick Reference to RDBMS Database Patchset Patch Numbers

ORA-19625: error identifying file

Was getting the following error in one of our database backups.
Tried crosscheck archivelog all; and change archivelog all crosscheck; followed by delete expired archivelog all did not find this archive log at all.

RMAN-03002: failure of backup command at 07/16/2013 23:11:14
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /ora13/orafra/RH_PAY/archivelog/2013_03_07/o1_mf_1_82660_1TzwREeTB_.arc
ORA-17503: ksfdopn:4 Failed to open file /ora13/orafra/RH_PAY/archivelog/2013_03_07/o1_mf_1_82660_1TzwREeTB_.arc
ORA-17500: ODM err:File does not exist

Will have to research more to see where this file information got stuck.
Solution:
But for now I am going to go with SKIP INACCESSIBLE option in my backup script.

Netezza Database size

Below sql will give you the size used and allocated for each database in Netezza:

select orx.database::nvarchar(64) as "databasename" ,
       case when sum(sod.used_bytes) is null then 0 else sum(sod.used_bytes)/1073741824 end as "usedspace_gb",
       case when sum(sod.allocated_bytes) is null then 0 else sum(sod.allocated_bytes)/1073741824 end as "allocatedspace_gb"
  from _v_sys_object_dslice_info sod inner join _v_obj_relation_xdb orx on orx.objid = sod.tblid
 group by "databasename"
 order by "databasename";

ASM Disk Limits

Limits when configuring ASM Instance (meatlink Note ID:370921.1)
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]

ASM imposes the following limits:

63 disk groups in a storage system

10,000 ASM disks in a storage system

2 terabyte maximum storage for each ASM disk (the Bug 6453944 allowed larger sizes, but that led to problems, see Note 736891.1 "ORA-15196 WITH ASM DISKS LARGER THAN 2TB")

40 exabyte maximum storage for each storage system

1 million files for each disk group

2.4 terabyte maximum storage for each file

ORA-19643: datafile 6: incremental-start SCN is too recent

When you see that error in your RMAN backups then check the status of the file#. It might be OFFLINE.

Here is the RMAN Log showing that error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on dev_7 channel at 07/10/2013 13:31:04
ORA-19643: datafile 6: incremental-start SCN is too recent
ORA-19640: datafile checkpoint is SCN 286109177130 time 01/28/2013 15:44:19

Recovery Manager complete.
[Major] From: ob2rman@ndhdbt3 "adm_t"  Time: 07/10/13 13:32:09
External utility reported error.

RMAN PID=9062

[Major] From: ob2rman@ndhdbt3 "adm_t"  Time: 07/10/13 13:32:09
The database reported error while performing requested operation.

RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03009: failure of backup command on dev_7 channel at 07/10/2013 13:31:04
 ORA-19643: datafile 6: incremental-start SCN is too recent
 ORA-19640: datafile checkpoint is SCN 286109177130 time 01/28/2013 15:44:19

 Recovery Manager complete.

In my case, I know that we have to take this particular tablespace offline due to some issues and that was causing SCN check to be invalid.

select current_scn from v$database;
CURRENT_SCN
331472777523

So, I just have to ged rid of that tablespace to make my backups run successful.

ORA-23515: materialized views and/or their indices exist in the tablespace

Was trying to drop a tablespace that was not needed anymore and I receive this error:

drop tablespace ts1 including contents and datafiles cascade constraints;

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution:
Run the following query to get the materialized views that it is talking about and drop them first and then you can drop the tablespace without any issues.

select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
  from dba_registered_snapshots
 where name in (select table_name from dba_tables where tablespace_name = 'TS1');

RMAN Slow performance with "control file sequential read" wait event

We have a database which is about 40TB and the control file is about 900MB and we have about 1.5TB Archivelogs generation everyday.

So, was thinking it was because of all the above why "show all" takes about 45 minutes in RMAN...

But, for some reason I was not able to convince my self that it should take that long for just SHOW ALL command!!!.
Not only that, it takes way too long to just allocate channels and that was making our backups to take for ever...

Anyways, digging deeper found the following sql that just sits in there with "control file sequential read" wait event... This made me to hunt for the sql:

SELECT RECID, STAMP, TYPE OBJECT_TYPE, OBJECT_RECID, OBJECT_STAMP, OBJECT_DATA,
       TYPE, (CASE
                WHEN TYPE = 'DATAFILE RENAME ON RESTORE' THEN DF.NAME
WHEN TYPE = 'TEMPFILE RENAME' THEN TF.NAME
ELSE TO_CHAR (NULL)
 END)
  OBJECT_FNAME, (CASE
WHEN TYPE = 'DATAFILE RENAME ON RESTORE' THEN DF.CREATION_CHANGE#
WHEN TYPE = 'TEMPFILE RENAME' THEN TF.CREATION_CHANGE#
ELSE TO_NUMBER (NULL)
 END)
    OBJECT_CREATE_SCN, SET_STAMP, SET_COUNT
  FROM V$DELETED_OBJECT, V$DATAFILE DF, V$TEMPFILE TF
 WHERE OBJECT_DATA = DF.FILE#(+)
   AND OBJECT_DATA = TF.FILE#(+)
   AND RECID BETWEEN :b1 AND :b2
   AND (STAMP >= :b3 OR RECID = :b2)
   AND STAMP >= :b4
 ORDER BY RECID

That brought me into this metalink id# Rman Backup Very Slow After Mass Deletion Of Expired Backups (Disk, Tape) [ID 465378.1]

Work around solution made SHOW ALL to return the results in seconds from 45 minutes...

*** Please check with Oracle Support prior to deploying this workaround in your database to make user it is OK ***

Solution

Bug is fixed in 11g but until then, use the workaround of clearing the controlfile section which
houses v$deleted_object:

SQL> execute dbms_backup_restore.resetcfilesection(19);

Then clear the corresponding high water mark in the catalog :

SQL> select * from rc_database;
     --> note the 'db_key' and 'dbinc_key' of your target based on dbid

For pre-11G catalog schemas:

SQL> update dbinc set high_do_recid = 0 where db_key = '' and dbinc_key=;
SQL> commit;

For 11G+ catalog schemas:

SQL> update node set high_do_recid = 0 where db_key = '' and dbinc_key=;
SQL> commit;

ORA-00845: MEMORY_TARGET not supported on this system

We had one of our RAC Node crashed and all databases shutdown and as usual they should come back once the ASM is up.

But this one database would not start at all.

Grepping for PMON for that database returns nothing:

So, trying to start manually:

SQL*Plus: Release 11.2.0.2.0 Production on Mon May 27 04:55:22 2013

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

Connected to an idle instance.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-00845: MEMORY_TARGET not supported on this system

Now that is weird, why would I get that? /dev/shm has no space? Really? but why? database is not started yet to utilize that!!!!

Grepping for database name gives me:

db2:11.2.0:node2:/opt/oracle>ps -ef |grep db2
oracle     493     1  0 Apr28 ?        00:00:00 oracledb2 (LOCAL=NO)
oracle   24841     1  0 04:51 ?        00:00:00 oracledb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   24846     1  0 04:51 ?        00:00:00 oracledb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   31322 26885  0 05:02 pts/1    00:00:00 grep db2
oracle   32553     1  0 Apr28 ?        00:00:00 oracleras2 (LOCAL=NO)

Hmm... I get nothing when I grep for PMON but there is still something running in the backgroud as hung process!!!

Time to kill those process...
db2:node2:/opt/oracle>kill -9 493 24841 24846 32553

Now do startup and database comes up without any complaints...

RMAN-20035: invalid high recid

While moving our database backups to use new version of HP Data Protector software, I got a hick up on one database with "RMAN-20035: invalid high recid" error.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid

Also, we were moving our RMAN catalog from one database to another database to resolve another performance issue. So, it might be due to sync issue.

Solution to that was to un-register the database from RMAN catalog and re-register.

More on this topic/issue in metalink id#273446.1

Note, you can unregister using dbms_rcvcat.unregisterdatabase procedure also but I am showing what I did.

To Unregister:
Connect to your RMAN catalog as how you generally do take your db backup and run:

RMAN> unregister database;

To Register:

RMAN> register database;

After this I was able to backup the database successfully...

Finding Bind variables in SQL's executed...

v$sql_bind_capture --> this view shows the bind variables passed to the sql at the run time
DBA_HIST_SQLBIND --> historical information

Ex:

select b.last_captured, a.sql_text, b.name, b.position, b.datatype_string, b.value_string
  from dba_hist_sqlbind b,
       --gv$sql_bind_capture b,
       v$sqlarea a
 where b.sql_id = '66j3kkqnv3m2f' --> sqlid that you are interested in looking for bind variable passed.
   and b.sql_id = a.sql_id

From Oracle Docs on DBA_HIST_SQLBIND:

DBA_HIST_SQLBIND displays historical information on bind variables used by SQL cursors. This view contains snapshots of V$SQL_BIND_CAPTURE.

LAST_CAPTURED

DATE

Date when the bind value was captured. Bind values are captured when SQL statements are executed. To limit the overhead, binds are captured at most every 15 minutes for a given cursor.

RMAN Connect error with 11g

Was trying to connect to 11g database using RMAN and getting this error:

/opt/oracle/etc>rman target DP_BACKUP/pass@db_name


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

The userid/password works fine if I connect using sqlplus as showed below!!!

/opt/oracle/etc>sqlplus DP_BACKUP/pass@db_name

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 8 09:59:03 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning and Real Application Clusters options

SQL>

User DP_BACKUP has SYSDBA role assigned 

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS

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

SYS                            TRUE  TRUE  FALSE

DP_BACKUP                      TRUE  FALSE FALSE

But still errors out!!!

Reason is the password is not in sync with the password file. So, either re-create the password file with appropriate password or change the password for DP_BACKUP user.

Ora-27303: additional information: startup egid = 80, current egid = 82

While using DBUA to upgrade database from 11.2.0.2 to 11.2.0.3, DBUA was throwing an error starting with PRCR-1079: Failed to start resource ora.xxx.db followed by bunch of other errors.

One error in between those error stack kinda made me obvious to look the file "oracle" permissions.

Ora-27303: additional information: startup egid = 80 (dba), current egid = 82 (asmadmin)

So, comparing the file permissions on both Oracle Home and Grid Home tells me that they are not same:

/opt/oracle>ls -al app/product/11.2.0.3/db_1/bin/oracle

-rwsr-s--x 1 oracle dba 232399431 Apr 29 12:39 app/product/11.2.0.3/db_1/bin/oracle

/opt/oracle>

/opt/oracle>ls -al /opt/grid/app/11.2.0.3/grid/bin/oracle

-rwxr-xr-x 1 oracle dba 203973009 Apr  4 17:08 /opt/grid/app/11.2.0.3/grid/bin/oracle

/opt/oracle>

Changing the permissions on Grid Home Oracle and after this DBUA had no issues.

/opt/oracle>cd /opt/grid/app/11.2.0.3/grid/bin/

/opt/grid/app/11.2.0.3/grid/bin>chmod 6751 oracle

/opt/grid/app/11.2.0.3/grid/bin>ls -al oracle

-rwsr-s--x 1 oracle dba 203973009 Apr  4 17:08 oracle

/opt/grid/app/11.2.0.3/grid/bin>

ORA-01031: insufficient privileges with "as sysman"

This error "ORA-01031: insufficient privileges" really made me frustrated while trying to upgrade 11.2.0.2 to 11.2.0.3.

Basically, as an upgrade process it has to write some information to 11.2.0.2 ASM so it shutsdown ASM instance running on 11.2.0.2 and then try's to start up with 11.2.0.3 binaries and thats when I kept getting this error.

Following is the Error that I was getting at the time of migration:
--------------------------------------------------------------------------------------------

CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
Start of resource "ora.asm" failed
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'node1'
CRS-2676: Start of 'ora.drivers.acfs' on 'node11' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'node1'
ORA-01031: insufficient privileges
CRS-5017: The resource action "ora.asm start" encountered the following error:
ORA-01031: insufficient privileges
. For details refer to "(:CLSN00107:)" in "/opt/grid/app/11.2.0.3/grid/log/node1/agent/ohasd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.asm' on 'node1' failed
CRS-2679: Attempting to clean 'ora.asm' on 'node1'
ORA-01031: insufficient privileges

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

So, tried to just start "sqlplus / as sysasm" (note with / as sysdba has no issues)

NONE::node1:/opt/oracle>export ORACLE_SID=+ASM1
+ASM1::node1:/opt/oracle>export ORACLE_HOME=/opt/grid/app/11.2.0.3/grid
+ASM1::node1:/opt/oracle>export PATH=$ORACLE_HOME/bin:$PATH

+ASM1::node1:/opt/oracle>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 4 11:24:22 2013

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

ERROR:
ORA-01031: insufficient privileges


First thought it might be the environment variables but not in my case.
ORACLE_HOME is set to 11.2.0.3 and PATH has $ORACLE_HOME/bin;$PATH but still gets the same error.

No issue when I set ORACLE_HOME back to 11.2.0.2 !!!! So, whats the difference?

Took me a while to figure this out but nailed it:
Basically, when I was doing upgrade OUI offers with "Privileged Operating Systems Groups" for ASM DBA, ASM Operator and ASM Administration.
I chose the default values for this and un-fortunately in my environment grid user "oracle" does not belong to a group named "asmadmin" and thus I get "insufficient privileges"

How do I change it?

Check the file config.c under /opt/grid/app/11.2.0.3/grid/rdbms/lib/

You will see the following:


#define SS_DBA_GRP "asmdba"
#define SS_OPER_GRP "asmoper"
#define SS_ASM_GRP "asmadmin" --> this was my problem. I changed this to "dba".

Its not done yet.

We need to recompile oracle binary to get this change affected.

Here is how you do that:

node1:/opt/grid/app/11.2.0.3/grid/rdbms/lib>make -f ins_rdbms.mk config.o ioracle

Make sure to check the output thoroughly as you might see errors in 11g environment as the folder/files permissions might be owned by "root" and it might fail to move the old oracle file to oracleO and copy the new file.
If you do see such errors then ask your admin to replace the old oracle file from /opt/grid/app/11.2.0.3/grid/bin/oracle to /opt/grid/app/11.2.0.3/grid/bin/oracleO and copy the file from
/opt/grid/app/11.2.0.3/grid/rdbms/lib/oracle to /opt/grid/app/11.2.0.3/grid/bin/

That is it:
The error vanishes now.


+ASM1:node1:/opt/grid/app/11.2.0.3/grid/rdbms/lib>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 4 17:12:55 2013

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

Connected to an idle instance.

Good luck. I am having another migration issue right now and will see what else is messed up.

"_allow_resetlogs_corruption" recovery....

Please DO NOT use this in any production environment without Oracle Support...

*** Using this parameter should be your LAST option as you do not have any proper backups to bring your database online ***

One of our database was kind of un-opened mode due to system tablespace datafile corruption (for any reason like restored from old backup etc.,)

We are OK to loose some data and some tablespaces which were created later point than the SCN that system tablespace has. So, used the parameter "_allow_resetlogs_corruption" an un-documented (repeat Oracle will not support your database if you recover your database with this option without their support) parameter. Below these errors shows the use of this parameter and I was able to recover database and open.

Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/oradata/db_name/datafile//system.282.757162799'

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradata/db_name/datafile/system.282.757162799'

Use of the parameter:


SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1469792256 bytes
Fixed Size                  2121312 bytes
Variable Size            1107296672 bytes
Database Buffers          352321536 bytes
Redo Buffers                8052736 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01248: file 19 was created in the future of incomplete recovery
ORA-01110: data file 19: '/oradata/db_name/datafile/file2.dbf'

I know for sure I do not need to worry about this datafile as I have data that I can put back in so marking it as OFFLINE.

SQL> alter database datafile 19 offline;

Database altered.

Now Open with resetlogs and you should be good. Take a FULL Backup ASAP.

SQL> alter database open resetlogs;

Database altered.

SQL>

Once again, Please do not use this parameter in any production or critical databases without Oracle Support and recommendation otherwise you will end up with inconsistency database.

ORACLE error 29308 encountered

Got this error while doing TSPITR:


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
EXP-00008: ORACLE error 29308 encountered
ORA-29308: view TS_PITR_CHECK failure
ORA-06512: at "SYS.DBMS_PITR", line 889
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
host command complete

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/14/2013 14:08:50
RMAN-03015: error occurred in stored script Memory Script
RMAN-06135: error executing host command: Additional information: 256
Additional information: 11

RMAN>

Basically what it is telling is there are some objects in the tablespace that are dependent on some other tablespace which is not in the TSPITR list.

Running the following query shows me that there are some indexes those belong in SYSTEM.

TSPITR Check Query:

SELECT * 

FROM SYS.TS_PITR_CHECK 

WHERE (

        TS1_NAME IN ('DATA_8K') 

        AND TS2_NAME NOT IN ('DATA_8K')

      )

OR    (

        TS1_NAME NOT IN ('DATA_8K') 

        AND TS2_NAME IN ('DATA_8K')

      ); 

But that is not true because if I run dbms_tts.transport_set_check it wont show any violations:

begin
 sys.dbms_tts.transport_set_check('DATA_8K',TRUE,TRUE);
end;

select * from sys.transport_set_violations; -- returns no rows means DATA_8K tablespace objects are all self contained...

So, what is the issue with RMAN TSPITR!!!!

Its a bug identified and the issue is fixed in 11g. RMAN TSPITR has issues when IOT (Index Organized Tables) are present in the tablespace.

Bug#6620517: DO TSPITR SUPPORT IOT IN 10.2 Database.

Bug Attributes

---

Type	B - Defect	Fixed in Product Version	11.2
Severity	2 - Severe Loss of Service	Product Version	10.2.0.3.0
Status	80 - Development to QA/Fix Delivered Internal	Platform	268 - z*OBSOLETE: Microsoft Windows Server 2003 R2 (64-bit AMD64 and Intel EM64T)
Created	Nov 9, 2007	Platform Version
Updated	Sep 15, 2011	Base Bug	N/A
Database Version	10.2.0.3.0	Affects Platforms	Generic
Product Source	Oracle

Related Products

---

Line	Oracle Database Products	Family	Oracle Database
Area	Oracle Database	Product	5 - Oracle Database - Enterprise Edition

TNS-04404: no error caused by: oracle.net.config.ConfigException: TNS-04414: File error

Got this error while trying to create a database using DBCA.

TNS-04404: no error caused by: oracle.net.config.ConfigException: TNS-04414: File error caused by: TNS-04612

Nothing to worry or panic. Just check your tnsnames.ora file for entries and am pretty sure it will have some syntax errors like missing closed parenthesis etc., 

ORA-19550: cannot use backup/restore functions while using dispatcher

Your RMAN connection is going thru SHARED SERVER Mode.
Make a TNS entry with DEDICATED Mode ( (server=dedicated) ) and try again.


19550, 00000, "cannot use backup/restore functions while using dispatcher"
// *Cause:  Attempted to use backup/restore functions while connected to the
//          dispatcher in a shared server.
//          This is not allowed because the device that is
//          used for backup and restore must remain allocated to a single
//          process.
// *Action: Connect directly to the instance then re-execute the backup or
//          restore function.

RMAN TSPITR

Example on using RMAN TSPITR (Tablespace Point-in-Time Recovery):


Login into your RMAN Catalog by setting up your database environment.

rman catalog rman/password@catalogdb target /

RMAN> configure channel 1 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

RMAN> configure channel 2 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

I am configuring the above channels manually as I had issues RMAN allocating channels for AUXILIARY instance...

run {
SET NEWNAME FOR DATAFILE '/ora05/oradata/db1/abc_ts_01.dbf' TO '/ora03/oradata/db1/abc_ts_01.dbf'; --here I am changing the default location of a datafile to a new location
recover tablespace abc_ts until time "to_date('16-FEB-2013 06:41:00','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/ora05_1/auxdest'; --this is optional but I personally wanted to give a storage to RMAN for its dynamic Auxiliary instance.
}

The above will restore your tablespace until the time specified.

Once the above is successful, you will notice that tablespace is OFFLINE as Oracle wants you to take a backup of that tablespace and lets you bring that tablespace online.

So, take a backup:

RMAN> backup tablespace abc_ts; --or take a backup from your backup software...

and bring it online:

RMAN> sql 'alter tablespace abc_ts online';

RMAN-12012: multiple records for DISK parallelism found in catalog

While trying to restore (TSPITR) using RMAN Catalog I ran into this wired errors stating while allocating channels...


RMAN> recover tablespace abc123
until time "to_date('19-FEB-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/ora05/auxdest';

Starting recover at 20-FEB-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2013 16:43:10
RMAN-12010: automatic channel allocation initialization failed
RMAN-12012: multiple records for DISK parallelism found in catalog

RMAN> exit

Doing some research, found the solution to reset config and try again.

SQL> exec dbms_backup_restore.resetconfig;

Now try again and you will have no issues allocating channels...

Finding Oracle allocated semaphores in Linux...

As I always say, there is always something to know/learn.

Having some issues with high usage of semaphores in our environment and digging more found this nice utility from Oracle (yeah its been there since 8.1 but today I came to know about it) called "sysresv".

If you have access to metalink then here is the ID#123322.1

BTW, you can still get all semaphores allocated by oracle with a simple command:
ipcs -s |grep oracle --This will list out all semaphores allocated for all instances on the server.

sysresv utility gives the list of semaphores only for the instance that you pointed to...

Once you get the semaphore id (semid) try below to get more info on that semid like process id (pid):
ipcs -si semid

This will give detailed info like below:


Semaphore Array semid=331382805
uid=80   gid=80  cuid=80         cgid=80
mode=0660, access_perms=0660
nsems = 128
otime = Tue Feb  5 16:45:46 2013
ctime = Tue Feb  5 16:45:46 2013
semnum     value      ncount     zcount     pid
0                 1          0             0          1315
1             18380      0             0          1315
2             18703      0             0          1315

From the above try to ps -ef |grep pid and see if this shows any OS process that is related to Oracle (in our case). If ps -ef does not return anything then that is a dead process and time clear that semaphore allocation using ipcrm -s semid.

Here is the info about this nice utility if you do not have metalink access:


SYSRESV Utility [ID 123322.1]

The sysresv utility included with Oracle 8.1.5 and above provides instance
status (and OS resources used) for specified ORACLE_SIDs.  This utility is
especially useful when multiple instances are running.  OS resources can be
removed using this utility if the specified instance is detected to be dead.

This utility may be useful when an instance has crashed or was aborted
and memory and semaphores related to this instance were not cleaned up
automatically.  This utility is also helpful in determining which instance
is running.

The sysresv utility, located in $O_H/bin, can be used from locations other
than $O_H/bin.

Point your environment to the instance of interest before using sysresv.

Usage:
------

sysresv:
usage   : sysresv [-if] [-d ] [-l sid1 ...]
          -i : Prompt before removing ipc resources for each sid
          -f : Remove ipc resources silently, oevrrides -i option
          -d : List ipc resources for each sid if on
          -l sid1 .. : apply sysresv to each sid
Default : sysresv -d on -l $ORACLE_SID
Note    : ipc resources are attempted to be deleted for a
          sid only if there is no currently running instance
          with that sid.

 
Examples:
---------

o  Instance is not running:

   /u02/app/oracle/product/8.1.7> sysresv

   IPC Resources for ORACLE_SID "R817" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "R817"


o  Instance is running:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"


o  Attempting to remove memory and semphores using sysresv when Oracle
   detects an instance is running:

   /u03/app/oracle/product/8.1.6> sysresv -f

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "X816"


o  Removing IPC resources:

   [Sysresv shows memory and semaphores exist but Oracle determines the
    instance is not alive.  Cleanup is needed.]

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "X816"


o  Removing IPC resources using sysresv:

   /u03/app/oracle/product/8.1.6> sysresv -i

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"
   Remove ipc resources for sid "X816" (y/n)?y
   Done removing ipc resources for sid "X816"
   /u03/app/oracle/product/8.1.6


   Verify the resources were removed:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"

   
o  If you need to remove memory segments, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -m

   Where is the memory id shown in the sysresv output.

   Example:
   % ipcrm -m 16437

   If you need to remove semaphores, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -s

   where is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802