February 2010 – My Tech Notes

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.

How to turn on or off colors in bash

Type the following command for just this listing,
$ ls --color=none
Or remove the alias with the unalias command:
$ unalias ls
via How to turn on or off colors in bash.

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.
Continue reading Moving an Oracle database to a newer Oracle home

Changeing an Oracle database character set

Check what character set you are using at the moment.

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

Shutdown the database.
```
SQL> shutdown immediate;
```
Startup in mount mode.
```
SQL> startup mount;
```

Restrict the database.

SQL> alter system enable restricted session;

Check the current job_queue_processes setting.
```
SQL> show parameter job_queue_processes;
```

Set it to 0 for now.

SQL> alter system set job_queue_processes=0;

Open the database.
```
SQL> alter database open;
```

Change the character set

SQL> alter database character set WE8MSWIN1252;

Restart the database.
```
SQL> shutdown immediate;
SQL> startup;
```

Check the job_queue_processes setting.

SQL> show parameter job_queue_processes;

Check the character setting is the new value.

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

If necessary, change the job_queue_processes back to what it was.
```
SQL> alter system set job_queue_processes=10;
```
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

Prtdiag is Not working in Solaris

Try running it as root.
If this works then check the permisions on /var/run/picld_door
The permisions should be set to 444
ie:

ls -l /var/run/picld_door
Dr--r--r--   1 root     root           0 Aug 27 07:40 /var/run/picld_door

This is the fix:

shell> chmod 444 /var/run/picld_door