Friday
Feb082013

Oracle - INSERT multiple values

Keep forgetting this syntax and it's a shame because it saves precious keystrokes:

insert all
into ryanboyertable1(mycol1, mycol2, mycol3) values ('val1','val2','val3')
into ryanboyertable1(mycol1, mycol2, mycol3) values ('val4','val5','val6')
into ryanboyertable1(mycol1, mycol2, mycol3) values ('val7','val8','val9')
select * from dual;

 

 

Friday
Feb082013

SSIS Error Connecting to Oracle

When faced with this:

 

[OLE DB Destination [50]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "MyOracleSid.ryanboyer" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

 

Try this:

 

  • Go into package properties and change 64 bit runtime mode to false.
Things might be speedier if you set this as well:
  • Set connection properties to retain connection

 

 

 

Thursday
Aug162012

What's filling up the archivelog file?

Found a post out on the interwebs regarding searching for the highest contributor to Oracle archivelog growth here.

Basically feed the name of the .arc file to a shell command. Where "TALLARICO_1234_45678.arc" is the name of my file:

 

Friday
Jan142011

RMAN - restoring tablespaces/datafiles

  • Restore a datafile in "Recovery" status
  • Restore a single tablespace
  • Fix these errors: RMAN-00571,RMAN-00569,RMAN-00571,RMAN-03002,ORA-00283,RMAN-11003,ORA-00283,ORA-01111,ORA-01110,ORA-01157,ORA-01111,ORA-01110

I added a few files to a tablespace in trouble. Months later I went to restore the database on another server for a refresh. I didn't remember to call out the newer datafiles in the recovery script I wrote for that refresh.

The database restored, but users soon reported odd errors in the applications hitting it.

Oops.

I'd already done a resetlogs, so I was starting to get a sinking feeling.

Happily I was running Oracle 10.2.x... so I could restore through the resetlogs.

Here's what I did:

  1. Identify which tablespaces and datafiles were offline: In OEM (emctl start dbconsole will give you the URL) I navigated to Administration > datafiles and looked for any datafiles that did not have a status of ONLINE
  2. Once I identified those I jotted down the TABLESPACE they were a part of and the MISSING000## file name Oracle had assigned it.
  3. I renamed the datafiles via SQLPlus from MISSING000## to its legit name
  4. I ran a (base) restore and subsequent recover of the datafile
It looked something like the following

Note that:

  • My backup files were already cataloged
  • The tablespace is ryan_boyer_tablespace
  • The missing datafile is datafile #25
  • The missing datafile should be called "ryanboyer_indx03.dbf"
  • Oracle is calling the missing file MISSING00025
  • The datafile should reside in /u03/oradata/ryanboyer/...
First take the tablespace offline (from RMAN execute like so: sql 'sql command'; ): Now try a recovery/restore in a run block (also executed from RMAN):

In my case the run block failed with the following:

I entered SQLPLUS to get the MISSING0025 datafile renamed to what it should be, ryanboyer_indx03.dbf

I was greeted with Tablespace altered.. Phew.

Now I went back to RMAN and re-ran my restore/recovery:

I was greeted with output similar to the following:

Double *Phew*.

Now I bring the tablespace back online (shown also from RMAN):

Honestly though I was greeted with errors indicating that datafiles that were also a part of that tablespace needed to be restored prior to the tablespace successfully being brought online. Only after repeating the above steps for each datafile in the affected tablespace was I able to bring the tablespace online.

Please comment if you find any of this helpful.

Sunday
Nov212010

Help! Oracle database won't shut down

The other day I'm trying to shut down my Oracle database and it *will not* go down gracefully.  I'm loathe to pull the plug on the server, so what's a dba to do?

  1. Stop the listener - you can at least prevent others from connecting to the instance that you're trying to shut down:  bash-3.00$ lsnrctl stop
  2. Get a list of processes hitting the database.  You'll need that before you start killing them off one by one:  bash-3.00$ ps -eaf|grep LOCAL
  3. The output from step two will have the processid in the second column.  Jot a few down and then start to
  4. Kill the processes.  Take the second column's output from step #2 and execute the following:  bash-3.00$ kill -9 396 - where "396" is the processid you wish to kill.
  5. Enter into SQLPlus and connect as SYSDBA:  SQL> conn /as sysdba - you'll receive "Connected to an idle instance"
  6. Shutdown the database via SQL> shutdown immediate You may be given the following:

ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted

    7. Reconnect to SQLPlus via sysdba

    8. Execute a startup force command.

    9. Don't forget to start the listener back up

 

The whole gut-wrenching thing may look like this:

SQL> conn /as sysdba
Connected to an idle instance.
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
SQL> conn /as sysbda
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where ::= [/][@] | /
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
SQL> startup
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup force pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/Tallarico.ora' force nomount;
ORACLE instance started.