EM 12c – find a user / schema in all databases

When deprovisioning users (for reasons of employment termination, consulting engagement completion, etc.) we must often lock or completely remove user accounts in potentially numerous databases.

If these databases are tracked within Enterprise Manager Cloud Control 12c, there is a fairly handy view that drastically improves this process, namely the SYSMAN.MGMT$DB_USERS view in your Grid Repository database

 

Reference: http://docs.oracle.com/cd/E24628_01/doc.121/e25161/views.htm#BABCECCB

Oracle user privileges on directory

Keep on forgetting this fact, so I’m committing it to the internet’s memory (as mine is suspect).

To view user privileges on a directory, use the dba_tab_privs view.  As this is Oracle, of course it would be ridiculous to expect that the “tab” in the view stands for table…  One would almost think there would be a dba_obj_privs (as with dba_obj_audit_opts) but of course not.  Don’t let the column name ‘TABLE_NAME’ fool you either, TMPDIR below is actually just a directory.

 

 

 

Creating a DSN on Windows to Oracle 11

Fairly simple process, but wanted to note it for future.

First, downloaded the Oracle InstantClient (Basic) and InstantClient ODBC packages:

instantclient_download

Then created a folder (C:\oracle_instantclient) and extracted the two downloaded zip files inside the folder.  Both will create and extract into a folder named instantclient_11_2.

While here, I also created a tnsnames folder for steps below.

instantclient_extract

Inside the instantclient_11_2 folder I ran odbc_install.exe – Oracle could do a better job of feedback, as all I saw was a flash of a command prompt, but whatever…

Then in Environment Variables I added C:\oracle_instantclient\instantclient_11_2 to the PATH variable, and created a TNS_ADMIN variable pointing at my tnsnames folder (C:\oracle_instantclient\tnsnames).

path_variable tnsnames_variable

I created a tnsnames.ora file inside of the tnsnames directory, and added a tns entry

tnsnames

 

I’m using the 32 bit client, so instead of using the default ODBC administrator, I launch the 32 bit version from C:\Windows\SysWOW64\odbcad32.exe.

 

I can now see and use the ODBC driver for Oracle:

odbcdriver

 

 

 

 

Altering columns in SQL Server and Oracle

While a great deal of DDL is common across these two platforms, altering individual columns varies somewhat:

SQL Server

 

 

Oracle

 

Setting the editor in SqlPlus

By default the “ed” command in SqlPlus invokes the corresponding default editor in Linux.

Exit with “q”

A better solution is to set the editor in SqlPlus – in this case I’m using “vi” as my editor of choice:

Once done, invoking “ed” places you into a vi edit – the saved file can then be run via the usual:

To persist this setting, it can be placed in glogin.sql or login.sql

References:

http://www.adp-gmbh.ch/ora/sqlplus/login.html

 

Adding constraints in Oracle and SQL Server

Creating primary, foreign, and unique constraints in Oracle and SQL Server is identical
Either in table creation:

 

Or using ALTER

 

Similarly for Unique and Foriegn Keys:

 

 

Disabling

 

 

Hyper-V with Windows 8.1 and SSDs

After enabling the native Hyper-V functionality in Windows 8.1 under Windows Features (run appwiz.cpl and Turn Windows Features On or Off to enable this functionality) I downloaded the Windows Server 2012 R2 VHD from TechNet (a somewhat painful process in its own right, but that’s a story for a different day) and the SQL Server 2014 ISO.

A couple of notes on issues I encountered during this process:
- when attempting to launch the ISO I got a Problem Mounting Image error – this turned out to be resolvable by copying the ISO from its original download location to a new location
- in order to load the VHD provided by TechNet (you would think they would provide a full HyperV loadable virtual machine…) I consistently received the error Microsoft Emulated IDE Controller Failed to Power on with Error “This version does not support this version of the file format”
2014-08-01_0028

Resolution:  Issue occurred because disk image was being stored on an SSD – moving it to a SATA drive resolved the problem.

Access SQL Server 2008 R2 in Single User mode

It’s happened to all of us – a SQL Server off on the outskirts of the environment is suddenly acting up and you realize that you don’t have a domain account with administrative privileges and maybe the sa account is disabled for good measure.

No problem, you think – I’ll just start the server in single user mode (with the usual trick of adding -m; to the advanced properties of the service in Configuration Manager) and then connect using sqlcmd.

But then:
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Okay, fair enough, I didn’t make sure the agent was off, so I’ll just turn it off and
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Ok, ok – I’ll use the -m’SQLCMD’ option so that only SQLCMD can connect in case some other services are conn….
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Now this is ridiculous – no one else is connecting with SQLCMD, is this a bug?

“Hey John, check this out – I think I found a bug”
“Did you open the command prompt with Run as Administrator?”
*Opens command prompt with Run as Administrator*
C:\Windows\system32>sqlcmd -S myServer\myInstance
1> select ‘Oh goddammit’;
2> GO
————
Oh goddammit

(1 rows affected)

Basic Restore/Recover in SQL Server/Oracle

Oracle

SQL Server

 

« Older Entries