How to upload huge files to Oracle support

Oracle's ftp site can be used to do the job by following the below steps:

Move the file to your local machine and tar/zip them appropriately.

Open a command window.

Start ftp:
C:\T> ftp
ftp>

Connect to the oracle ftp server and connect as user anonymous:
ftp> open ftp.oracle.com
User (bigip-ftp.oracle.com:(none)): anonymous

Use as a password a valid email address used in metalink:

331 Please specify the password.
Password:
230 Login successful.

Move into the directory "support/incoming":

ftp> pwd
257 "/"
ftp> cd support/incoming
250 Directory successfully changed.

Create a new directory with the name of your service request number:
ftp> mkdir
257 "/support/incoming/" created
ftp> cd
250 Directory successfully changed.

Set the transfer mode to binary:
ftp> bin
200 Switching to Binary mode.

Put the files on the ftp server:
ftp> put

Use the command pwd to check the current directory:
ftp> pwd
257 "/support/incoming/"

REMARK: For security reasons the command ls cannot be executed

Exit from ftp site:
ftp> quit

Inform Support that the file has been uploaded in the following location:
ftp://ftp.oracle.com/ftp/anonymous/support/incoming//filename

vnetd + Oracle RMAN restore from tape using NetBackup

I had a unique issue when working on a retore using rman, the issue was the restore was started with 10 channels and the restore was happening with one channel and the other channels were not reading any data and finally failed with “cannot connect socket” error from netbackup side.

The rman side failed with a generic MML error as below.

ORA-19624: operation failed, retry possible
ORA-19507: failed to retrieve sequential file, handle="AAAPRD_dbf_sokhhtgt_689501725", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file


This issue went for a day or two and finally with the help of a system admin we resolved the issue.


The issue was like below :

1.The database server failed to establish more vnetd connections from master/media servers of Netbackup.

1. vnetd is used by default from NBU 6.0 and up. So NetBackup uses the firewalled configuration even if you do not have a firewall.

2. If vnetd fails NBU failback to normal deamon port connection (bpcd, bpbrm etc).


This was finally fixed by changing the per_source value in /etc/xinetd.conf from 10 to UNLIMITED after whcih all the channels running fine.

Oracle 10.1.0.5 database opened with 10.2.0.4 home

We had a case where a dba had opened a 10.1.0.5 database with a 10.2.0.4 Home and with the init parameter compatible=10.2.0.4 by mistake.

After that the dba attempted to start the db using 10.1.0.5, which resulted in the following error as the compatibility information had been written allover
the controlfiles, datafiles etc.


/rdbms/v10.1/dbs >sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jun 4 05:37:07 2009

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1323752 bytes
Variable Size 164351256 bytes
Database Buffers 436207616 bytes
Redo Buffers 2097152 bytes
SQL> @ /tmp/ctrl.sql
CREATE CONTROLFILE SET DATABASE "RETRDEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01130: database file version 10.2.0.4.0 incompatible with ORACLE version
10.1.0.4.0
ORA-01110: data file 1: '+DG8_DEV/retrdev/datafile/system.256.629212191'



Since compatibility parameter cannot be rolled back in this case, we dint have any backups as this was a developement environment.To workaround this we had

to do the following:

1.Upgrade the database to 10.2.0.4
2.Export the database.
3.Recreate a 10.1.0.5 database and import it.

So we have to be carefullwhicle settign such parameters.

Oracle 10g RAC check/modify private interconnect information

Query the private interconnect information from the database:

SQL> select * from gv$cluster_interconnects ;

INST_ID NAME IP_ADDRESS IS_ SOURCE
---------- --------------- ---------------- --- -------------------------------
1 eth0 192.168.124.186 NO OS dependent software

Query the private interconnect information using oifcfg:

$ oifcfg getif
eth1 192.168.128.0 global public
eth2 192.168.127.0 global cluster_interconnect

Check the interconnect information from the alert log

And also the alert log will throw the information during start up just after displaying the non-default init parameters:

open_cursors = 300
pga_aggregate_target = 73400320
Cluster communication is configured to use the following interface(s) for this instance
192.168.124.186


You can check the available interfaces using

$ oifcfg iflist
eth0 192.168.124.0
eth1 192.168.128.0
eth2 192.168.127.0
ib1 10.192.2.0



To change the setings follow the below steps:

delete the wrong settings

oifcfg delif -global eth1/192.168.128.0
oifcfg delif -global eth2/192.168.127.0



oifcfg setif -global eth0/192.168.124.0:public
oifcfg setif -global ib1/10.192.2.0:cluster_interconnect

The new settings would take effect after the database bounce.


Note: The crs picks the interconnect information from the /etc/hosts and the database picks from the configuration of oifcfg.

How to recreate a undo tablespace

There was an issue in the undo tablespace , where the size of one of the datafiles in the undo tablespace(ASM) did not match the size in the control file and I did the folowing to recreate the undo tablespace:

1. Make sure the database was last cleanly shut down.

sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> shutdown immediate

2. mount database in RESTRICT mode, using pfile.

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started. Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.

3. Try to offline drop the bad datafile.

SQL> ALTER DATABASE DATAFILE 'C:\ORADATA\DB\UNDOTBS2_02.DBF' OFFLINE DROP;

*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

or this SQL:

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

4. Use this query to see how many rollback segments were corrupted:

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ NEEDS RECOVERY UNDOTBS2
_SYSSMU20$ NEEDS RECOVERY UNDOTBS2

5. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

Make sure you uncomment “undo_management=AUTO”, and specify you want to use UNDOTBS1 as undo tablespace.

#undo_management=AUTO
undo_tablespace=UNDOTBS1

6. Start the database again:

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora


7. Drop bad rollback segments


SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
…

SQL> drop rollback segment "_SYSSMU20$";
Rollback segment dropped.

8. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————
SYSTEM ONLINE SYSTEM
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1
_SYSSMU21$ ONLINE UNDOTBS1

9. Now drop bad undo TABLESPACE UNDOTBS2;

SQL> drop TABLESPACE UNDOTBS2;

10. Recreate the undo rollback tablespace with all its rollback segments

SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'C:\oradata\DB\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;


11. Change undo tablespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

12. Remove the following line from pfile

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16 $','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

and uncomment “undo_management=AUTO”

undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

13. Shutdown database

SQL>shutdown immediate;

14. Edit initCRM_18.ora, make sure you change ‘undo_tablespace=UNDOTBS2″ to “undo_tablespace=UNDOTBS1″, then start oracle database:


sqlplus /nolog
SQL>connect sys/change@db as sysdba
SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\db\initdb.ora
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

15. Create Undo tablespace:

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'C:\oradata\db\UNDOTBS02.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
SQL>DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

16. Startup database with spfile

SQL> startup;
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Oracle 10g CLONE FROM RAC TO NON-RAC (RMAN)

Database

Target database – PRD10G => This is the source database in RACRecovery Catalog – RMCAT10G (in Machard) => This is the catalog database for RMANAuxillary database(New DB) – TST => This is the target database Non-RAC
Note: Backups are in tape , MML is Netbackup

Steps

1. The first step would be to create a auxillary instance, which will be in no-mount state.To prepare a instance, create a pfile from the spfile of PRD10G.
Eg. Create pfile=’/tmp/initprd.ora’ from spfile;

2. Then edit this pfile to remove all the parameters denoting the Cluster, in our case I commented it as below;
#tst1.__db_cache_size=260046848#tst2.__db_cache_size=260046848....#*.cluster_database=true#*.remote_listener='LISTENERS_tst'*.background_dump_dest='/rdbms/v10.1/admin/tst/bdump'*.compatible='10.1.0.4.0'*.control_files='+DG4_DEV/tst/controlfile/backup.277.629493875','+DG_DEV/tst/controlfile/backup.264.629493875'*.core_dump_dest='/rdbms/v10.1/admin/tst/cdump'*.user_dump_dest='/rdbms/v10.1/admin/tst/udump'*.db_block_size=8192*.db_create_file_dest='+DG4_DEV' ===> Modify it accoring to the file system or ASM diskgroup in the destination server....*.sessions=170*.sga_target=471859200*.undo_management='AUTO'undo_tablespace='UNDOTBS1'
3. Create a password file to login remote to the auxillay instance.

4. Add entries to the Listener.ora file and also add the prd and rmcat10g entries to the tnsnames.ora if they are not available.

5. Create any directories specified in the pfile eg.bdump,udump etc.

6. Use this pfile to startup the new instance in nomount mode
Startup new instance to check if all parameters are correct and for further RMAN operations
SQL> show parameter cluster_database
NAME TYPE VALUE------------------------------------ ----------- ------------------------------cluster_database boolean FALSEcluster_database_instances integer 1
SQL> show parameter thread
NAME TYPE VALUE------------------------------------ ----------- ------------------------------thread integer 0

7. Check the location of the target database datafiles so that we can specify the new names for them, in our case:
SQL> select name from v$datafile;
NAME--------------------------------------------------------------------------------+DG4/prd/datafile/system.260.629141721+DG4/prd/datafile/undotbs1.276.629141721...+DG4/prd/datafile/usermgr_admin.256.629142617
12 rows selected.

8. Connect to RMAN with – PRD as target, RMCAT10G as catalog and TST as Auxiliary instances.

9. From the catalog RC_% Views find the time or SCN that you want to duplicate, In our case it is up to a specific time.

10. Then run a run block similar to this:
run{allocate auxiliary channel c1 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';allocate auxiliary channel c2 device type sbt PARMS 'SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
duplicate target database to 'tst' db_file_name_convert=('+DG4/prd/datafile/','+DG4_DEV/tst/datafile/')logfilegroup 1('+DG4_DEV/tst/onlinelog/redo01.log') size 100M,group 2 ('+DG4_DEV/tst/onlinelog/redo02.log') size 100MUNTIL TIME "to_date('2007-08-08:07:00:00','YYYY-MM-DD:HH24:MI:SS')" ;
release channel c1;release channel c2;}

This will clone a database called tst from prd as a non-rac database.

Happy cloning.

Enabling Trace in Oracle

Tracing a session is a imporatant phase of a problem analysis for a query,job in Oracle.There are various methods of enabling trace for sessions.Following are a few methods for enabling trace.
To check if any trace is enabled in the current database, the following query can be used:
select * from DBA_ENABLED_TRACES ;

1. Enable trace at instance level
Start trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = TRUE;
Stop trace:
SQLPLUS> ALTER SYSTEM SET sql_trace = FALSE;

2. Enable trace for the current session
Start trace:
ALTER SESSION SET sql_trace = TRUE; (or)EXECUTE dbms_session.set_sql_trace (TRUE); (or)EXECUTE dbms_support.start_trace;Stop trace:
ALTER SESSION SET sql_trace = FALSE; (or)EXECUTE dbms_session.set_sql_trace (FALSE); (or)EXECUTE dbms_support.stop_trace;

3. Enable trace in another session
Find out SID and SERIAL# from v$session. For example:
Start trace:
EXECUTE DBMS_MONITOR..start_trace_in_session (SID, SERIAL#);
With Waits and Binds
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE);
With Waits
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(SID, SERIAL#, waits=>TRUE, binds=>TRUE);

Stop trace:
EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);