Helps and Hints for Databases

In the four years I have done Unix system administration, I have found that I keep finding myself looking through newsgroups for fixes I discovered long ago. The purpose of these pages is to store these helpful tidbits for future use, and forget about having to research them time and again. While I have not be a DBA for as long, I thought I would continue the tradition by keeping a database page, too.

I am hoping you find these hints as helpful as I did.

There is also a separate page for unix help.

Oracle | Sybase | Ingres

Oracle

How to find the database date.

$ ORACLE_SID=<sidname>; export ORACLE_SID


$ svrmgrl


SVRMGR> connect internal


SVRMGR> select sysdate from cat;

Preferred method of moving/duplicating a database:

1. Copy or Install Oracle to new server if not already existing.
2. Create text backup of controlfile.
   SVRMGR> alter database backup controlfile to trace;
   and go print it out!
3. Perform cold backup of datafiles.
   1. Shutdown database
      shutdown immediate <instance>;
   2. Backup all files to tape or disk, including controlfiles, redo logs, and any associated data files.
4. Perform OS disk mount point changes (if any).
5. Edit config.ora and/or init.ora files for controlfile bdump, cdump and udump locations.
6. startup mount database;
7. Rename all files, including redo logs to point to new mount point locations. Typos cause errors.
8. alter database open;

Sybase

How to find the database date.

$ isql -Usa -P<passwd>


1> select getdate()


2> go

Rebuilding/recovering a Sybase database.

I found that it takes a very long time to bcp large databases out. Besides that, most people don't do nightly bcps to backup their databases, so this is how to recover a database as I did.

1. Copy or Install Oracle to new server if not already existing.
2. Make hard copies of sysdevices and sp_dbhelp for each database for reference
3. Rebuild master device (make sure SQLserver is NOT RUNNING.)
4. Start backup server, before you forget.
5. Start server in single user mode.
6. Run installmaster
7. Shutdown server and put in multiuser mode.
8. Check backup server name in sysservers table
9. Load model database. Not necessary if you use the vanilla model.
10. If tempdb has been altered or increased in the original installation, recreate devices. Make sure that they are at least as large as the original. It is important to use the original device name so that it works well when you load the master database.
11. If sybsystemprocs has been altered in the original installation, recreate devices. Make sure that they are at least as large as the original. You should use the same device name and while creating the device the same physical name. I ran into a glitch here is trying to make it something else, but someone else might have a better way to do this. I've found that using a different name gets it marked suspect, and you end up dropping it and doing it all over. No big deal.
12. Load sybsystemprocs
13. Shutdown database and restart in single user mode.
14. Load master database.
15. Drop any production or test databases still present in master. The server complains mightily until you do this. It is looking for catalogs that it no longer can find in each of the databases. You will likely find that even though these databases are marked suspect, you can't remove them with dbcc repair (<dbname>, dropdb). To manually remove the references from sysdatabases and sysusages see "When dbrepair won't drop suspect DB".
16. Create all the devices required for your database. I skipped this step because all the LVM points to the raw volumes had the same names and sizes.
17. Create database for load for each DB you wish to recreate.
18. Load database.

When dbrepair won't drop suspect DB

	1> use master
	2> go
	1> sp_configure "allow updates", 1
	2> go
	1> delete master..sysdatabases where dbid=<x>
	2> delete master..sysusages where dbid=<x>
	3> go
	1> checkpoint
	2> go
	1> sp_configure "allow updates", 0
	2> go
	1> shutdown with nowait
	2> go

Ingres

Guest Book | Email | Home

Jacinth
Revised: August 16, 1999