Author: C. Peter Chen

Founder of Lava Development, LLC.
Posted on

Using Oracle RMAN to duplicate a database

Today, I was working with a code base still in development, but really wanted to use it against the latest production data. To achieve this, I used Oracle 11g’s Recovery Manager, “RMAN”, to duplicate the latest content of my production database onto a development Oracle server that I have set up to work with my development code. Let us assume that my production database name was “prodDb” residing on server “prodServer”, the database I was creating is “devDb” residing on “devServer”.

First, I needed to create some directories on devServer.

mkdir -p /u01/app/oracle/admin/devDb/adump
mkdir -p /u01/app/oracle/flash_recovery_area/devDb
mkdir -p /u01/oradata/devDb

Then, on devServer, I used the scp command to copy the prodServer password file to devServer.

scp prodServer:/u01/app/oracle/product/ver_no/db_1/dbs/orapwproddb $ORACLE_HOME/dbs/.

Next, I created a dummy init file so we can mount this empty devDb database.

echo 'DB_NAME=devDb' > $ORACLE_HOME/dbs/initdevDb.ora
echo 'control_files=(/u01/oradata/devDb/control01.ctl,/u01/oradata/devDb/control02.ctl,/u01/oradata/devDb/control03.ctl)' >> $ORACLE_HOME/dbs/initdevDb.ora

I mounted devDb, then restarted the listener.

(From terminal prompt, log in to SQL*Plus as sysdba)
sqlplus /nolog
conn / as sysdba

(SQL*Plus command to mount database)
startup nomount pfile=$ORACLE_HOME/dbs/initdevDb.ora;
exit;

(Back in terminal prompt, restart the listener)
lsnrctl stop
lsnrctl start

My work on the devServer was then done. I moved on to the prodServer machine; from the terminal prompt of the prodServer, I started RMAN.

(From terminal prompt, run "rman")
rman

(In RMAN prompt, connect to prodDb as target, and to devDb as the auxiliary)
connect target sys@prodDb
connect auxiliary sys@devDb

(In RMAN, run the following script; you may wish to modify to suit your needs, of course)
run {
   duplicate target database to devDb from active database
   spfile
           parameter_value_convert 'prodDb','devDb'
           set db_unique_name='devDb'
           set service_names='devDb.mydomain.com'
           set log_file_name_convert='prodDb','devDb'
           set log_archive_config=''
           set fal_client=''
           set fal_server=''
           set log_archive_dest_1=''
           set memory_max_target='1200m'
           set memory_target='1200m'
           set sga_max_size='800m'
           set sga_target='800m'
           set db_recovery_file_dest_size = '20G'
           db_file_name_convert 'prodDb','devDb'
        ;
     }
Posted on

Inserting multiple records at once into a database table

For demonstration purposes, we are going to assume that we have a table named “employees” with two fields, name and department.

Oracle

insert all
  into employees (name, department) values('Aaron','Accounting')
  into employees (name, department) values('Ibis','IT')
  into employees (name, department) values('Mary','Marketing')
  into employees (name, department) values('Paul','Useless')
select '' from dual;

MySQL, PostgreSQL, and SQL Server

insert into employees (name, department) values
  ('Aaron','Accounting'),
  ('Ibis','IT'),
  ('Mary','Marketing'),
  ('Paul','Useless');
Posted on

Shorten long URLs with Perl, cURL, and the short.to API

#!/usr/bin/perl

### This is the long URL we wish to ultimately shorten
$url = "http://ww2db.com/battle_spec.php?battle_id=10";



sub body_callback {
        my ($chunk,$context)=@_;
        push @{$context}, $chunk;
        return length($chunk);
}

use Curl::easy;

my $curl= Curl::easy::init();
Curl::easy::setopt ($curl, CURLOPT_SSL_VERIFYHOST, 0);
Curl::easy::setopt ($curl, CURLOPT_SSL_VERIFYPEER, 0);
Curl::easy::setopt ($curl, CURLOPT_URL, "http://short.to/s.txt?url=$url");
Curl::easy::setopt ($curl, CURLOPT_WRITEFUNCTION, &body_callback);
my @body;
Curl::easy::setopt ($curl, CURLOPT_FILE, @body);
Curl::easy::setopt ($curl, CURLOPT_ERRORBUFFER, "errorBuffer");

if (Curl::easy::perform ($curl) != 0) {
	print "Fail: $errorBuffern";
}
else {
	### Now we have the URL stored within @body for our use.
	print "Success: @bodyn";
};

Curl::easy::cleanup($curl);
Posted on

Update Twitter status programmatically with PHP and cURL

function twitterUpdate($u, $p, $upd) {
	// $u is your username (string)
	// $p is your password (string)
	// $upd is your status update (string)

	$url = 'http://twitter.com/statuses/update.xml';

	$curl_handle = curl_init();
	curl_setopt($curl_handle, CURLOPT_URL, $url);
	curl_setopt($curl_handle, CURLOPT_CONNECTTIMEOUT, 2);
	curl_setopt($curl_handle, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($curl_handle, CURLOPT_POST, 1);
	curl_setopt($curl_handle, CURLOPT_POSTFIELDS, "status=".$upd);
	curl_setopt($curl_handle, CURLOPT_USERPWD, $u.":".$p);
	$buffer = curl_exec($curl_handle);
	curl_close($curl_handle);

	if (empty($buffer)) {
		return 'fail';
	}
	else {
		return 'success';
	}
}

Example usage is below.

$u='username';
$p='password';
$upd='Hello world!';

$twitterUpdateResult = twitterUpdate($u, $p, $upd);

echo $twitterUpdateResult;
Posted on

Recover from a bad update statement with Oracle flashback

This morning, a coworker came to my office with an awkward smile on his face. “Oops”, he started, “I ran a bad query and I updated a bunch of records incorrectly.” He thought he had messed up big time, but actually, I was able to recover data for him quite easily, utilizing our Oracle 11g database’s flashback feature (note: this method applies to Oracle 10g as well).

I asked him two questions: What was the update SQL statement that he ran, and when did he run it? For our example, let’s say the SQL statement was as follows, and he ran it at 10am.

-- Bad SQL statement executed by user
update myTable
set field1 = field2, field2 = null
where field1 is null;

As you can see, since he is moving field2 into field1, and previously other records had already gone through this process, he could no longer tell which ones were modified by him at 10am and which ones were done previously. Since it only had been a few minutes since he committed the bad update statement, I knew I had plenty of time to recover the data. Actually, I did not need much time at all. This is what I did to identify the records he modified:

create table myTable_comparison
as
select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.field1 <> after.field1;

Note in the above example, I used the additional clause … as of timestamp [insert a date value here] to pull out a view of what the data looked like today at 9:59am, a moment before my coworker updated the data. This creates the table myTable_comparison that contains all rows which had a different before and after field1 value, using myTable’s primary key column as the unique identifier to help my coworker review what had been changed. He confirmed the records in this table are exactly what he wanted to revert, so I rewrote the select statement into an update statement to revert the data back to the previous state.

In this case, my coworker mistakenly updated values. What if he had mistakenly deleted records instead? To recover, we just need to find the records that was present previously, but no longer present anymore by using an outer join between the before and after tables.

select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.pkey_field=after.pkey_field(+) and after.pkey_field is null;
Posted on

Java hashtable sample usage

A hashtable stores key-value pairs. In Java, it can be found in the package java.util.Hashtable. The examples below demonstrates some ways hashtables can be used in Java.

Instantiating a Hashtable

// Generic hashtable
Hashtable hashtable = new Hashtable();
// Hashtable with a specified initial size
Hashtable hashtable = new Hashtable(100);

Adding Data

hashtable.put("A", 1);
hashtable.put("B", 2);

In the example above, “A” and “B” are the keys, and 1 and 2 are the corresponding values.

Displaying Hashtable Info

// General info
System.out.println("Is Empty (returns true/false)?: " + hashtable.isEmpty());
System.out.println("Hashtable size (returns integer): " + hashtable.size());
// Query by key
System.out.print("Contains key \"A\" (returns true/false)?: " + hashtable.containsKey("A"));
// Query by value
System.out.print("Contains value 1 (returns true/false)?: " + hashtable.contains(1));

Getting Data

// Get by a particular key
System.out.print("Value for Key \"A\" is: " + hashtable.get("A"));
// Get all
for (String key : hashtable.values()) {
	// ...
}

Removing Data

hashtable.remove("A");
Posted on

Crystal Reports Server XI backup and recovery step-by-step

I am running Crystal Reports Server XI with a SQL Server 2000 database. These two components are running on two different servers, but it should not change the procedures below much.

Procedures for Scheduled Daily Backups

On the database server, I set up a batch file that utilizes SQL Server 2000’s osql command to perform backup of the Central Management Server (CMS) database; I scheduled it to run nightly. The content of the batch file is as follows. Note that you should substitute in your server name, user name, password, and file paths. Also, note the copy command makes a copy of the backup file to another server, which is highly recommended; how good is a backup if the system failure is caused by a hard disk burning out and the backup resides on the same hard disk, right?

osql -S [serverHostName] -U [databaseUsername] -P [myPassword] -Q "begin declare @CMSBackupFile varchar(255) set @CMSBackupFile='C:cms.bak' backup database [cmsDatabaseName] to disk = @CMSBackupFile end"
xcopy.exe /c /d /e /h /r /y C:cms.bak \anotherServerbackupscms

On the Crystal Reports Server XI server, there is a folder that stores the actual reports; Crystal Reports Server XI refers to this folder as the Input File Repository. By default, it is located at C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput, which is what the example below will make use. The example is another batch file, scheduled to run nightly, that makes a backup copy of the reports to another server.

xcopy.exe /c /d /e /h /r /y "C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput*.*" \anotherServerbackupsfileStore

Restore Procedures

My plan to deal with the unthinkable is listed below.

For the SQL Server, if needed, install SQL Server and then copy the backup file (“cms.bak” in this example) to the new database server. Next, issue the following two osql commands in the command prompt to create/recreate the CMS database.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "drop database [cmsDatabaseName]"
osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "restore database [cmsDatabaseName] from disk='c:cms.bak'"

Next, we will likely find an orphaned user in the restored database; this can be seen in SQL Server Enterprise Manager’s “Users” section for the CMS database; you will see an user without “Login Name”. To fix this, run the following osql command in the command prompt.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -d [cmsDatabaseName] -Q "exec sp_change_users_login 'Auto_Fix', '[crystalReportsDatabaseUser]', NULL, '[passwordHere]'"

For the Crystal Reports Server XI reports, if needed, they can be copied directly from your backup to the new Input File Repository location.

When you have completed the restore, you may wish to restart the CMS and Input File Repository services in the Central Configuration Manager.

Posted on

Fixing orphaned users after attaching or restoring a SQL Server database

I ran into this issue when I took a backup set from a production database and restored it into a machine in my development environment. I noticed that the recently restored database has an user named “bob”, but when looking at bob’s entry in the newly restored database, the Login Name field is blank. To fix this, I made use of SQL Server’s built-in stored procedure sp_change_users_login. The syntax is below:

Syntax:

sp_change_users_login 'action'
	[, 'user']
	[, 'login']
	[, 'password']

Valid values for 'action':

Report
Update_One
Auto_Fix

The “Report” action displays a report of all possible orphans in the current database and requires no further parameters. Sample usage:

exec sp_change_users_login 'Report'

The “Update_One” action links a specified orphaned user in the current database to an existing SQL Server login. With this action, the “user” and “login” parameters must be provided, and the “password” parameter must be NULL or not provided. Sample usage:

exec sp_change_users_login 'Update_One', 'bob', 'bob'

Finally, the method I chose to perform to fix my issue was the “Auto-Fix” action, which saves me the step of having to manually create the “bob” SQL Server login first because it will automatically create the login if it does not already exist. The “Auto_Fix” action requires the “user” and “password” parameters, and the “login” parameter must be NULL. Sample usage:

exec sp_change_users_login 'Auto_Fix', 'bob', NULL, 'password123'
Posted on

Changing Oracle 11g password sensitivity

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.

Proudly powered by WordPress