Basic Restore/Recover in SQL Server/Oracle

0

Oracle

SQL Server

 

Creating Stored Procedures SQL Server/Oracle

0

SQL Server (2008 R2)

Oracle

References:

http://technet.microsoft.com/en-us/library/ms187926.aspx

 

Drop object if it already exists (Oracle/SQL Server)

0

SQL Server

To recreate a table (drop if exists, then create)

if object_id(‘dbo.yourtable’) is not null
begin
drop table dbo.yourtable
end

create table ….

OR

To alter a table if it exists, otherwise create it – this is preferable as it preserves permissions

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N’[dbo].[vw_myView]‘))     EXEC sp_executesql N’CREATE VIEW [dbo].[vw_myView] AS SELECT ”This is a code stub which will be replaced by an Alter Statement” as [code_stub]‘ GO

ALTER VIEW [dbo].[vw_myView] AS SELECT ‘This is a code which should be replaced by the real code for your view’ as [real_code] GO

 

Oracle

declare
c int;
begin
select count(*) into c from user_tables where table_name = upper(‘table_name’);
if c = 1 then
execute immediate ‘drop table table_name’;
end if;
end;

OR

BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE mytable’;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;

OBIA/OBIEE/WLS log locations

0

One of the most irritating tasks I deal with when troubleshooting our new OBIA stack is locating the numerous and poorly named logs that record the events for the various system components.

Weblogic:

$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/<server name>/logs

OBIEE:

$MIDDLEWARE_HOME/instances/instance1/diagnostics/logs/<component>

 

References:

http://www.biconsultinggroup.com/oraclebiblog/2011/3/14/log-files-in-obiee-11g.html

http://oraclebisolutions.blogspot.com/2012/12/obiee-11g-file-locations.html

RMAN force archivelog deletion without backups

0

For those times when you just need to clean out old archivelogs and don’t have the luxury of taking backups first:

Either:
From RMAN: delete force archivelog completed before ‘sysdate – 7′;

Or:
From OS, delete the old archivelogs
– find /path/to/files* -mtime +5 -exec rm {} \;
From RMAN:
– crosscheck archivelog all;
– delete expired archivelog all;

‘OraOLEDB.Oracle.1′ provider is not registered on the local machine

0

Ran into this issue recently after moving to a new Win 7 system – I’d installed the Oracle 32-bit full client and had been running Toad and SQLPLUS successfully, so I knew the client itself (and the usual slew of environment variables, etc.) was working more or less properly.

Issue occurred when I attempted to load an SSIS package from a different system on which both the 64 and 32 bit clients had been installed, so I’d initially planned on installing the 64-bit client, but after searching around a bit it turned out that was unneccesary as the fix turned out to be surprisingly simple:

C:\>cd oracle\product\11.2.0\client_32\BIN
C:\oracle\product\11.2.0\client_32\BIN>c:\Windows\System32\regsvr32.exe OraOLEDB11.dll

Expanding VirtualBox volume in Linux guest OS

0

Simple set of instructions placed here to remind myself how to expand volumes of the Oracle VMs I use for testing, development, playing around, and general shenanigans. While the example is using an Oracle Developer Day vmdk, this should apply equally to any other vmdk linux VM.

Many of the basic Oracle Developer Day VMs downloadable from the Oracle site and pre-installed with the latest and greatest technologies are often woefully undersized, sometimes sporting an Oracle Base volume of less than 10G. The process for expanding these volumes is relatively simple, consisting of (shutdown the VM first, of course)

1) Converting the downloaded .vmdk disk file to a .vdi (VirtualBox cannot expand a .vmdk at this time)

VBoxManage.exe clonehd "Oracle_Developer_Day-disk2.vmdk" "Oracle_Developer_Day-disk1.vd2" --format vdi
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Note that I’m only cloning the disk2 of the download, as this is the disk on which the /home volume is mapped

2) Expanding the generated .vdi to an appropriate size for your needs

VBoxManage.exe modifyhd "Oracle_Developer_Day-disk2.vdi" --resize 40000
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

3) Remove the vmdk from the VM in settings->storage and add the converted and expanded vdi
2013-08-29_1155
2013-08-29_1156
2013-08-29_1156_001

4) Expanding your volume to take advantage of the new space
Note that this method will be dependent on the tool you use to manage volumes as well as whether logical volumes and groups are defined or as is the case with the Developer Day VMs, just partitions.

I will be using the free download GParted to manage this:

a) Download GParted Live CD and mount it in your VM’s settings (make sure that GParted is the only CD/DVD mounted):

2013-08-30_0940

b) Start your VM and immediately hit F12 to get into the Boot Menu, select c) CD-ROM

c) You should now be in the GParted launcher, select the first option and then accept defaults (Don’t touch Keymap, language, etc.)

d) Once GParted loads, select the partition you are going to extend:

2013-08-30_0944

e) Then right-click and select resize, after which dragging the bar to add all of the new space you plan to allocate:

2013-08-30_0945

f) Hit apply, then once that’s complete restart the system

2013-08-30_0945_001

Oracle Developer Day VMs and scp

0

If you’ve ever tried to use scp to connect to an Oracle Developer Day VM you may have encountered an issue where, after entering the remote password for the target VM you get the message:

oracle@localhost]$ scp test.tgz oracle@192.168.56.104:/home/oracle
oracle@192.168.56.104's password: oracle 
<strong>All passwords are oracle or noted if otherwise.</strong>

And nothing happens, no files get copied, no error messages appear.

The issue is that in the Developer Day VMs the .bashrc and .bash_profile (in /home/oracle) contain commands that output to the console, and this is problematic for scp.

The simple solution is to comment out the following lines (note that this needs to be done only on the remote VM, ie. not the VM from which you are intiating scp):
/home/oracle/.bashrc

#cat ~/Desktop/README.txt
#/sbin/ifconfig | grep "inet addr"

/home/oracle/.bash_profile

#echo IP Address is:
#/sbin/ifconfig | grep "inet addr"

Oracle’s response to serious issues with their software: I don’t know, try some stuff?

0

The latest and greatest from those incredible fucktards at Oracle Corp. – how’s this for fun, release a ‘security update’ with a major breaking change, then when people desperately call your expensive support lines to patch your patch, give them the incorrect patch number to install. A day later, it turns out your patch on top of your security update has STILL left a major breaking change (to Materialized Views, no less, which are already buggy and a pain in the ass to deal with – note to Oracle: an Invalid Object should not be synonymous with This MV hasn’t been refreshed in a day; also, don’t fucking tell me to rewrite my SQL using Oracle’s NON ANSI compliant (+) notation because MVs have ‘problems’ with ANSI joins). Now when people contact you complaining that the patch on top of the patch that you provided is STILL NOT WORKING, give them yet ANOTHER patch. For added fun, make this patch incompatible with the previous patch, so people need to perform a patch rollback (something even Burleson calls ‘buggy’). And for 10x the fun, make this impossible to do with your ‘cloud-enabled’ pile of shit aka EM 12c and then act surprised that people are actually trying to use EM for patching in the first place.

Listing all events set in Oracle 11g

0

A recurring pain in the side for many DBAs is that while Oracle makes it relatively easy to set session or system wide events (if you consider the unintuitive and semantically garbage statement “alter system set events ’10046 trace name context forever, level 12′” *easy*) yet makes it well-nigh impossible to actually see what has been set and is running in the background. The conventionaly solution was to use the DBMS_SYSTEM.read_ev procedure and iterate through codes 10000-20000 in order to catch most of the numerically set events, but this is obviously an inelegant and somewhat shotgun approach and furthermore (and more importantly) doesn’t appear to catch ‘named’ events like ‘sql_trace’, etc.

So without further ado, here is the way to list all set events in an Oracle DB:

select * from dba_system_events;
HAHAHA, just kidding. That would make way too much sense

Ok, for real this time:
select * from table(DBMS_SYSTEM.LIST_EVENTS);
Got you again, that almost sounds reasonable, right?

Nah, the real way to list events is to use the horrible oradebug command:
1. identify the session

   SQL> select p.pid, p.spid, s.username  
        from v$process p, v$session s  
        where p.addr = s.paddr;  

2. Attach to the desired session

 SQL> connect / as sysdba
   SQL> oradebug setorapid <pid> 
   - OR -  
   SQL> oradebug setospid <spid> 
   ...
SQL> oradebug eventdump <level> 
session - Dump session group's event settings  
process - Dump process group's event settings  
system  - Dump system group's event settings(Ie the instance wide events)

Note: level needs to be specified in lowercase !
— OR –

SQL> alter session set events 'immediate eventdump(<level>)'; 

SESSION - Dump session group's event settings 
PROCESS - Dump process group's event settings   
SYSTEM  - Dump system group's event settings(Ie the instance wide events

Why it’s so hard to simply come up with concise, intuitive procedures like DBMS_SYSTEM/DBMS_SESSION.SET_EVENT/LIST_EVNTS(@name, @level, etc.) is beyond me but I guess when you’re a $7/hr developer for oracle clean and modular coding is probably not your forte.

Go to Top