When trying to execute a crystal check on one of my development systems I found a problem after trying to fix another problem.  All the crystals were going to error status and the message was coming up:

File: SQL Access ManagerSQL error. Stmt #: 2  Error Position: 0  Return: 404 – ORA-01017: invalid username/password; logon denied  (200,0)

It turns out that the PSACCESSPRFL table had an extra entry which was invalid for the environment.  Once I removed the invalid entry from the PSACCESSPRFL table the problem was resolved.

When doing SQL in Oracle where you want to search for something containing a string, you can use the like command.

 Select * from table1 where field1 like ‘somevalue’;

This will return rows with field1 = somevalue, not exactly useful.  But you can use the wildcard ‘%’ so

 Select * from table1 where field1 like ‘%somevalue%’;

This will return rows with field1 containing the string ‘somevalue’ in any form.  There is another wildcard ‘_’ which will match any character in that position, so

Select * from tabe1 where field1 like ‘_omevalue’;

This will return rows with field1 = aomevalue, bomevalue, comevalue, domevalue, …., zomevalue

If you want to use the the literal of % and _ then you will have to put an escape character in front of it, and tell Oracle what the escape character is:

Select * from table1 where field1 like ‘lookfor\%’ escape ‘\’

This will return rows where field1 = ‘lookfor%’

The file oratab is created when you install the Oracle Database server product.  This file is typically found in the /etc directory and contains entries in the format:

 sid:oracle_home:Y|N

 So the sid represents the System Id of an Oracle Database on the current server, oracle_home represents the home of where the database instance resides, and the Y|N indicates weather or not the database instance starts at bootup. Y=Yes, N=No. 

 This file is used during the dbstart, dbshut, and oraenv commands, and can also be used in custom scripting to cycle through the databases instances housed on the server.

?When using the VI editor on most unix systems a common command you will use is the search and replace.  The % indicates that you want to search the entire file, you can also use 1,$ to indicate from line 1 to the end.  If you just say :s it will only search the current line. If you want to search from the current line to end of a file you can type :.,$s the “.” Indicates the current line. Lastly the g at the end of the command indicates “global” so all occurances will be changed.  If you omit the g it will only change the first occurrence of search_string.

 :%s/{search_string}/{replacement_string}/g

 If you are searching for a string or replace a string the has a “/” in it, you can have to prefix the “/” with a “\” it can get somewhat messy but it works!

For example

:%s/\/etc/\/opt/g

This will search the entire file for /etc and change it to /opt

ORA-01126: Database Must Be Mounted

When trying to change an oracle database to no archive log mode you might receive an error: ORA-01126: database must be mounted  EXCLUSIVE and not open for this operation.

Turn Off Archive Logging:

The easiest way is to shutdown the database and then startup unmounted, then do the alters, you will want to do this as the main oracle administrator database user:

NOTE: When in NOARCHIVELOG mode the database runs normally, but there is no capacity to perform any type of point in time recovery operations or online backups. Thus, you have to shutdown the database to back it up, and when you recover the database you can only recover it to the point of the last backup. While this might be fine for a development environment, the management folks tend to frown when a weeks worth of current production data is lost forever.

Turn On Archive Logging:

To turn on archive logging, you can use the command:

alter database archivelog;

Note: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common one Smart Panda Runs into is:  ORA-00257

Check Archive Logging Status:

To see the current status issue the following command:

archive log list;

When turning on archiving, if you need to use RMAN to do a backup, you need to actually have a log archive file, if you have just turned it on, there is no archive logs, so you will get an error.  You can force Oracle to generate a log archive using the following command from sql*plus:

alter system switch logfile;