Change sizes of all redo logs using one shell script

I had this rather surprisingly difficult puzzle of changing sizes of all members of both redo log groups this past weekend. And that too using one shell script. There are two challenges here.

First is the fact that there is no resize option available for redo logs and my database had only two redo log groups which is the minimum requirement. Oracle writes on all members of a redo log group simultaneously and once it is full it switches to the next group. In order to change to the size of the members of any redo log group you had to drop that group first and then add again with the desired size. Having only two redo log groups means that you can't drop any of them; Oracle won't let you do that.

The solution to this first problem is to add an interim redo log group, drop the first redo log group and recreate it with desired size, then drop the second redo log group and recreate it and finally drop the 3rd interim redo log group.

Oracle database 12C Installation on Linux 64bit

Despite a major architectural change in the product design the installation part of 12C database remains mostly the same. The only notable difference is when you create a database you create two different types of databases.

CDB, which is basically a container database and will not hold any user data and

PDB, which is pluggable database and can be more than one. PDBs will actually hold the user data and ideally there should be one PDB per application (adhering to multitenant architecture).

Here are the complete installation steps performed on Virtual box running Oracle Linux 6 64bit.  

12C is here...

Like the arrival of "Man of Steel" or "Dark Knight Rises" or Pakistan vs India cricket match, the anticipation for 12C was by no means lesser. 

Its finally here and you can download it using the link. 

Finally there is a chance to play around the buzzwords circulating in my head for almost seven months. Multitenancy, Pluggable databases, first database for cloud etc etc.

If you are unable to wait then here is the installation guide for Linux. 

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. 

DBCA could not detect the database

If you have created the database manually or you have duplicated the database using RMAN duplication, you may have encountered this error. Apparently there won't be any errors anyway. You will be able to connect to your database using SQL*PLUS or any other client tool and perform all the normal operations. But DBCA and other graphical tools like DBUA won't be able to detect that database exists.

Another problem caused by this is that you won't be able to configure database for EM control. Not appearing in DBCA also means that you cannot delete the database completely in one go and configure several other options. 

Enough problems....

But the reason and solution is simple. Every database not created using DBCA will not be registered in "oratab" file. The file is located in "/etc" folder and is used by database startup and shutdown scripts. But it is also used by GUI tools to locate what database services are running. So just add an entry for your database in the file and save it. Run the DBCA again and all will be OK.

Typical entry in /etc/oratab entry will look like the following.

ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:N

RMAN, creating recovery catalog

Recovery catalog is oracle's recommendation to use with rman. If you don't use recovery catalog then all database backup/recovery related information will be stored inside the control file of your database. However it is strongly recommended that you use recovery catalog and take regular backups of catalog as well. Here is how you can create recovery catalog. The example assumes that you are creating recovery catalog schema inside your target database.

The process is simple. You will create a tablespace to hold catalog information, a user who will own the recovery catalog and then create the catalog while logging into that schema through rman client.

Here is the process in practice.  


SQL> create tablespace ts_catalog
  2  datafile '/u01/app/oracle/oradata/VSTDB01/catalog.dbf'
  3  size 100M
  4  autoextend on
  5  maxsize 400M;

Tablespace created.

SQL> create user rman identified by rman
  2  default tablespace ts_catalog;

User created.

SQL> grant connect,resource,recovery_catalog_owner to rman;

Grant succeeded.

$ rman catalog rman/rman@VSTDB01

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 20 13:57:58 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace ts_catalog;

recovery catalog created

RMAN> connect target /

connected to target database: VSTDB01 (DBID=2542636510)

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN connect to Auxiliary Fails With ORA-01031: INSUFFICIENT PRIVILEGES

If you are getting this error when you are trying to connect like this

$ rman auxiliary /

then it may be because your OS user is not added to either DBA or OINSTALL system groups. This is easy to resolve. But if you are getting this error while doing the below

$ rman auxiliary sys/oracle@AUX1

and you know that you have configured everything like Listener and TNSNAMES etc then the resolution is a bit tricky. The auxiliary database is mostly used when you are duplicating a database or something. There are situations where you must connect to your auxiliary database using service name like when you are duplicating from active database without any backup.

In these situations you can't change your password file using SQL*PLUS because database will be in no-mount state. Here is the two step solution.

1. Copy your target database's password file and rename it so that the instance names are replaced.
2. Make sure that instance and service names are same in the following locations and files. They must be same and at the same time all of them must be in UPPER case

Here are the locations where you need to verify and check the instance and service names.

• The db_name parameter in parameter file (init.ora)
• Instance name in Password file's name
• SID_NAME in Sid description section of Listener.ora
• Service name in tnsnames.ora entry

$ rman auxiliary sys/oracle@AUX1 

will work now.

Instantiating the ASM disk failed error

IT Problems can be very tricky sometimes. While installing ASM on Linux today I was having an issue with instantiating the disks. Everything seemed OK and the following command was giving me an all OK status

$ oracleasm status

But when I tried to mark the disks as ASM disks I continued to get the message that "Instantiating the disk failed". The following command was failing.

$ oracleasm createdisk DATAVOL /dev/sdb1

For two hours I was stuck with Google but unable to resolve the issue. The simple solution that came out was that SELinux was stopping this. I wasn't aware that SELinux on my host was enabled. I disabled the Firewall while installing the operating system but forgot about SELinux. I disabled and it worked like a charm.

This is where most of the IT problem originate from. When you disable the Firewall and SELinux on a production system, the System Admin may want to kill you. But if you don't you will be hard pressed to find a solution that is suitable for both DBA and System Admin. 

Fortunately mine was a personal test system and was not required to run away from any System Admin. 

Installing Linux packages for oracle ASM installation

I have been playing around with ASM quite often now a day. The thing that confused quite a lot while installing ASM was the pre installations tasks. You need several Linux packages before you can start the Universal Installer.

What needs to be understood is that when you use ASM as your storage type for database, the operating system does not have any control over the disks that are being used. The packages which are required to be installed basically help Linux mark those disks as Oracle ASM disks. You can generally relate this to a File System for better understanding and packages help to build that file system. So here is what you need to install and how you can do it using Yum.

• oracleasm
• oracleasm-support
• oracleasm-lib

You are not required to install the first package if you are using the latest version of oracle linux. It is built into the Linux Kernel. To install the second one you can use the following command.

yum install oracleasm-support

The third package is not yet available in Yum repositories and you will have to download this from oracle's website separately. You can download it from here and save it to disk. Once saved, navigate to the directory where you have saved and issue the following command.

rpm -Uvh oracleasmlib-2.0.4-1.el6.i686.rpm