Showing posts with label Oracle database administration. Show all posts

Monday, June 10, 2013

ORA-16038 ORA-19809 ORA-00312

Got the error when starting an Oracle database:

SQL> startup
ORACLE instance started.

Total System Global Area 1010827264 bytes
Fixed Size 2045480 bytes
Variable Size 385878488 bytes
Database Buffers 603979776 bytes
Redo Buffers 18923520 bytes
Database mounted.

ORA-16038: log 3 sequence# 42 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u02/oradata/DB/redo03.log'

Solution:

To solve ORA-19809 error, increase the value of db_recovery_file_dest_size parameter. And start the database:

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=3G SCOPE=BOTH;

SQL> shutdown abort

SQL> startup

Tuesday, November 27, 2012

Oracle changePerm.sh script in versions 9.2.0.8, Oracle 10g

changePerm.sh is a script that allows users that do not belong to the "dba" group to access and execute files in the ORACLE_HOME directory.

The script is located in the ORACLE_HOME/install directory on Unix and Linux systems (there is no equivalent for Windows). This script ships with Oracle Database versions 9.2.0.8, Oracle 10g.

The changePerm.sh file is no more available nor necessary for Oracle database server 11.1.0.x because in 11g, permissions under the Oracle Home (including "others") are set correctly/relaxed. (oracle note 834626.1)

===================================
Before change:
$ ls -ld $ORACLE_HOME/lib
drwxr-x--- 3 oracle dba 5632 Nov 27 09:30 /fs/oracle/product/10204ee/lib

Running script:

$ cd $ORACLE_HOME/install

$ ls -l

total 964

-rwxr-xr-x 1 oracle dba 11380 Nov 22 2007 changePerm.sh

-rw-r----- 1 oracle dba 0 Jun 7 2005 createseed.sh

-rw-r----- 1 oracle dba 0 Jun 7 2005 createseed1.sh

-rw-r----- 1 oracle dba 618 Nov 26 14:19 envVars.properties

-rw-r----- 1 oracle dba 131 Nov 26 13:51 envVars.properties.bak

-rwxr-xr-x 1 oracle dba 38 Apr 19 2005 install.excl

drwxr-x--- 2 oracle dba 512 Nov 26 11:55 jlib

-rw-r----- 1 oracle dba 437788 Nov 26 14:19 make.log

-rw-r----- 1 oracle dba 102 Nov 26 14:19 portlist.ini

-rw-r--r-- 1 oracle dba 124 Nov 26 14:19 readme.txt

-rw-r--r-- 1 oracle dba 7609 Jan 12 2007 restrict.lst

-rwxr-xr-x 1 oracle dba 822 Nov 26 14:16 rootdeletenode.sh

-rw-r--r-- 1 oracle dba 10031 Nov 26 14:16 rootlocaladd

-rw-r----- 1 oracle dba 0 Jun 7 2005 seed.log

-rw-r--r-- 1 oracle dba 2808 Jul 14 2005 templocal

drwxr-x--- 2 oracle dba 512 Nov 26 14:19 unix

drwxr-x--- 2 oracle dba 512 Nov 26 14:13 utl

$ ./changePerm.sh

-------------------------------------------------------------------------------

Disclaimer: The purpose of this script is to relax permissions on some of the

files in the database Oracle Home so that all clients can access them.

Please note that Oracle Corporation recommends using the most restrictive file

permissions as possible for your given implementation. Running this script

should be done only after considering all security ramifications.

-------------------------------------------------------------------------------

-n Do you wish to continue (y/n) [n]:

Spooling the error log /tmp/changePerm_err.log...

Finished running the script successfully

After change:

$ ls -ld $ORACLE_HOME/lib
drwxr-xr-x 3 oracle dba 5632 Nov 27 09:40 /fs/oracle/product/10204ee/lib

===================================
Reference: http://www.orafaq.com/wiki/ChangePerm_sh

Friday, October 26, 2012

Oracle Database Automatic Startup and Shutdown on UNIX

Reference:
http://www.oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux.php

Monday, July 9, 2012

ORA-00821 ORA-01034 ORA-27101, SGA_target and SPARC T series database servers

Problem:

When migrating a database from an older Solaris database server to a new Solaris T3/T4 series database server, SGA_Target has to be increased from 350MB t0 900MB to have the database started up. Otherwise ORA-04031 error would occur.

After spending sometime researching, the following Oracle Support article explains the issue and gives a workaround.

Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Goal

Migrated a database server from a server with 8 CPU's to a server with 128 (virtual) CPU's - that is, 2 CPU's, each with 8 cores which each handled 8 threads = 2x8x8 = 128.

So Solaris (on SPARC T series) is presenting Oracle with 128 CPU's. This makes Oracle adjust it's memory demands upwards, meaning that before the databases can ran fine with 320 MB of ram (sga_target) now needs to have up around 800-900 MB of RAM?

Is there anyway to minimize the memory usage?

Fix

Calculating the sga_target should consider the following:

The calculation for min cache size = max (number of CPUs, number of processor groups) * max (granule size, 4MB)

So the SGA size will mainly depend on CPU_COUNT.

Bug 8316255 was filed for this issue and initially closed as 'Not a Bug' with the comments:

"CPU_COUNT should not be changed . This is not a bug.
Reducing CPU_COUNT has side effects due to the fact that the per cpu power of CMT may not be sufficient to sustain high load ( e.g. cache fusion messages ).
Also, some of the dependent parameters such as gc latches would be underconfigured ".

Currently, a fix has been produced which added the notion of the raw cpu threads and effective cpu threads.
This fix is only available in the 11.2.0.3 Bundle Patch 1 for Exadata Database and will be included in 12.1.
Please refer to:
Note 8316255.8 - Bug 8316255 - Wrong discovery of number of CPUs in SPARC T series.
Note 13343057.8 - Bug 13343057 - 11.2.0.3 Bundle Patch 1 for Exadata Database

For other systems, a way of reducing memory needs would be to set a manual value for DB_CACHE_SIZE (which depends on CPU_COUNT) and keep the old value of SGA_TARGET or not using ASMM (not using SGA_TARGET).

Reference: ORA-00821 ORA-01034 ORA-27101, SGA_target need to be increased [ID 815426.1]

Friday, July 6, 2012

Who controls load balancing with multiple CPUs, Oracle or OS?

Who controls load balancing with multiple CPUs, Oracle or OS?

Answer:

Load Balancing is not done by Oracle. The Operating System manages CPU control and usage.

The only references to CPU parameters in the init.ora file for Oracle are:
--CPU_COUNT: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams039.htm#REFRN10023
--PARALLEL_THREADS_PER_CPU: http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams183.htm

Neither of the above parameters are involved with Load Balancing of the CPU.

Reference: How to Load Balance Oracle with Multiple CPUs [ID 158090.1]

Oracle Drop Database Command

I did not know this command before until someone mentioned to me the other day! I am used to manually dropping databases on UNIX.

Anyhow, here is some info about this command.

DROP DATABASE

The DROP DATABASE command deletes these files from operating system.
* Datafiles
* Online Redo Log Files
* Controlfiles
* SPFILE (if it exists)

The DROP DATABASE command does not delete the following files:
* init.ora (text version of the Oracle initialization file)
* password file
* entries in listener files
* entries in oratab file

If the operating system is Windows, then it does not delete the Windows service for the target database instance.

Reference: Drop Database Command Versus Remove an Oracle Database Manually [ID 443822.1]

Friday, April 20, 2012

How to set up exclude_list files on NetBackup clients

Question: how to set up exclude_list files on NetBackup clients?

Answer: Symantec website has an excellent article to explain this: DOCUMENTATION: Additional examples for setting up exclude_list files on NetBackup clients

An exclude_list file can be created for a specific client, policy or schedule. The exclude_list file must be created by the administrator in the /usr/openv/netbackup directory on UNIX clients.

The following options exist when creating an exclude_list file:
- Create an exclude_list file if the exclude list will apply to all backups of the client.
- Create an exclude_list. if the exclude list will apply only for a specific policy.
- Create an exclude_list.. if the exclude list will apply to only one schedule in a policy.

NetBackup will use only read the exclude_list file that most closely matches the backup policy and schedule being performed. The and used in the file name must be an exact match to the policy and schedule defined on the master server.

The following examples are meant to show different methods to format an exclude_list on a NetBackup client. It is important to note that exclude list entries need to follow UNIX rules and conventions.

How to properly exclude a file name by the extension:

The following is a valid way to handle file names by extension:

*.log

*_log

This will properly exclude all files and directory names that end in .log or _log. The asterisk character is needed to do the wildcard expansion.

The incorrect way to exclude by extension is to use:

.log

_log

This will only match specific file or directory names for .log or _log. Without the asterisk character, there is no wildcard expansion done to match files ending in these entries.

How to properly exclude a filename that contains a specific string:

The following is a valid way to handle filenames that contain a specific string:

*.log.*

*_log_*

This will match any file or directory name that contains .log. or _log_. The period or underscore has no specific meaning in this example. The asterisk does the wildcard expansion.

How to properly exclude a specific filename or directory:

The following is a valid way to handle specific file or directory names.

log

logs

This will match any file or directory names called log or logs. In this case, no asterisk is needed since there isn't any matching to be done.

How to use the ? character in an exclude_list entry:

A valid way to use the ? character in an exclude_list entry would be:

log.??????

This would exclude any file that begins with log. and follows with six characters. This would match log.021804 but not match log. since log. must be followed by six characters. The ? is used as a wildcard for any single character in a file or directory name. Each question mark is used to match a single character.

How to use the [] characters in an exclude_list entry:

EXAMPLE 1: A valid way to use the [] characters in an exclude_list entry would be:
log[12345]

This would exclude the files log1, log2, log3, log4, log5 but would not match log or log12345. The characters inside the [] are used to match any single character that is contained between the [].

     EXAMPLE 2: Suppose path "/path" contains several subdirectories, u01, u02, u03, u04, u10, u12, u13, u14.

     If you only want to back up /path/u01, the following exclude entry would *not* work, and all subdirectories would be backed up:
             /path/u[02-14]

     To exclude every subdirectory except u01, two lines would be needed in the exclude_list, using the following syntax:
            /path/u0[2-4]
            /path/u1[0-4]

    An easy way to confirm before the backup whether the syntax is correct is to use an "ls" command. For the above incorrect syntax, "ls" would reveal that the syntax is incorrect:
           ls /path/u[02-14]
           No Match.

How to exclude a file or directory that contains a special character:
    A valid way to exclude files that contain special characters in the name would be:

\*logfile\*

This would match the file named *logfile* but would not match logfile or any filename that contains logfile. The backslash character will de-reference any of the special characters used in a NetBackup exclude_list.

These special characters are:

asterisk *

comment #

square brackets [ ]

backslash \

question mark ?

Reference: DOCUMENTATION: Additional examples for setting up exclude_list files on NetBackup clients

Clone database from a RMAN Cold back in Oracle 10g

Database environment:

· Oracle version: 10.2.0.4se

· RMAN version: 10.2.0.4

· OS: Solaris 10

· Target database: TargetDB

· Auxiliary database: AuxDB

· RMAN catalog database: RcatDB

1. Take a cold backup from the target database TargetDB (source database) and using RMAN catalog database RcatDB

2. Prepare the directory structure for the auxiliary database AuxDB (cloned database)

Scenario:

· The target database and the auxiliary database are on different database servers.

· Need to copy the cold backups of the target database from one server to the server where the auxiliary database resides.

· The file structure of the cold backup should be the same as on the target database server.

3. Ensure the following two parameters are included in the pfile:

############################################

# for rman database duplication

###########################################

db_file_name_convert=('/oracle_data/TargetDB', '/oracle_data/AuxDB')

log_file_name_convert=('/oracle_data/TargetDB', '/oracle_data/AuxDB')

4. Add the auxiliary database to /etc/oratab:

AuxDB:/fs/u02/sw_ux/oracle/product/10.2.0.4se:Y

5. Add the auxiliary database to tnsnames.ora

AuxDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))

)

(CONNECT_DATA =

(SID = AuxDB)

(SERVER = DEDICATED)

)

6. Add a Oracle password file for the auxiliary database

orapwd file=$ORACLE_HOME/dbs/orapwAuxDB password= entries=10

7. Start the auxiliary database in NOMOUNT mode

$ . oraenv

ORACLE_SID = [AuxDB] ? AuxDB

The /oracle/product/10.2.0.4se/bin/orabase binary does not exist

You can set ORACLE_BASE manually if it is required.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Oct 25 11:26:32 2010

Connected to an idle instance.

SQL> create spfile from pfile='/oracle/admin/AuxDB/pfile/initAuxDB.ora';

File created.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 683671552 bytes

Fixed Size 2043136 bytes

Variable Size 276828928 bytes

Database Buffers 398458880 bytes

Redo Buffers 6340608 bytes

8. Use RMAN to clone the auxiliary database

$ rman trace clone_AuxDB.log

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Oct 25 11:44:53 2010

RMAN> connect target sys/syspwd@TargetDB;

connected to target database: TargetDB (DBID=1722637034)

RMAN> connect catalog rman/rcpwd@RcatDB;

connected to recovery catalog database

RMAN> connect auxiliary /

connected to auxiliary database: AuxDB (not mounted)

RMAN> duplicate target database to AuxDB;

Finished restore at 25-OCT-10

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUXDB" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 30

MAXINSTANCES 1

MAXLOGHISTORY 2337

LOGFILE

GROUP 1 ( '/oracle_data/AuxDB/redo01.log', '/oracle_data/AuxDB/redo01b.log' ) SIZE 10 M REUSE,

GROUP 2 ( '/oracle_data/AuxDB/redo02.log', '/oracle_data/AuxDB/redo02b.log' ) SIZE 10 M REUSE,

GROUP 3 ( '/oracle_data/AuxDB/redo03.log', '/oracle_data/AuxDB/redo03b.log' ) SIZE 10 M REUSE

DATAFILE

'/oracle_data/AuxDB/system_01.dbf'

CHARACTER SET UTF8

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

released channel: ORA_AUX_DISK_1

datafile 3 switched to datafile copy

input datafile copy recid=1 stamp=733319493 filename=/oracle_data/AuxDB/sysaux_01.dbf

datafile 12 switched to datafile copy

input datafile copy recid=10 stamp=733319493 filename=/oracle_data/AuxDB/users01.dbf

datafile 13 switched to datafile copy

input datafile copy recid=11 stamp=733319493 filename=/oracle_data/AuxDB/undotbs01.dbf

contents of Memory Script:

{

recover

clone database

noredo

delete archivelog

;

}

executing Memory Script

Starting recover at 25-OCT-10

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=104 devtype=DISK

Finished recover at 25-OCT-10

contents of Memory Script:

{

shutdown clone;

startup clone nomount ;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 683671552 bytes

Fixed Size 2043136 bytes

Variable Size 276828928 bytes

Database Buffers 398458880 bytes

Redo Buffers 6340608 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUXDB" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 30

MAXINSTANCES 1

MAXLOGHISTORY 2337

LOGFILE

GROUP 1 ( '/oracle_data/AuxDB/redo01.log', '/oracle_data/AuxDB/redo01b.log' ) SIZE 10 M REUSE,

GROUP 2 ( '/oracle_data/AuxDB/redo02.log', '/oracle_data/AuxDB/redo02b.log' ) SIZE 10 M REUSE,

GROUP 3 ( '/oracle_data/AuxDB/redo03.log', '/oracle_data/AuxDB/redo03b.log' ) SIZE 10 M REUSE

DATAFILE

'/oracle_data/AuxDB/system_01.dbf'

CHARACTER SET UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"/oracle_data/AuxDB/temp_01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/oracle_data/AuxDB/sysaux_01.dbf";

catalog clone datafilecopy "/oracle_data/AuxDB/data_01.dbf";

catalog clone datafilecopy "/oracle_data/AuxDB/indexes_01.dbf";

catalog clone datafilecopy "/oracle_data/AuxDB/users01.dbf";

catalog clone datafilecopy "/oracle_data/AuxDB/undotbs01.dbf";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /oracle_data/AuxDB/temp_01.dbf in control file

cataloged datafile copy

datafile copy filename=/oracle_data/AuxDB/sysaux_01.dbf recid=1 stamp=733319503

cataloged datafile copy

datafile copy filename=/oracle_data/AuxDB/undotbs01.dbf recid=11 stamp=733319503

datafile 3 switched to datafile copy

input datafile copy recid=1 stamp=733319503 filename=/oracle_data/AuxDB/sysaux_01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=2 stamp=733319503 filename=/oracle_data/AuxDB/data_01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=5 stamp=733319503 filename=/oracle_data/AuxDB/indexes_01.dbf

datafile 8 switched to datafile copy

input datafile copy recid=10 stamp=733319503 filename=/oracle_data/AuxDB/users01.dbf

datafile 13 switched to datafile copy

input datafile copy recid=11 stamp=733319503 filename=/oracle_data/AuxDB/undotbs01.dbf

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 25-OCT-10

RMAN>

RMAN> exit

Recovery Manager complete.

9. Complete.

DBA_REGISTRY_HISTORY IS SHOWING 11.2.0.20 FOR THE COMMENT COLUMN FOR 11.2.0.3

After upgrading from 11.2.0.2se to 11.2.0.3se, DBA_REGISTRY_HISTORY shows Patchset 11.2.0.2.0 for 11.2.0.3. For example:

select * from dba_registry_history order by action_time;

ACTION_TIME	ACTION	NAMESPACE	VERSION	ID	BUNDLE_SERIES	COMMENTS
2007-04-27 1:24:06.000000 PM	CPU	SERVER	10.2.0.2.0	5689957		CPUJan2007
2009-05-23 2:38:48.000000 AM	UPGRADE	SERVER	10.2.0.4.0			Upgraded from 10.2.0.2.0
2011-11-10 12:31:30.905842 PM	VIEW INVALIDATE			8289601		view invalidation
2011-11-10 12:31:31.708360 PM	UPGRADE	SERVER	11.2.0.3.0			Upgraded from 10.2.0.4.0
2011-11-10 12:59:23.315431 PM	APPLY	SERVER	11.2.0.3	0	PSU	Patchset 11.2.0.2.0

Oracle has confirmed that it is a bug (Bug 13360611).

DBA_REGISTRY_HISTORY provides information about upgrades, downgrades, and critical patch updates that have been performed on the database.

Column	Datatype	NULL	Description
ACTION_TIME	TIMESTAMP(6)		The time the upgrade, downgrade, or patch action was completed
ACTION	VARCHAR2(30)		The specific action (for example, UPGRADE or DOWNGRADE)
NAMESPACE	VARCHAR2(30)		The namespace of the components affected (for example, SERVER)
VERSION	VARCHAR2(30)		The version number of the server (for example, 10.2.0.1.0)
ID	NUMBER		The identification number of the Critical Patch Update
COMMENTS	VARCHAR2(255)		Additional comments about the action taken

Spatial DBA - Oracle and ArcSDE

Monday, June 10, 2013

ORA-16038 ORA-19809 ORA-00312

Tuesday, November 27, 2012

Oracle changePerm.sh script in versions 9.2.0.8, Oracle 10g

Friday, October 26, 2012

Oracle Database Automatic Startup and Shutdown on UNIX

Monday, July 9, 2012

ORA-00821 ORA-01034 ORA-27101, SGA_target and SPARC T series database servers

Goal

Fix

Friday, July 6, 2012

Who controls load balancing with multiple CPUs, Oracle or OS?

Oracle Drop Database Command

DROP DATABASE

Friday, April 20, 2012

How to set up exclude_list files on NetBackup clients

Reference: DOCUMENTATION: Additional examples for setting up exclude_list files on NetBackup clients

Clone database from a RMAN Cold back in Oracle 10g

DBA_REGISTRY_HISTORY IS SHOWING 11.2.0.20 FOR THE COMMENT COLUMN FOR 11.2.0.3

Popular Posts in Last 30 Days

About Me

My favorites

Blog Archive

Labels

Disclaimer