PeopleTools 8.46, 8.47, 8.48, and 8.49 – How Do You find out if Oracle Database has been installed as Unicode

Unicode DB must be using a supported Oracle/PeopleSoft unicode character set. (UTF8)
You can check what is the value by running the following query in the DB instance in question.

SQL> SELECT PARAMETER, VALUE from V$NLS_PARAMETERS where PARAMETER=’NLS_CHARACTERSET’;

PARAMETER VALUE
——————————–
NLS_CHARACTERSET
UTF8

In addition, PeopleTools must be told that it is utilizing a UNICODE db and that is done by the setting of the UNICODE_ENABLED boolean flag on the PSSTATUS table. A value of 1 indicates this DB is to be treated by tools as a UNICODE DB.

SQL> SELECT UNICODE_ENABLED from PSSTATUS;

UNICODE_ENABLED
—————————–
1

PeopleTools 8.4x Unicode vs. non-Unicode Tools Installation

What’s the difference between a Unicode and non-Unicode Tools install?

SOLUTION:
If UNICODE_ENABLED=1 on the PSSTATUS table, then you definitely have a Unicode installation. If UNICODE_ENABLED=0, then you do NOT have a Unicode installation.

COBOL DIFFERENCES
After you enter your PeopleSoft Licence Code during the PeopleTools installation, there are two radio buttons. You are asked to choose whether the installation is ANSI or Unicode. If you choose Unicode, a unicode.cfg file is generated in the %PS_HOME%\setup directory. This file is subsequently sent to the Unix box during the PeopleSoft Batch Transfer (pstrans.exe).

The COBOL codeline is the same. The only difference is the one file, unicode.cfg in the \setup directory. This is not found in a non-Unicode installation. In a Unicode installation, the file has one section [unicode] and one line, unicode = 1.

This file impacts the compile process of our source COBOL. There is a flag in the pscbl.mak script which looks for the existence of this file. If it exists, then an additional script is run to perform the unicode conversion and compilation.

For NT Customers with unicode db, run CBL2UNI.BAT for any NT Servers which host the COBOL executables. This also applies to Unix Customers using an NT Application server to support Remote call COBOL.

NOTE: When running a Unicode database the parameter RCCBL PRDBIN needs to be set. The line delivered — %PS_HOME%\cblbinU is only an example, itn may not be your actual path.
Customer used %PS_HOME%/cblbin — NOT %PS_HOME%\cblbinU. No U and / not \.

ISSUE:
Running both Unicode and non-Unicode databases.
When attempting to run a calc from the process scheduler we are getting the message below. Is it a problem with Server Express, compiled COBOL or something else?
Error Message : SQLRT: Using Unicode COBOL for an Ansi DB

SOLUTION:
Unicode and non-Unicode installations can not share the same compiled codeline. The Unicode databases require the COBOL programs to be converted/compiled appropriately for Unicode.

If each database instance has its own codeline on the Unix box that was brought over using PSTRANS.exe from the FileServer PeopleTools installation, it should be possible. If necessary, remove/rename the unicode.cfg file from $PS_HOME/install in the ANSI environments and re-compile the COBOL.

See also:
Resolution 709697: Remote COBOL program (GLPJEDIT) failed with return code: 9977, Reason: SQLRT: Attempting to use Ansi API for a Unicode DB ;

E-INST Unicode vs. non Unicode. 8.16 Tools
Details: Unicode Databases
In addition to supporting several legacy character sets, PeopleSoft supports creating Oracle databases using Unicode. Unicode enables you to maintain data in virtually any modern language in a single database. Prior to Unicode, many languages could not co-exist in one database, as they did not share a common encoding scheme.

To create an Oracle Unicode database, the character set UTF8 must be specified in the CREATE DATABASEstatement. PeopleSoft triples column sizes when running against a Unicode Oracle database, as all Oracle 8i column sizes are measured in bytes, and PeopleSoft uses character-based column sizes. This tripling in size represents the worst-case 1-to-3 ratio of characters-to-byte for Basic Multilingual Plane Unicode characters when represented in UTF8.

Unicode databases are particularly important if the languages you selected do not share the same character set. Typically, a single character set can encode all languages written in a single script. For example, English, French and Spanish all share the same script (Latin), so they can co-exist in a non-Unicode database. However, Japanese does not share the same script as French, so if you need to have Japanese and French co-exist in a single system, you will require a Unicode database.

Note! The characters required for the English language exist in all Unicode and non-Unicode character sets, so you can safely disregard English when you are determining whether to use Unicode for your database. For
example, Japanese and English can co-exist in a single Unicode or non-Unicode database. If you plan on installing or supporting a combination of languages that do not share the same character set, you should use a Unicode database. On Oracle, Unicode characters require between 1 and 3 bytes of storage each. As characters needed to write English are represented with one byte in UTF8, you should see no additional space requirements for a UTF8 database when using primarily English characters.If you decide to use Unicode for your database, you do not need to select a character set as described below.

Non-Unicode Databases
If you plan on running only one language in your system, or if your selected languages share the same character set, you can safely create a non-Unicode database. In this case, you need to decide in which character set your database should be created.
On Oracle, PeopleSoft supports the following Oracle character sets. Should one or more languages you are planning on using not appear in this list, you will need to create a Unicode database, as this is the complete list of Oracle non-Unicode character sets supported by PeopleSoft.

When installing a PeopleSoft unicode database, you will run the rel8xxu.sql scripts as part of the installation process.

PeopleTools 8.4x When to Enable NLS_LENGTH_SEMANTICS=CHAR during an Upgrade

 FSCM 9.0/Tools 8.48 upgrade (Unicode Database) and the upgrade document tells you to do the following change in the database before running the datatype conversion:

“CHARACTER LENGTH SEMANTICS (CLS) only applies to Unicode databases. The CLS conversion has a dependency on the init.ora parameter NLS_LENGTH_SEMANTICS. The init.ora parameter NLS_LENGTH_SEMANTICS=CHAR, must be enabled for PeopleSoft Enterprise Unicode databases prior to executing the conversion”.

Can you enable the parameter at the beginning of the upgrade in the Copy of Production database?

You cannot set that parameter UNTIL the point in time that the upgrade document tells you to do so (e.g. right before starting the data type conversion). Otherwise it will adversely affect the tools upgrade, specifically resulting in errors in altering the tools tables (trying to insert BLOB data into LONG fields). That is why the instructions to set the parameter are not found until the middle of the upgrade

PeopleTools 8.48 / 8.49 Incorrect Character Length is Displayed

When describing a table with SQLPlus, it shows that the field is three times longer than what it should be. In older versions of Oracle and PeopleSoft this was normal, however in the newer versions of PeopleSoft and Oracle it should be reporting back the correct lengths.

TEST CASE:

Create a record through Application Designer.
Application Designer build script confirms that Application Designer no longer triples the VARCHAR2 datatype columns. For example , If emplid is varchar(11) then Application Designer creates the build scripts as varchar(11) and not as varchar(33) like older versions.
However, when run a descr on the table through sql*plus ,it shows the tables as varchar(33) in database instead of varchar(11).

SOLUTION:

The issue is caused by running mistmatched versions of connectivity tool (SQL*PLUS) and database server. Older client (SQL*PLUS) versions and some of the third party query tools cannot see CHAR semantics parameters in the database and they will get the length returned in BYTE. As an example 10g UTF8 database with a VARCHAR2(10) column will show up in a 9i client as VARCHAR2(30).

The PeopleSoft Unicode implementation for PT8.48 and higher no longer triples the VARCHAR2 datatype columns. Instead we rely on an Oracle feature called CHARACTER LENGTH SEMANTICS. This parameter is not needed for non-Unicode databases or for pre 9.0 Unicode application databases.
The parameter you must add is NLS_LENGTH_SEMANTICS=CHAR.

PeopleTools: Publish/Subscribe Servers Crashing Intermittently on Oracle 10.2.0.1

After upgrading the DBMS server to Oracle 10.2.0.1, the application server publication/subscription servers crash intermittently.
The following is an example shown in app server log.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:54:59](0) Unhandled exception occurred. Writing crash dump to PSPUBDSP_dflt.8912\dump
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 1400.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 3123.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 2206.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 3803.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 2253.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 3082.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) PSAFFIRM(!nSqlRecurse) failed at f:\pt84605b-retail\peopletools\SRC\pssam\samlib.cpp, line 2037.
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) SQL error. Stmt #: 2475 Error Position: 28 Return: 24909 – ORA-24909: call in progress. Current operation cancelled
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](1) GenMessageBox(200, 0, M): f:\pt84605b-retail\peopletools\SRC\pspubsub\pubsubdispatcher.cpp: SQL error. Stmt #: 2475 Error Position: 28 Return: 24909 – ORA-24909: call in progress. Current operation cancelled
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](0) Sam SqlError: SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD-HH24.MI.SS.”000000″‘) FROM PSCLOCK
PSPUBDSP_dflt.8912 (0) [02/17/06 11:55:02](1) GenMessageBox(200, 0, M): SQL Access Manager: A SQL error occurred. Please consult your system log for details.

But there is nothing specific in crash dump.

On the application server box the database client is at Oracle 10.1.0.1 while the database server had been upgraded to 10.2.0.1. Peoplesoft requires that the database client and server are on the same patch level.
In this case, applying the Oracle 10.2.0.1 patch to the database client on the application server resolved the issue.

The following are some notes for Oracle 10g patches for PT8.46.

Note, after applying the patch the libclntsh lib will need to be re-generated.

NOTE: Problems applying the Oracle Patch. The following instructions, provided by Oracle database group, resolved the install problem.
1. Make backup copies of 4425566/etc/config/actions and 4425566/etc/config/inventory
2. Edit 4425566/etc/config/actions, change two occurrances of the string “oracle.rdbms” to
“oracle.client”
3. Edit 4425566/etc/config/inventory, change one occurrance of the string “oracle.rdbms” to “oracle.client”
4. Retry “opatch apply”
____________________

For Oracle 10g
The minimum version of Oracle 10g and patches required for PT8.46 are:
For all Platforms but HP-Itanium and Windows Oracle 10.1.0.3.0 + (Merged patch 4113001 comprised of one-off patches 3612581 and 3907008) will be the minimum requirement.
For Windows 32bit Oracle 10.1.0.3.0 + (Patch# 4074232) which is a cumulative patch that includes patches 3612581 and 3907008) will be the minimum requirement.
For Windows 64bit Oracle 10.1.0.3.0 + (Patch# 3990812) which is a cumulative patch that includes patches 3612581 and 3907008) will be the minimum requirement.
For HP-Itanium Oracle 10.1.0.3.0 + (Merged patch 4113001 comprised of the following one-off patches 3612581, 3907008, and 3975759) will be the minimum requirement.
HP-Itanium Oracle 10.1.0.4.0 patchset + (Interim patch# 3975759) Note: These minimum requirements apply to both the Server and Client installations.

Patch#4113001 comprised of one-off patches 3612581 and 3907008 addresses the following issues.

Patch# 3612581 addresses randomly generated ORA-00600 error messages and core dumps when running SQR with Oracle 10g.

Patch# 3907008 addresses a problem of writing a specific range of Unicode characters into a column.

Patch# 3975759 addresses a problem of connecting to the DB server via the Oracle client library on the HP-Itanium platform only. This patch was originally generated for Oracle patchset 10.1.0.2.0 and subsequently rolled up into a bundled interim patch# 4113001 to be applied on top of the 10.1.0.3.0 patchset. This interim patch# 3975759 did not get rolled up into the 10.1.0.4.0 patchset for the HP-Itanium platform. Need to apply interim patch# 3975759 to the 10.1.0.4.0 patchset for the HP-Itanium platform.

PeopleSoft PeopleTools 8.48 and HRMS 8.9 Multilingual Oracle Database Install

PeopleTools 8.48 and PeopleSoft HRMS 8.9 w/Multilingual Oracle Install

The first createdbxx.sql script shows an incorrect way to create a PeopleSoft Database for a Unicode Installation. The second script is correct.

In this situation, the user has created the database using the incorrect character set WE8ISO8859P15:
Here is the create database script:
>
> CREATE DATABASE HRDMO
> maxdatafiles 1021
> maxinstances 1
> maxlogfiles 8
> maxlogmembers 4
> CHARACTER SET WE8ISO8859P15
> NATIONAL CHARACTER SET UTF8

I compared this to the script for a successful unicode install:
SQL> CREATE DATABASE HR89DMO
2 maxdatafiles 1021
3 maxinstances 1
4 maxlogfiles 8
5 maxlogmembers 4
6 CHARACTER SET UTF8
7 NATIONAL CHARACTER SET UTF8

For additional information, please refer to the PT8.4x Installation guide for Oracle (Task on “Planning Multilingual Strategy”). The following is an extract from the task:
Using Unicode Databases
In addition to supporting several legacy character sets, PeopleSoft supports creating Oracle databases using Unicode. Unicode enables you to maintain data in virtually any modern language in a single database. Prior to Unicode, many languages could not coexist in one database, as they did not share a common character set. To create an Oracle Unicode database, you must specify the character set UTF8 in the CREATE DATABASE statement. PeopleSoft triples column sizes when running against a Unicode Oracle database, as all Oracle
column sizes are measured in bytes, and PeopleSoft uses character-based column sizes. This tripling in size represents the worst-case 1-to-3 ratio of characters-to-byte for Basic Multilingual Plane Unicode characters
when represented in UTF8.

Database Hangs, Temporary Tablespace Has Been Exhausted

Database Appears to be Hung

No new users can connect to the database, queries appear to be hanging, system status shows no blockers and attempts to execute new queries fail – all are possible signs or indications that you’ve exhausted the Temporary Tablespace.

Once the Temporary Tablespace cannot extend: all existing and future transactions that need TEMP space will not complete and this can lead to a cascading series of failure events as objects and rows become locked preventing users from completing their tasks.

You can determine which process is or has consumed all of the space available in the Temporary Tablespace by checking the alert log. You can monitor the growth of the TEMP segment space with the following query:

select b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status from v$session a, v$sort_usage b where a.saddr = b.session_addr order by b.blocks, b.tablespace;

Corrective Actions Include:

• Increase the TEMPORARY TABLESPACE size

• Create new TEMPORARY Tablespaces and assign different users to the different TEMPORARY Tablespaces

• Increase the TEMPORARY TABLESPACE size by enabling autoextend on and setting a maxsize

RMAN uses a considerable amount of temporary tablespace. If you use RMAN you need to make sure that you generate statistics on the system schema’s fixed objects otherwise, RMAN’s performance may suffer and possibly hang executing backups.

Temporary Tablespace Errors in a RAC Environment

In a RAC environment there are two possible scenarios where an ORA-1652 error can occur:

• The TEMP Tablespace is completely exhausted

• The local temp segment cannot extend but space for this temp tablespace is available on other instances

To find out which scenario is occurring you can execute the following SQL:

select sum(free_blocks) from gv$sort_segment where tablespace_name = ”;

If the free blocks is equal to ‘0′, then you’ve hit scenario number 1 and are completely out of space. In this case you need to decide whether or not to extend the size of the temporary tablespace by either adding a datafile or enabling autoextend (if not enabled).

If sufficient space is available after executing the above query then you are likely running in the situation described by scenario 2 where ORA-1652 errors are handled differently in a RAC environment than a non-RAC environment when the local instance cannot extend it’s temp segment. In a non-RAC environment, an ORA-1652 can cause all SQL utilizing that tablespace to fail. In RAC, you may be able to get additional sort segment space from other instances. To see how space is allocated across all instances execute the following query:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Total_blocks will show the total number of temp segment space available for each instance. Used_blocks will show how much of that space has been utilized. Free_blocks will show how much space has been ALLOCATED to this instance. If you are getting ORA-1652 errors on an instance, you will likely see that used_blocks = total_blocks and free_blocks = 0. If this happens, you may see ORA-1652 errors repeated in the alert log.

When this happens (you see repeated requests for temporary segment space from other instances), we are requesting free space from another instance. This should be considered a warning to the DBA that there is instance contention for temporary space. This may cause the instance to take longer to service internal temporary-sapce requests because inter-node coordination is required.

If there is severe temp space contention across instances, a slowdown most likely will occur. The following are potential workarounds:

• Increase the size of the temporay tablespace

• Do not use the DEFAULT temp tablespace feature in RAC

• Increase the sort_area_size and/or pga_aggreagate_target

If there is a severe slowdown, it is likely that SMON is unable to process sort segment requests because it is busy doing other work. The following diagnostics may be needed to diagnose inter-node sort segment contention:

- Output from the following query periodically while the problem is occuring:

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

- Global hanganalyze and systemstate dumps.

ORA-1652: “unable to extend temp segment by %s in tablespace %s”

Failed to allocate an extent for temp segment in tablespace is the cause. The action to alleviate the problem is to use ALTER TABLESPACE ADD DATAFILE to add one or more files to the tablespace indicated or create the object in another tablespace or turn on autoextend (if not enabled).

A “temp segment” is not necessarily a SORT segment in a temporary tablespace.

It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablesapces. When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data. This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation. It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend it report ORA-1652 rather than an INDEX related space error.

A TEMPORARY segment may be from:

• A SORT – used for a SELECT or for DML/DDL

• CREATE INDEX – the index create performs a SORT in the user’s default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace. Once the index build is complete the segment type is changed.

• CREATE PK CONSTRAINT

• ENABLE CONSTRAINT

• CREATE TABLE – New tables start out as TEMPORARY segments. eg. If MINEXTENTS is > 1 or you issue CREATE table as SELECT

• Accessing a GLOBAL TEMPORARY TABLE – When you access a global temporary table a TEMP segment is instantiated to hold the temporary data

It is worth making sure the TEMP tablespaces PCTINCREASE is 0 and that it has a sensible (large) storage clause to prevent fragmentation. For TEMPORARY temp tablespaces make sure both INITIAL and NEXT are set to large values as extent sizes are taken from the NEXT clause and not the INITIAL clause.

RMAN Catalog Info

Resynchronizing the Recovery Catalog

When RMAN performs a resynchronization, it compares the recovery catalog to either the current control file of the target database, or a backup control file and updates the recovery catalog with information that is missing of has changed. When resynchronizing RMAN does the following:

• Creates a snapshot controfile

• Compares the recovery catalog to the snapshot control file

• Updates the recovery catalog with missing or changed information

RMAN performs resynchronization automatically as needed when you execute certain commands, including BACKUP. You can also manually perform a full resynchronization using the RESYNC CATALOG command.

Types of Records Updated When The Recovery Catalog is Resynchronized

• Log History – created when an online redo log switch occurs

• Archived Redo Logs – associated with archived logs that were created by archiving an online log, copying an existing archived redo log, or restoring an archived redo log backup set. RMAN tracks this information so that is knows which archived logs it should expect to find

• Backup History – associated with backup sets, backup pieces, proxy copies and file copies. The RESYNC CATALOG command updates these records when a BACKUP command is executed

• Incarnation History – associated with database incarnations

• Physical schema – associated with datafiles and tablespaces. If the target database is open, then undo segment information is also updated. Physical schema information in the recovery catalog is updated only when the target has the current control file mounted. If the target database has mounted a backup control file, a freshly created control file, or a control file that is less current than a current file that was previously seen, then the physical schema information in the recovery catalog is not updated. Physical schema information is also not updated when you use the RESYNC CATALOG FROM CONTROLFILECOPY command.

Full and Partial Resynchronization

Resynchronization can be full or partial. In a partial resynchronization, RMAN reads the current control file to update changed information about new backups, new archived logs, and so forth. However, RMAN does not synchronize metadata about the database physical schema: datafiles, tablespaces, redo threads, rollback segments (only if the database is open), and offline redo logs. In a full resynchronization, RMAN updates all changed records, including those for the database schema.

Note: Although RMAN performs partial resynchronizations when using a backup control file, it does not perform full resynchronizations. A backup control file may not have correct information about the database physical schema, so a full resynchronization could update the recovery catalog with incorrect information.

When To Resynchronize the Recovery Catalog

RMAN automatically performs a full or partial resynchronization in most situations in which they are needed. Most RMAN commands such as BACKUP, DELETE and so forth perform a full or partial resynchronization (depending on whether the schema metadata has changed) automatically when the target database control file is mounted and the recovery catalog database is available. Thus, you should not need to manually run RESYNC CATALOG very often if ever.

Resynchronizing After the Recovery Catalog is Unavailable

If the recovery catalog is unavailable when you issue RMAN commands that cause a partial resynchronization, then open the catalog database later and resynchronize it manually with the RESYNC CATALOG command.

For example, the target database may be in Dallas, TX while the recovery catalog is in Florida. You may not want to make daily backups of the target database in CATALOG mode, to avoid depending on the availability of a geographically distant database. In such a case you could connect to the catalog as often as feasible (for example, once a week) and run the RESYNC CATALOG command.

Resynchronizing in ARCHIVELOG Mode When You Back Up Infrequently

Assume that you do the following:

• Run the database in ARCHIVELOG mode

• Backup the database infrequently (for example, hundreds of archive logs are archived between database backups)

• Generate a high number of log switches every day (for example, 1000 switches between catalog resynchronizations)

In this case, you may want to manually resynchronize the recovery catalog regularly because the recovery catalog is not updated automatically when a redo log switch occurs or when a redo log is archived. The database stores information about log switches and archived redo logs only in the control file. You must periodically resynchronize in order to propagate this information into the recovery catalog.

How frequently you need to resynchronize the recovery catalog depends on the rate at which the database archives redo logs. The cost of the operation is proportional to the number of records in the control file that have been inserted or changed since the previous resynchronization. If no records have been inserted or changed, then the cost of resynchronization is very low; if many records have been inserted or changed, then the resynchronization is more time-consuming.

Resynchronizing After Physical Database Change

Resynchronize the recovery catalog after making any change to the physical structure of the target database. As with redo log archive operations, the recovery catalog is not updated automatically after physical schema changes, such as adding or dropping tablespaces, adding datafiles to a tablespace, or adding or dropping rollback segments.

Forcing a Full Resynchronization of the Recovery Catalog

Use RESYNCT CATALOG to force a full resynchronization of the recovery catalog.

1. Connect RMAN to the target and recovery catalog databases, and then mount or open the target database if not already mounted or open:

startup mount;

1. Run the RESYNC CATALOG command at the RMAN prompt:

RESYNC CATALOG;

Resynchronizing the Recovery Catalog and CONTROL_FILE_RECORD_KEEP_TIME

If you maintain a recovery catalog, then use the RMAN RESYNC CATALOG command often enough to ensure that the control file records are propagated to the recovery catalog before they are reused.

Make sure that CONTROL_FILE_RECORD_KEEP_TIME is longer than the interval between backups or resynchronizations. Otherwise, control file records could be reused before they are propagated to the recovery catalog. An extra week is a safe margin in most circumstances.

Note: Never set CONTROL_FILE_RECORD_KEEP_TIME to 0. If you do, then backup records may be overwritten in the control file before RMAN is able to add them to the catalog.

Managing the Control File When You Use a Recovery Catalog

The goal is to ensure that the metadata in the recovery catalog is current. Because the recovery catalog obtains its metadata from the target control file, the currency of the data in the catalog depends on the currency of the data in the control file. You need to make sure that the backup metadata in the control file is recorded in the catalog before it is overwritten with new records.

The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter determines the minimum number of days that records are retained in the control file before they are candidates for being overwritten. Hence, you must ensure that you resynchronize the recovery catalog with the control file records before these records are erased. You should perform either of the following actions at intervals less than CONTROL_FILE_RECORD_KEEP_TIME setting:

• Make a backup, thereby, performing an implicit resynchronization of the recovery catalog

• Manually resynchronize the recovery catalog with the RESYNC CATALOG command

So, to ensure the currency of the information in the recovery catalog, the frequency of resynchronizations should be related to the value for the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter.

One problem can arise if the control file becomes too large. The size of the target database control file grows depending on the number of:

• Backups that you perform

• Archived redo logs that the database generates

• Days that this information is stored in the control file

If the control file grows so large that it can no longer expand because it reached either the maximum number of blocks or the maximum number of records, then the database may overwrite the oldest records even if their age is less than the CONTROL_FILE_RECORD_KEEP_TIME setting. In this case, the database writes a message to the alert log. If you discover that this situation occurs frequently, then reducing the value of CONTROL_FILE_RECORD_KEEP_TIME and increase the frequency of resynchronizations will help to prevent that condition.

Note: The maximum size of the control file is port-specific. Typically, the maximum size is 20,000 Oracle blocks.