Hacking user passwords in Oracle 11g

The title of this post is a bit misleading. I am not a hacker and also this blog is not about how you can cheat on Oracle. The title comes from a colleague who asked me how he can get his password back if he forgets. The simple answer is that you can't. There is an easy way to get back your access to database but there is no way to get back your old password if you forgot. The reason is that Oracle does not store passwords rather it stores the hash value of passwords. There is no known way to decrypt this hash value and mechanism has never been shared by Oracle (and rightly so).

So if you have forgot your password, you can easily ask your DBA to reset it. The DBA will use the following command to do so.

SQL> alter user identified by ;

Regarding hash values being stored inside database, if you have database version 10g or earlier you can use the following command to get hash value.

SQL> select username,password from dba_users where username='SCOTT';

The problem with this approach was that any user can view at least his own password's hash value using the following command.

SQL> select username,password from user_users where username='SCOTT';

Starting from 11g Oracle has changed this. The hash values for passwords are not visible in password column of *_users views. The column will be blank. The hash values are only visible to DBAs now through a new view user$. 

SQL> select name,password

  2  from user$

  3  where name='SCOTT';

NAME       PASSWORD

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

SCOTT       F894844C34402B67

It is highly recommended that normal users should not have access to this view.

Fusion Middleware RCU fails to create OIM schema

This is what happens when you are trying to install Oracle IAM 11gR2. Normally it happens when you are installing Identity Management suite on Oracle Linux 6, 64 bit version. However in my case I faced this error on 32 bit. It can happen on any schema related to IAM suite and is not just limited to OIM schema. According to Oracle support its a bug which has no clear solution and they are working on it. Check Article id 1469169.1 for more details.

However there are several workarounds available which may or may not work for you. Here they are:

• Make sure that you have all required packages installed. Here is the list of packages that you must have. 
• Download the Windows version of RCU and run it from your Windows desktop.
• Try to run RCU from a 32 bit platform if you are installing on 64 bit server.
• Use an older version of Oracle Linux like EL 5.

In my case the first workaround worked.

Accessing NTFS partition from Linux

Linux does not offer the default support for NTFS partitions. Although you can natively access them but you can't actually use them especially in read/write mode. I required this because my VMs were originally created in Windows and I can't create FAT partitions for them because it has limitation on size of file.

No brainer. I just googled for sometime and found a solution which did not worked in its entirity but with some tweaking I made it to work. You can find the original article here. But here is what worked for me.

You need ntfs-3g drivers to access and modify data on NTFS partitions. To install this driver you first have to create a yum repository for this. The repository is as expected located under fedora project.

Log in using root and execute the following commands to create repository.

$ su
password:
# cd /etc/init.d/yum.repos.d
# wget http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

The above command will download the rpm file which you will install to create repository.

# rpm -ivh epel-release-6-8.noarch.rpm

Once the repository has been setup, you can now use yum to install the actual driver.

# yum install ntfs-3g

The driver is now installed and you are ready to mount your NTFS partition. Use the following to locate the ntfs partition.

# fdisk -l

In my case I wanted to mount parition located at /dev/sda3. Create a folder to serve as mount point.

# mkdir -p /home/oracle/ntfs

Use the following to mount the drive.

# mount -t ntfs-3g /dev/sda3 /home/oracle/ntfs

Identity columns to debut in 12C

Identity columns are set to debut in the upcoming new version of oracle database. You no longer had to create sequence and write before insert triggers to auto update primary key columns. The following syntax is set to work in 12c and will do everything for you.

SQL> CREATE TABLE TEST (
ID GENERATE AS IDENTITY COLUMN,
.
.
.
);

Behind the scenes oracle will create the sequence and will update the column when new records are entered.

This is not something new or out of the box as other databases like SQL SERVER and MySQL have this feature for quite sometime. Oracle however relied on the hard programmatic way until now. 12c is set to change that.

Custom domain and oracle dba from Nexus 7

About a month I decided to change the title of this blog from "Imran's Oracle blog" to "the amazing oracle". It wasn't just a name change but rather the idea change behind this blog. As I have another space of mine where I can share non oracle stuff so I decided this to make something purely oracle related. The new name voiced out from no where when I got rman duplication work like a charm on very first attempt.

Most pleasant thing about it was there was no such domain as yet. So I jumped on it and here it is finally this blog is going in a new direction with its own new name. The platform will still be blogger and I have no plans of changing it in near future. 

Also, I bought Nexus 7 about a month ago. I also recently moved from Windows to Linux and when I got WiFi working on it, I thought to access Linux shell from Nexus 7. After all it requires is a ssh client like Putty. 

I searched and found many ssh client for Nexus 7 and installed ConnectBot. Everything worked so smoothly and within 15 mins I was accessing linux shell from Nexus 7 while laying in my bed. 

When you can access linux shell, you can do just about anything with linux. So I jumped straight to SQL*PLUS and started to play with oracle.  Even took a backup of database using rman. :)

Getting WiFi networks accessible on oracle linux 6

I recently changed my home laptop from being a Windows machine to a Linux machine. It took more time then I expected because firstly I was not using a distribution which was good for Desktop like Ubuntu or Mint and secondly because I had Windows 8 installed on my laptop and was keen to keep it. Somehow I managed to install oracle linux 6.3 on my laptop but I had to revert back to Windows 7 from Windows 8.

Linux does not include WiFi drivers especially the enterprise linux which is basically meant for database servers connected via LAN cables. I though it like that as well and suggested to myself that I should treat this as my mainframe server and stick to my table every time I need to work on it.

But I was not going to settle on this for too long. So I went on a mission to make WiFi networks accessible. After about an hour of Googling I found the link of a forum which listed a few simple steps to make it work. Not very hopeful I tried those steps and restarted the machine and vola it worked.

You can check that specific forum thread here.

I have nothing but all praises for the person suggesting and solving my problem. For your convenience here the steps.

1. Download the firmware files by clicking this.
2. Save the file into any location.
3. Open a terminal and set your working directory where you have placed the file.
4. Extract the files using the command: tar xjf broadcom-wl-4.150.10.5.tar.bz2
5. Navigate to extracted folders: cd broadcom-wl-4.150.10.5/driver
6. Switch to root user: su (password for root will be prompted).
7. Issue the command: b43-fwcutter -w /lib/firmware wl_apsta_mimo.o

The above steps are tested on Oracle linux 6.3 but they should work on any oracle, redhat, CentOS or Fedora based distributions. The forum is basically for Fedora. 

RMAN, new configuration parameters in 11g

Configuration parameters define the overall environmental behavior of rman. You can set these parameters to automate things as well as define bevaviors with repect to different commands. In 11g there are two new parameters which are basically two new features. You can use the following command to list all configuration parameters with their default values.

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

I have highlighted both the new parameters. First one, as you may have guessed RMAN now allows you to compress the backup sets generated via rman. The configuration allows you to set the default compression algorithm which you want to use while taking backups. By default it is set to basic which you can change depending on your requirement. To see the list of algorithms available you can use the following command. Note the description column which states which algorithm is suited for what situations.

SQL> select algorithm_name,algorithm_description,is_default
  2  from V$RMAN_COMPRESSION_ALGORITHM;
ALGORITHM_NAME       ALGORITHM_DESCRIPTION  IS_
------------------------------ -------------------------------------------------- ---
BZIP2       good compression ratio  NO
BASIC       good compression ratio  YES
LOW       maximum possible compression speed  NO
ZLIB       balance between speed and compression ratio  NO
MEDIUM       balance between speed and compression ratio  NO
HIGH       maximum possible compression ratio  NO

6 rows selected.

You can change the value of parameter using the following command.

RMAN> configure compression algorithm "low";
new RMAN configuration parameters:
CONFIGURE COMPRESSION ALGORITHM 'low' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
new RMAN configuration parameters are successfully stored
RMAN> 

The other parameter allows you to set the archivelog deletion policy. As name suggests that this setting when set will cause automatic deletion of archivelogs. This is a very handsome feature in itself and will require another post for full details.

Enough to state is that you can set policy like delete archivelogs after two backups. RMAN will delete archive logs which have been backed up twice. 

Create restore points for specific SCN and one that preserve in 11g

Restore points were introduced in 10g as part of amazing new flashback technology. You can check my earlier post about them here if you want to know more about them first hand.

In 11g there are two minor tweaks in the concept. Normally when you create restore point, it is created on current SCN. Now you can customize this behavior. You can create restore point to some earlier SCN. The only rule is here that SCN should exist in first place.

For example, issue the following command to know the current SCN of database.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1017119

Suppose we want to create restore point on 1017000 instead of current one. Following command will do that.

SQL> create restore point restore1 as of scn 1017000;

Restore point created.

You can also use time-stamp instead of SCN.

SQL> create restore point restore2 as of timestamp to_date('10-Feb-2013');

Restore point created.

The second concept is of perseverance. Oracle has a retention policy for keeping restore points and once that retention point is reached, the restore points created earlier are deleted in order of their creation. Now you can control that as well. Suppose you want to create a restore point and also want it to be preserved until and unless you drop it yourself, you can use the following command.

SQL> create restore point restore3 preserve;

Restore point created.

You can check which restore points are preserved and which are not by using the following query.

SQL> select name,preserved from v$restore_point;

NAME                           PRE

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

RESTORE3                       YES

RESTORE1                       NO

RESTORE2                       NO

 

Difference between normal and standby database duplication

RMAN is a wonderful tool and has gone distances from being just another backup and recovery option. It simplifies a lot of things and has a lot of role to play in technologies like RAC, Data Guard etc. 

To setup a data guard environment the first thing you need is the another copy of your production database. RMAN duplication is the best way to get this done.

However normal duplication is not what you require. As you are using duplication for a standby database which means that your end database will still be dependent on your production database. This requires changes in how database is structured. As database structure will be different, so will be the control file. You can create a copy of control file for standby, backup it and register with RMAN so that RMAN can use it using one single command.

RMAN> backup current controlfile for standby;

Once you are done with that you can do all other things like creating pfile, instantiating instance and creating directories etc like you would do for normal duplication. Your target database will be your primary instance and auxiliary database will be your standby instance. Connect to both of them and issue the following command to start the duplication process.

RMAN> duplicate target database for standby dorecover;

The above command will duplicate database, recover it to the latest SCN and then will bring it up in mounnt state. It will not open the database and neither it is required because nature of database will be standby. 

Create pfile/spfile from memory in oracle 11g

Starting from version 11g of database, oracle now allows you to create pfile or spfile from memory. As you know both of these parameter files serve the similar purpose of instantiating the database instance. It was possible to create one file if you had the other one available. But now you can create either of them even if you don't have any of them but you have database up and running.

The simple command to do so will be:

SQL> create pfile from memory;

for spfile:

SQL> create spfile from memory;

The file created this way will list the values of 150 essential parameters. It is not necessary that you had provided an explicit value for any of them or not, oracle will create file with those 150 parameter values along with their values either one you provided or the default ones.