Category: Oracle

When I was testing for the planned Oracle 11g upgrade, I was fairly excited about the possibility of being able to add an additional dimension to password complexity by introducing case sensitivity. Unfortunately, after some investigation, I found several legacy applications that were still in use in my environment that either had many hard-coded all upper-case passwords or have program functions that changed all casing to upper case. While introducing case sensitive passwords was definitely a goal for my environment, I also understood that the developers responsible for the applications noted above would not have enough time to make the changes to meet my database upgrade schedule. My solution was to temporarily disable the case sensitivity feature in Oracle 11g, thus I could still proceed with my upgrade, while providing the developers an extra month or two to make modifications to their code to comply with the new security model.

To disable password case sensitivity in Oracle 11g:

alter system set sec_case_sensitive_logon=false;

Again, you should carefully evaluate your situation before disabling this new feature found in Oracle 11g. Password sensitivity may be a great tool in safeguarding your data from the unauthorized.

I ran into the need to do this because one of my users performed a big data import, and it was not until he finished that he realized somewhere along the way when he was preparing the data, instead of “2009”, some of the years came out to be “1909”. To fix this in the database, I made use of Oracle’s built-in numtoyminterval() function, which stands for “Number to Year/Month Interval”. The syntax is as follows:

numtoyminterval(n, interval_name)

“n” is the quantity, and “interval_name” is either “year” or “month”. The following example illustrates its basic usage.

select sysdate as now,
sysdate + numtoyminterval(1,'month') as plus_1_month,
sysdate + numtoyminterval(3,'month') as plus_3_months,
sysdate + numtoyminterval(12,'month') as plus_12_months,
sysdate + numtoyminterval(1,'year') as plus_1_year
from dual;

NOW       PLUS_1_MO PLUS_3_MO PLUS_12_M PLUS_1_YE
--------- --------- --------- --------- ---------
20-MAR-09 20-APR-09 20-JUN-09 20-MAR-10 20-MAR-10

Armed with this Oracle built-in function, I simply ran the following update statement to correct the bad data that my user had imported today.

update example_table
set date_goes_here = date_goes_here + numtoyminterval(1,'year')
where date_goes_here between '1-jan-1909' and '31-dec-1909'
and trunc(entry_date,'ddd') = trunc(sysdate,'ddd')
and entry_by = 'careless_user';

Temporary tablespaces are used by the system to sort out things that it cannot do in memory. For example, if you join two very large tables that exceed the memory capacity, Oracle will use the temp tablespace to perform the sorting operation in the temp tablespace. Now, imagine the case in which an user performs a Cartesian join by mistake; the temp tablespace may grow to be a very large size in order to cope with the data. Once the operation is completed, Oracle will not automatically reduce the file size, which what I observed with my Oracle 10g database.

To resolve the issue, I performed the following 6 steps. This solution does not require system restart, but it is recommend that you perform this during slower hours if possible. Temp tablespace does not hold any permanent objects, so I do not back up my temp tablespace, but as always, I recommend that you make certain you have a very recent backup set available to you before you start working, just in case something goes wrong.

1. Create a new temp tablespace

create temporary tablespace temp2
tempfile '/filepath/TEMP02.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

2. Make the new temp tablespace the primary one

alter database default temporary tablespace temp2;

3. Drop the runaway tempfile of the original temp tablespace

alter database tempfile '/filepath/TEMP01.DBF' 
drop including datafiles;

4. Create a new tempfile for the original temp tablespace

alter tablespace temp
add tempfile '/filepath/TEMP01.DBF'
size 1000m autoextend on next 100m maxsize 4000m;

5. Make the original temp tablespace the primary one again

alter database default temporary tablespace temp;

6. Drop the new temp tablespace we created in #1 as we no longer need it

drop tablespace temp2 including contents and datafiles;

At this point, I saw that the disk space is recovered, and the tempfile is at the modest size of 1gb. All the file paths and file sizes are for demonstration purposes only; you should adjust the commands according to your needs.

Not sure where your temp files are? Try the following select statement:

select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS#  RFILE# STATUS  ENABLED         BYTES  BLOCKS CREATE_BYTES BLOCK_SIZE NAME
----- ---------------- --------- --- ------- ------- ---------- ---------- ------- ------------ ---------- --------------------
    1                0             3       1 ONLINE  READ WRITE 1048576000  128000   1048576000       8192 /FILEPATH/TEMP01.DBF

I ran into a situation today when I need to update 10,000 records in a master table totaling 1,000,000 rows, and the value that I’m going to use comes from the accounting department. Let us set up the scenario below.

Master Table Job Number * Job Type Invoice Amount 101 Installation 1000 102 Installation 1000 103 Maintenance 500 104 Repair 400 105 Installation 3000

Data From Accounting Job Number * Updated Invoice Amount 103 400 104 600

* Primary key

In sum, accounting wants to update job 103’s invoice amount to $400 and job 104’s invoice amount to $600, while leaving all other amounts the same. In this scenario, accounting only sent me two records, but in the real life situation I ran into, it was over 10,000 records. Also, this scenario’s master table has only 5 records; my real master table has close to 1,000,000 records. Thus, we need an update statement that is efficient.

Because Oracle does not allow multiple tables within the same update statement, I know at least one subquery will be needed. My first attempt, which was incorrect, was this:

-- Incorrect example!
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting
	where m.job_number=a.job_number
);

The problem with this update statement was that I realized it would update all 5 records instead of just the 2 records accounting wanted to update; this would result in 103 and 104 being updated to the updated amounts, but all other amounts wiped out to null value. To remedy that, we could throw in a where clause to the update statement, as below:

-- Better example, but still inefficient
update master_table m
set m.invoice_amount=(
	select a.updated_invoice_amount from data_from_accounting a
	where m.job_number=a.job_number
)
where m.job_number in(
	select a2.job_number from data_from_accounting a2
	where m.job_number=a2.job_number
);

This would correctly update the records I wanted to update while leaving the others alone, but this query is a little inefficient as it needs to go through the data from accounting twice.

Finally, I came up with a solution that works very efficiently, even with my large 1,000,000-record table.

-- Best practice
update (
	select m.invoice_amount, a.updated_invoice_amount
	from master_table m, data_from_accounting a
	where m.job_number=a.job_number
)
set m.invoice_amount=a.updated_invoice_amount;

Note that the job number field in both tables in this example scenario are both primary keys. In order for the “best practice” update statement to work, they must be either primary keys or unique indexes, otherwise the query will fail with the error message “ORA-01779: Cannot modify a column which maps to a non key-preserved table”. As an aside, to create primary key or unique indexes, follow the examples below.

-- Primary key
alter table master_table add (
  constraint pk_master_table primary key (job_number)
);

-- Unique index
create unique index idx_data_from_accounting on data_from_accounting (job_number);

The code makes use of Oracle’s dbms_random package and some very light-weight PL/SQL. You may adjust the number of fields, field data types, total number of records, etc. to suit your needs.

create table schema.test_data (
  txt  varchar2(10), 
  numb  number(10), 
  dt  date
);
begin
  for i in 1..10000 loop
    insert into schema.test_data values (dbms_random.string('X',10), dbms_random.value(0,20), to_date(trunc(dbms_random.value(2422599, 2457085)),'j'));
  end loop;
  commit;
end;

When we are done, we will see that the table contains 10,000 records as we specified in the sample code above.

select count(*) from schema.test_data;

  COUNT(*)
----------
     10000

Below is what the data may look like.

select txt, numb, to_char(dt,'mm/dd/yyyy') as dt from schema.test_data where rownum<10;

TXT              NUMB DT
---------- ---------- ----------
35W6DQ986O          7 11/26/1943
8NOOSRH6R2          8 01/18/1993
7HPKA10GKQ         16 10/07/2012
I90Z9YVWHW          2 11/01/1939
WNNW1M7BNM         15 10/27/1982
R9OQF67QWP         13 08/28/1926
PD39YGY35D         10 02/04/1952
N8R8DKMAIO         15 07/06/1986
43MELQ9M0Q          3 06/19/1921

Please review the following series of samples for how the dbms_random package works.

Random number:

select dbms_random.random as rand_numb from dual;

 RAND_NUMB
----------
-1.642E+09

Random number between 0 and 1:

select dbms_random.value as rand_numb_btwn_0_1 from dual;

RAND_NUMB_BTWN_0_1
------------------
        .950707301

Random number between x and y:

select dbms_random.value(10,20) as rand_numb_btwn_10_20 from dual;

RAND_NUMB_BTWN_10_20
--------------------
          14.6714295

Random upper case letters:

select dbms_random.string('U',10) as rand_upper from dual;

RAND_UPPER
----------
WVWZVYKNKP

Random lower case letters:

select dbms_random.string('L',10) as rand_lower from dual;

RAND_UPPER
----------
jdoyttefuw

Random mixed case letters:

select dbms_random.string('A',10) as rand_mixed from dual;

RAND_MIXED
----------
UBagEltDAr

Random upper case alphanumeric characters:

select dbms_random.string('X',10) as rand_upper_alphanumeric from dual;

RAND_UPPER_ALPHANUMERIC
-----------------------
JSANG89O8S

Random characters:

select dbms_random.string('P',10) as rand_chars from dual;

RAND_CHARS
----------
}![ NSOoo1

Random date between 1 Oct 1920 and 15 Mar 2015:
Note: The values used, 2422599 and 2457085, are Julian date numbers.

select to_date(trunc(dbms_random.value(2422599,2457085)),'j') as rand_date from dual;

RAND_DATE
---------
07-NOV-62

This is a simple step-by-step of how I created my main database in the Oracle 11g server running in a Linux environment. It is by no means comprehensive, but it may serve as a good starting point should you be looking for such a quick guide.

Increase Shared Memory Size

I am running Oracle Enterprise Linux, and on all Linux flavors, the total shared memory (shm) size is limited to 2gb. I am looking for a maximum size of 3gb, so I need to perform the following preparatory steps to set up a memory file system before setting up my database. You may or may not review this section.

[oracle@dbserver ~]$ su - root
Password:
[root@dbserver ~]# nano /etc/fstab

In nano (or your favorite editor), add or modify your entry for “/dev/shm” so that it looks like this:

[root@dbserver ~]# more /etc/fstab
...
tmpfs     /dev/shm     tmpfs     size=3g     0 0
...

At this point, you have the option of either running the following command to increase “/dev/shm” size to 3gb at runtime, or simply restart the server so that the change you have just made in fstab will take effect.

[root@dbserver ~]# mount -t shm tmpfs -o size=3g /dev/shm

Either after running “mount” or after restarting, “df -k” should reveal the following. If so, you are ready to set up the database. Note you should now use the “oracle” user instead of the “root” user.

[oracle@dbserver ~]$ dk -k
Filesystem   1K-blocks   Used   Available   Use%   Mounted on
...
tmpfs          3145728      0     3145728     0%   /dev/shm
...

Database Configuration Assistant (DBCA)

You may launch DBCA as follows.

[oracle@dbserver ~]$ dbca

Step 1: Operations — Choose “Create a Database”

Step 2: Database Templates — Choose the appropriate template. “General Purpose or Transaction Processing” is likely the one you will select.

Step 3: Database Identification — Set “Global Database Name” to “db_name.yorktel.com” and “SID” to “db_name”

Step 4: Management Options — Check “Configure Enterprise Manager”, check “Enable Alert Notifications”, and configure the SMTP server and email address.

Step 5: Database Credentials — Enter passwords for each system user, or choose “Use the Same Administrative Password for All Accounts” to use only one password.

Step 6: Storage Options — In my environment, I chose “File System”; your system might be different.

Step 7: Database File Locations — Since I chose “File System” in the previous step, in this current step I chose “Use Common Location for All Database Files” and set the path to “/u02/app/oracle/oradata”. Once the database is created, my database files will thus end up in “/u02/app/oracle/oradata/db_name”.

Step 8: Recovery Configuration — Check “Specify Flash Recovery Area” and set the “Flash Recovery Area Size”; in my environment, I use the very large value of “81920” (in mb) because I have the space available to waste. In this step, also check “Enable Archiving”.

Step 9: Database Content — No change necessary in my case

Step 10: Initialization Parameters — Under “Memory” tab, set “Memory Size (SGA and PGA)” to 3072mb (which is 3gb); if you cannot move beyond this step due to the 2gb limitation, please see the first section of this guide. Knowing my environment and the high number of individual user sessions, under “Sizing” tab, I changed the “Processes” parameter to 220 to suit my environment’s needs. I then clicked on “All Initialization Parameters” and “Show Advanced Parameters” to update/confirm the following; you may use the list for reference, but you may need to do different configurations for your environment.

• db_flashback_retention_target: I set it to “5760” for setting up the flashback database later
• db_unique_name: I set this to “unique_db_name”
• memory_max_target: I set this to “3G”
• processes: I confirmed this is “250”
• standby_file_management: I set this to “AUTO”
• undo_management: I set this to “AUTO” for setting up the flashback database later
• undo_retention: I set this to “3600” for setting up the flashback database later

Step 11: Security Settings — No change necessary in my case

Step 12: Automatic Maintenance Tasks — No change necessary in my case

Step 13: Database Storage — You should confirm that the file locations are correct

Step 14: Creation Options — Make appropriate selections and “Finish” the DBCA process

The “Finish” button starts the database creation process.

Database Backup with Enterprise Manager

Once DBCA database creation is completed, the database will be set up and will be available for use. At this point, I logged on to Enterprise Manager to perform some final set up items regarding database backup. Again, this may or may not be fitting with your requirements, but it may present a good reference for you to review. To go into Enterprise Manager (EM), go to the URL “https://dbserver:1158/em/” and log on as sysdba.

Once I got into EM, I went to the “Availability” tab and clicked on “Recovery Settings”. I checked “ARCHIVELOG Mode” and changed the first archive log file destination to a path I prefer, such as “/u02/app/oracle/oradata/flash/db_name”. I checked “Enable Flashback Database” and restarted the database for this to take effect.

After the database was restarted, I logged back into EM (again as sysdba), I returned to “Availability” tab, and clicked on “Schedule Backup”. I clicked on the “Schedule Oracle-Suggested Backup” button and chose “Disk” in step 1 and configured the date and time in step 3. I set it up so that it runs a full database backup regularly during periods that typically sees low usage. The RMAN script hidden behind this GUI is the following.

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA$OEM_LEVEL_0';
backup incremental level 1 cumulative  copies=1 for recover of copy with tag

'ORA$OEM_LEVEL_0' database;
}

Next, again in “Availability”, I clicked on “Manage Current Backups” and scheduled “Crosscheck All”, “Delete All Obsolete”, and “Delete All Expired” jobs so that obsolete and expired backup sets will be removed regularly in free up space for current backup sets; note to schedule it to run regularly, you should choose the “repeating” radio button. The RMAN scripts hidden behind the GUI are as follows.

-- Crosscheck
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;
-- Delete Obsolete
DELETE NOPROMPT OBSOLETE;
-- Delete Expired
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;

Assumptions

In this sample, it is assumed that you already have your Oracle 11g primary database functioning, and have already set up a Oracle 11g physical standby database, and can access them using the Data Guard Command-Line Interface (dgmgrl) utility. The current primary database will be referred to as “orcl1” and the current physical standby database will be referred to as “orcl2”. The SID for both primary and standby is “orclsid”. The current primary host is “host1” and the standby host is “host2”.

Switchover/Failover Procedures

First, launch the Data Guard Command-Line Interface and connect to the database.

[oracle@host1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@orcl1
Password:
Connected.

Then, issue switchover or failover command depending on your situation. Generally, a switchover is used to willingly pass the role of the primary database to the physical standby database (or, one of the physical standby databases), while a failover is typically done only when a major problem prevents you from normally using the primary database. Below is what you may see when you perform a switch over.

DGMGRL> switchover to orcl2
Performing switchover NOW. Please wait...
Operation requires shutdown of instance "orclsid" on database "orcl1".
Shutting down instance "orclsid"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "orclsid" on database "orcl2".
Shutting down instance "orclsid"...
database not mounted
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1".
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "orclsid" on database "orcl2".
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Switchover succeeded. New primary is "orcl2"

And in the case of a failover…

DGMGRL> failover to orcl2
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl2"

To confirm Data Guard switchover or failover has completed successfully, log in to Data Guard Command-Line Interface and issue the “show configuration” command.

[oracle@host2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@orcl2
Password:
Connected.
DGMGRL> show configuration

Configuration
  Name:                DRSolution
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    orcl2 - Primary database
    orcl1 - Physical standby database

Fast-Start Failover: DISABLED

Current status for "DRSolution":
SUCCESS

The above sample shows what the display would look like after a switchover. In the case of a failover, the physical standby database will show as “disabled” as below.

DGMGRL> show configuration

Configuration
  Name:                DRSolution
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    orcl2 - Primary database
    orcl1 - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Current status for "DRSolution":
Warning: ORA-16608: one or more databases have warnings

As an additional note, as the former physical standby database of orcl2 is now operating as your new primary database, you may wish to use Enterprise Manager to help you manage orcl2. If that is the case, you will need to perform the following to recreate the DBControl repository to enable Enterprise Manager on host2.

[oracle@host2 ~]$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Sep 29, 2008 2:02:40 PM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orclsid
Database Control is already configured for the database orclsid
You have chosen to configure Database Control for managing the database orclsid
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]:

At this point, you should have your former physical standby database orcl2 running fully as your primary database server.

After a failover, the physical standby database will not be present in your current setup. In the case that the failed database could be brought back, issue the following command in Data Guard Command-Line Interface to reinstate the failed database as a physical standby database.

DGMGRL> reinstate database orcl1
Reinstating database "orcl1", please wait...
Operation requires shutdown of instance "orclsid" on database "orcl1"
Shutting down instance "orclsid"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1"
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl1" ...
Operation requires shutdown of instance "orclsid" on database "orcl1"
Shutting down instance "orclsid"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "orclsid" on database "orcl1"
Starting instance "orclsid"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "orcl1" ...
Reinstatement of database "orcl1" succeeded