Cross checking oracle ArchiveLogs using RMAN in Oracle 8 and 9

In Oracle 10g you can run the following command to crosscheck and expire any expired archive logs:
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

However in Oracle 8 and 9 you need to do the following

Example for Oracle 8 use:
RMAN> Change Archivelog All Validate;

Example for Oracle 8i use:
RMAN> change Archivelog All Crosscheck;

Example for Oracle 9i:
RMAN> crosscheck archivelog all;

How do you create Export/Import views

Run the “catexp.sql” as SYS. The file is located in $ORACLE_HOME/rdbms/admin.

SQL> @?/rdbms/admin/catexp.sql

Renaming a SQL Server instance.

SQL> sp_dropserver 'oldservername';
SQL> sp_addserver 'newservername';

don’t run in same batch.
the two statements must be executed separately.

via sp_addserver Transact-SQL.

Moving an Oracle database to a newer Oracle home

Moving a database to a new oracle home, owned by a different user and running a newer version.

NOTE : This is how is worked in my environment. It might not work the same in your environment. This is meant as a guide only.

Changeing an Oracle database character set

1. Check what character set you are using at the moment.

   SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';

2. Shutdown the database.

   SQL> shutdown immediate;

3. Startup in mount mode.

   SQL> startup mount;

4. Restrict the database.

   SQL> alter system enable restricted session;

5. Check the current job_queue_processes setting.

   SQL> show parameter job_queue_processes;

6. Set it to 0 for now.

   SQL> alter system set job_queue_processes=0;

7. Open the database.

   SQL> alter database open;

8. Change the character set

   SQL> alter database character set WE8MSWIN1252;

9. Restart the database.

   SQL> shutdown immediate;
   SQL> startup;

10. Check the job_queue_processes setting.

    SQL> show parameter job_queue_processes;

11. Check the character setting is the new value.

    SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';

12. If necessary, change the job_queue_processes back to what it was.

    SQL> alter system set job_queue_processes=10;

13. Restart the database.

    SQL> shutdown immediate;
    SQL> startup;

Using cpio

Some files that you get from Oracle download come in cpio format.
Here’s what to do with them.
First unzip the files:

gunzip lnx_920_disk1.cpio.gz
gunzip lnx_920_disk2.cpio.gz
gunzip lnx_920_disk3.cpio.gz

Next unpack the contents of the files:

cpio -idmv < lnx_920_disk1.cpio
cpio -idmv < lnx_920_disk2.cpio
cpio -idmv < lnx_920_disk3.cpio

ERROR: NMO not setuid-root (Unix-only)

Possible Cause : root.sh not run after the EM 10G installation.

Fix:
Check the permissions and ownership of the nmo and nmb executable in the $
AGENT_OH/bin, where the management agent has been installed:

$ ls -al nmo
$ ls -al nmb

These should be of the form :

-rwsr-s--- 1 root dba 22465 Apr 13 17:53 nmo
-rwsr-s--- 1 root dba 18055 Apr 13 17:53 nmb

Inappropriate ioctl for device and Oracle 10.2.0.3

Photo by Cindy_FL

I was trying to setup an instance of Oracle 10.2.0.3 a while back and was running into a strange problem.

I could get 10.2.0.1 to install and run OK, and the upgrade to 10.2.0.3 seemed to work OK. But then when I tried to create a database it would fail and give me a “Inappropriate ioctl for device” error message. And inappropriate what for who ?

MySQL create user

Every time I have to setup users in MySQL I seem to have problems.
I was setting up a system to do some WordPress testing and installed MySQL OK, but ran into problems with the users and permissions. Again.

So I had to search the net, again, and find out what I was doing wrong, again.
Maybe I’m just used to the way oracle does it, my MySQL does it in a strange way, why is it so hard ?

Adding space to a tablespace

by numstead

One of the jobs I do the most as a DBA is added space to peoples schemas.  It’s not a big thing, just something I have to monitor and watch out for. Users don’t like it when they can’t save their data into the database.

Below is a script that simplifies the main tasks required to do this.