photo

I forgot my root password, how can I get into my system?

Issue:
I forgot my root password, how can I get into my system? Resolution:You can change your root password from single user mode or rescue mode. Getting into single use mode depends on your bootloader:

GRUB
Booting into single user mode using GRUB is accomplished by editing the kernel line of the boot configuration. This assumes that either the GRUB boot menu is not password protected or that you have access to the password if it is.

If the GRUB boot menu is password protected and you do not have access to the password, then you will need to use a rescue disk to boot the system. Follow the instructions given by the rescue disk boot process to recover your installation and then chroot to your system image (usually accomplished by issuing the command chroot /mnt/sysimage). From this point you should be able to use the passwd to change the root password of the system.

At the boot prompt, select the kernel that you wish to boot with and press 'e' (for edit). You will now be taken to a screen where you can edit the boot parameters. Move the cursor to the kernel line and press 'e' again. Now append an 'S' to the end of the line, press Return, and then 'b' (for boot). The system will now start in single user mode and you can change the root password using the passwd command.

LILO
When the system comes to the LILO: prompt, type linux single . When you get the # prompt you will need to type passwd root. This will update the password to a newer one. At this point you can type exit and your system should return to the boot sequence. Alternatively, you can reboot your system with the shutdown -r now or reboot commands. The system should boot up normally. You can now use your new root password to gain root access.

If LILO is configured to not wait at the boot menu (timeout value in /etc/lilo.conf set to 0) you can still halt the boot process by pressing any key in the split second before LILO boots the kernel.

Database is Shown with Status 'Pending' in Grid Control

Subject: Problem: Database is Shown with Status 'Pending' in Grid Control
Doc ID: Note:312797.1 Type: PROBLEM
Last Revision Date: 26-MAR-2007 Status: PUBLISHED

In this Document
Symptoms
Cause
Solution



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



Applies to: Enterprise Manager Grid Control - Version: 10.1.0.2 to 10.1.0.4
This problem can occur on any platform.
SymptomsDatabase is shown with status pending in Grid Control.
CauseThe DBSNMP user on the databases is locked and the dbsnmp password has not been configured
for the database. SolutionTo implement the solution, please execute the following steps:

1. Connect to database using sqlplus as sysdba and run the following command:



alter user DBSNMP account unlock;

2. Configure DBSNMP passwords for each database:


In Grid Control go to Management System > Agents and click on the link for the agent of the database
Select the database from the list of targets and click on the 'Configure' button
Specify the correct dbsnmp password in the 'Monitor Password' field
Click on the 'Test Connection' button.
You should see a 'Success' indication at the top of the page. Click Next and complete the Configure Database dialog appropriate to your needs.


3. Wait for 4 minutes before checking the status of the newly configured database (It should take a maximum of 4 minutes to determine the status of the database)

Keywords'GRID~CONTROL' 'CONFIGURE~DATABASE' 'LOCK' 'DBSNMP'
--------------------------------------------------------------------------------

Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace

Subject: Grid Control install failed with Ora-30041: Cannot grant quota on the tablespace
Doc ID: Note:329375.1 Type: PROBLEM
Last Revision Date: 27-JUL-2005 Status: MODERATED

In this Document
Symptoms
Cause
Solution



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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Enterprise Manager Grid Control - Version: 10.1.0.3.0
This problem can occur on any platform.

Symptoms
Getting error ORA-30041: Cannot grant quota on the tablespace when installing Grid Control 10.1.0.3.0 using an existing 10.2 database.
Cause
10gR2 databases are not supported to be used as the Oracle Management Repository for Grid Control release 1.
Solution
To implement the solution, please execute the following steps:

1. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using a New database' option

OR

2. Install the Grid Control using the 'Enterprise Manager 10g Grid Control Using an Existing Database' option with a 9.2.0.5+ or 10.1.0.3+ database but not a 10gR2 database.

Errors
ORA-30041 Cannot grant quota on the tablespace

If you grid is 10g Release 3 (10.2.0.3.0), ensure the database release should be 9.2.0.6 and later, or 10.1.0.4 and later.

How to Clean Up a Failed Grid Control Installation

Subject: How to Clean Up a Failed Grid Control Installation
Doc ID: Note:434858.1 Type: HOWTO
Last Revision Date: 04-JUN-2007 Status: MODERATED

In this Document
Goal
Solution
Option 1: Configuration Assistant
Option 2: Full Clean up of Failed Installation



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


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



Applies to:
Enterprise Manager Grid Control - Version: 10.2.0.0
Information in this document applies to any platform.

Goal
The following provides 2 different options on how to progress with a failed installation for Grid Control.

IMPORTANT NOTE: Please make sure that any issues identified has been corrected before re-attempting to complete installation
Solution
Option 1: Configuration Assistant
If partial installation has failed, it is possible to manually run the following Configuration Assistants rather than performing a re-installation.

NOTE: In some circumstances a full reinstallation process may be required. Please see Option 2, for procedure on full cleanup and re-installation.

OMS:

runConfig.sh ORACLE_HOME= ACTION=Configure MODE=Perform

Please see Note 362217.1 for further details on using runConfig.sh

Agent:

/bin/agentca -f

Option 2: Full Clean up of Failed Installation
1. Abort the runInstaller wizard.

2. Manually kill off any rogue/residue processes still running from failed installation:

Windows:

Kill any opmn, agent processes owned by oracle via Task Manager and then perform reboot.

Unix:

ps -ef|grep opmn ps -ef|grep agent |grep oracle kill

3. Cleanup Repository as per

4. Execute runInstaller go to:

Installed Products > Select items "oms" and "agent" > Click Remove

NOTE: If the option chosen for initial installation was for "New Database" (default), verify that database is running ok and alert.log does not report any issues or errors. Keep this db10g installation and on next attempt of re-installation rather than choosing "New Database", choose the "Existing Database" option.
6) Remove subdirectories for oms10g and agent10g

rm -rf oms10g/ agent10g/

7) Remove Oracle installation subdirectories under /tmp

Unix only:

rm -rf /tmp/em rm -rf /tmp/OraInstall*

8) Then perform a re-installation:

runInstaller




++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Subject: How To Drop, Create And Recreate DB Control In A 10g Database
Doc ID: Note:278100.1 Type: BULLETIN
Last Revision Date: 05-JUN-2007 Status: PUBLISHED

In this Document
Purpose
Scope and Application
How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:
A. Delete DB Control Objects:
B. Create DB Control Objects
C. Recreate/ReConfig DB Control



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



Applies to:
Enterprise Manager Grid Control - Version: 10.1 to 10.2
Information in this document applies to any platform.
DBConsole
Purpose
This article provides detailed instructions on how to 1) create, 2) drop and 3)recreate the repository and configuration files for the DB Control application used to manage a single 10g Database.


Scope and Application
The steps in this article are written for a DBA or System Administrator who needs to create, drop or reconfigure the DB Control Application.

The format of the document will include steps for both 10.1 and 10.2 EMCA because the commands changed between the two releases.
How To Drop, Create And Recreate DB Control In A 10g Database
DB Control options:


A. Delete DB Control Objects:
Option 1. Delete DB Control Configuration Files using EMCA scripts
Option 2. Delete DB Control Configuration Files Manually:
Option 3. Delete DB Control Repository Objects using RepManager
Option 4. Delete DB Control Repository Objects Manually
Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA


Option 1. Delete DB Control Configuration Files using EMCA scripts:

For DB Control 10.1.x, run the command: /bin/emca -x For DB Control 10.2.x, run the command: bin/emca -deconfig dbcontrol db

Option 2. Delete DB Control Configuration Files Manually:

Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__


NOTE:
On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it


Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete '

- where is the DB Control service name (typically: OracleDBConsole)

Also available from Microsoft is the delsrv.exe command. (Free download from Microsoft)



Option 3. Delete DB Control Repository using RepManager:
This option is not as complete as the other options. You may find that dropping the repository using the commandline options is a better solution. Also note, RepManager is not used to create a DB Control Repository.

In both 10g R1 and R2 run: /sysman/admin/emdrep/bin/RepManager -action drop

Option 4. Delete DB Control Repository Objects Manually
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> SHUTDOWN
IMMEDIATE;SQL> STARTUP RESTRICT;SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;SQL> EXEC sysman.setEMUserContext('',5);SQL>
REVOKE dba FROM sysman;SQL> DECLARECURSOR c1 ISSELECT owner, synonym_name nameFROM dba_synonymsWHERE table_owner
= 'SYSMAN';BEGINFOR r1 IN c1 LOOPIF r1.owner = 'PUBLIC' THENEXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;ELSEEXECUTE
IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;END IF;END LOOP;END;/SQL> DROP USER mgmt_view CASCADE;SQL> DROP ROLE mgmt_user;SQL> DROP USER sysman CASCADE;SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Note: The above will completely delete the DB Control repository from the database; under certain circumstances (e.g. you want to recreate the repository later on) the following statements may be sufficient to remove the repository:


Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:SQL> drop user
sysman cascade;SQL> drop role MGMT_USER;SQL> drop user MGMT_VIEW cascade;SQL> drop public synonym MGMT_TARGET_BLACKOUTS;SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Option 5. Delete DB Control Configuration Files and Repository Objects using EMCA

For DB Control 10.1.x, dropping both the configuration files and the repository objects is a two step process. Run the following two commands:

/bin/emca -x /sysman/admin/emdrep/bin/RepManager -action drop

For DB Control 10.2.x, both configuration files and repository objects can be deleted with a single command. Run the command:

/bin/emca -deconfig dbcontrol db -repos drop


B. Create DB Control Objects
Option 1. Create the DB Control Configuration Files
Option 2. Create the DB Control Repository Objects and Configuration Files


Option 1. Create the DB Control Configuration Files
To create only the DB Control configuration files, skipping the repository creation (this would be done for instance if you dropped only the files and left the repository in place):

For DB Control 10.1.x, run the command: /bin/emca -rFor DB Control 10.2.x, run the command: /bin/emca -config dbcontrol db


Option 2. Create the DB Control Repository Objects and Configuration Files

For DB Control 10.1.x, run the command: /bin/emcaFor DB Control 10.2.x, run the command: bin/emca -config dbcontrol db -repos create


C. Recreate/ReConfig DB Control
Option 1. Recreate the DB Control Configuration Files only (leave Repository intact)
Option 2. Recreate the DB Control Configuration Files and Repository

In 10.2, the EMCA commands can be used to reconfigure the existing installs without removing them first.

Option 1. Recreate the DB Control Configuration Files only (leave Repository intact):
For DB Control 10.2.x, run the command:

/bin/emca -config dbcontrol db

Option 2. Recreate the DB Control Configuration Files and Repository
For DB Control 10.2.x, run the command:

/bin/emca -config dbcontrol db -repos recreate

For additional information on EMCA commandline options, please see the Oracle Enterprise Manager 10g Advanced Configuration Guide or see Note 330130.1 Overview Of The EMCA Commands Available for DB Control 10.2


Keywords
'EMCA' 'REPMANAGER' 'DB~CONTROL' 'DBCONSOLE'

How to use RMAN to create a physical standby database

How to use RMAN to create a physical standby database

文書番号 125354 最終更新日 2007-07-04 公開区分 契約顧客のみ
製品名(バージョン)[コンポーネント] Oracle Server - Enterprise Edition (9.2.0 - ) [Stanby・Database/Dataguard]
プラットフォーム(バージョン) すべてのプラットフォーム ( - )
関連文書 80299 　 66875 　

概要 [DataGuard] RMAN を使用したフィジカル・スタンバイの作成方法
内容:
[概要]
RMAN を使用した Data Guard 環境(フィジカル・スタンバイ)の構築方法について
説明します。


[対象リリース]
Oracle Database 10g Release2(10.2.0)
Oracle Database 10g Release1(10.1.0)
Oracle9i Database Release2(9.2.0)


[対象プラットフォーム]
すべてのプラットフォーム


[詳細]
手順の概要は以下のようになります。

1. プライマリ側のデータベースの作成
2. プライマリ・データベースをアーカイブログモードに変更
3. プライマリ・データベースのバックアップの取得
4. 取得したバックアップのスタンバイ側へのコピー
5. スタンバイでの設定
6. スタンバイでのバックアップのリストア
7. スタンバイの管理リカバリモードへの変更

詳細な手順は以下のようになります。

1. プライマリ側のデータベースの作成

スタンバイは意識せず、通常のシングルの環境と同様に DBCA を使用して作成
してください。このとき、リスナーの設定も行ってください。

2. プライマリ・データベースをアーカイブログモードに変更

以下の KROWN を参考にしてください。

KROWN:67982 自動アーカイブログモードへの変更方法
2.2 データベースをアーカイブログモードに変更(OPS,RAC環境の場

R10.1 以降では、log_archive_start のパラメータの設定は不要です。
アーカイブログの出力先ディレクトリは、log_archive_dest_1 に設定します。

3. プライマリ・データベースのバックアップの取得

RMAN(Recovery Manager) を使用してプライマリデータベースのバックアップ
を取得します。

% rman target / nocatalog
RMAN> backup database;
RMAN> backup current controlfile for standby;
RMAN> sql 'alter system archive log current';
RMAN> backup archivelog all;

4. 取得したバックアップのスタンバイ側へのコピー

手順 3 により $ORACLE_HOME/dbs 配下に取得したバックアップのファイルが
出力されています。バックアップのファイル名は、RMAN でのコマンド実行時
のログや、RMAN から以下のコマンドを実行することで確認が可能です。

RMAN> list backup;

上記のファイルをスタンバイ側の $ORACLE_HOME/dbs 配下にコピーします。

5. スタンバイでの設定

スタンバイ側で以下の設定を行います。

1) 初期化パラメータファイル
2) tnsnames.ora (プライマリとスタンバイの両方に接続できる設定をします)
3) listener.ora
4) パスワードファイル
5) サービス(Windows の場合のみ、oradim コマンドで作成します)

なお、以下の文章を参考にしてください。

KROWN:66875 初心者のためのDataGuard(Physical Standby)環境作成の手順
KROWN:80299 [10g Data Guard] Physical Standby Datbase 環境の構築方法

6. スタンバイでのバックアップのリストア

取得していたバックアップをスタンバイ側にリストアします。
スタンバイ側で以下のようなコマンドを実行します。

% sqlplus "/as sysdba"
SQL> startup nomount
SQL> exit

% rman target sys/@<プライマリに接続するサービス名> auxiliary /
RMAN> duplicate target database for standby dorecover;

なお、プライマリとスタンバイでディレクトリ構成が同じ場合(同じパス設定
でデータファイルを作成する場合)、RMAN でのコマンドは NOFILENAMECHECK
オプションを指定した以下のようなものを使用してください。

RMAN> duplicate target database for standby nofilenamecheck dorecover;

7. スタンバイの管理リカバリモードへの変更

以下のコマンドにより、スタンバイを管理リカバリモードに変更します。

SQL> recover automatic standby database;


[参照情報]
マニュアル
『 Oracle Data Guard 概要および管理 10g リリース2（10.2）』
F Recovery Manager を使用したスタンバイ・データベースの作成

『 Oracle Data Guard 概要および管理 10g リリース1（10.1）』
D Recovery Manager を使用したフィジカル・スタンバイ・データベースの作成

『 Oracle9i Recovery Manager ユーザーズ・ガイド リリース2（9.2）』
13章 Recovery Manager によるスタンバイ・データベースの作成

KROWN:66875 初心者のためのDataGuard(Physical Standby)環境作成の手順
KROWN:80299 [10g Data Guard] Physical Standby Datbase 環境の構築方法

[更新履歴]
2007/07/04 本文書を公開

MAA - Creating a Single Instance Physical Standby for a RAC Primary

Subject: MAA - Creating a Single Instance Physical Standby for a RAC Primary
Doc ID: Note:387339.1 Type: WHITE PAPER
Last Revision Date: 14-AUG-2006 Status: PUBLISHED

MAA / Data Guard 10g Setup Guide –

Creating a Single Instance
Physical Standby for a RAC Primary


Oracle Maximum Availability Architecture White Paper

April 2006



Overview
Oracle Maximum Availability Architecture (MAA) [1] is Oracle's best practices blueprint based on proven Oracle high-availability technologies and recommendations. The goal of MAA is to remove the complexity in designing the optimal high-availability architecture.

Published as part of the MAA series of white papers, this paper focuses on creating a single instance physical standby database for a RAC primary database. This document assumes that there is an existing RAC database and you want to implement Data Guard by adding a physical standby database to the configuration. The end configuration for this document is a RAC primary database with a single-instance physical standby database. The steps outlined in this document use SQL*Plus, apply to both Oracle Database 10g Release 1 and Oracle Database 10g Release 2, and they assume using ASM/OMF, and that the software and ASM instance on the standby host have already been installed/created.

The example used in this document has the database unique name of the RAC database as CHICAGO. The instance names of the two RAC instances are CHICAGO1 (on node chicago_host1) and CHICAGO2 (on node chicago_host2). The database unique name of the single-instance physical standby database is BOSTON (on node boston_host1).

This document includes the following tasks:

Task 1: Gather Files and Perform Back Up
Task 2: Configure Oracle Net on the Physical Standby
Task 3: Create the Physical Standby Instance and Database
Task 4: Configure the Primary Database for Data Guard
Task 5: Verify Data Guard Configuration
This document assumes that the following conditions are met:

The primary RAC database is using ASM.
The standby host has an existing Oracle software installation.
The physical standby database storage uses an existing ASM instance.
The primary and standby databases are using a flash recovery area.
Oracle Managed Files (OMF) is used for all storage.


Task 1: Gather Files and Perform Back Up

On the primary node, create a staging directory. For example:
[oracle@chicago_host1 oracle]$ mkdir -p /opt/oracle/stage


Create the same exact path on the standby host:
[oracle@boston_host1 oracle]$ mkdir -p /opt/oracle/stage


On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:
SQL> CREATE PFILE='/opt/oracle/stage/initCHICAGO.ora' FROM SPFILE;


On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:
[oracle@chicago_host1 stage]$ rman target /
RMAN> backup device type disk format '/opt/oracle/stage/%U' database plus archivelog;
RMAN> backup device type disk format '/opt/oracle/stage/%U' current controlfile for standby;


Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:
[oracle@chicago_host1 oracle]$ cp $ORACLE_HOME/network/admin/*.ora /opt/oracle/stage


Copy the contents of the staging directory on the RAC node to the staging directory on the standby host. For example:
[oracle@chicago_host1 oracle]$ scp /opt/oracle/stage/* oracle@boston_host1:/opt/oracle/stage

Task 2: Configure Oracle Net SERVICES on the Standby

Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on the standby host.
Modify the listener.ora file on the standby host to contain the hostname of the standby host.
Modify the tnsnames.ora file on each node, including the primary RAC nodes and standby host, to contain all primary and standby net service names. In this example, each tnsnames.ora file should contain all three of the net service names in the following table:
Example Entries in the tnsnames.ora Files

Primary Net Service Names Standby Net Service Name
CHICAGO1_SERV = (DESCRIPTION = (ADDRESS =
(PROTOCOL = TCP)
(HOST =chicago_host1vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER =DEDICATED)
(SERVICE_NAME =CHICAGO)
(INSTANCE_NAME =CHICAGO1)
)
)
BOSTON =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST =boston_host1)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BOSTON)
)
)

CHICAGO2_SERV =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = chicago_host2vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CHICAGO)
(INSTANCE_NAME = CHICAGO2)
)
)



Task 3: Create the Physical Standby Instance and Database

To enable secure transmission of redo data, make sure the primary and physical standby databases use a password file, and make sure the password for the SYS user is identical on every system. For example:
$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwBOSTON password=oracle


The naming and location of the password file varies on different platforms. See “Creating and Maintaining a Password File” in the Oracle Database Administrator’s Guide for more information.

Copy and rename the primary database PFILE from the staging area on the standby host to the $ORACLE_HOME/dbs directory on the standby host. For example:
[oracle@boston_host1 stage]$ cp initCHICAGO.ora $ORACLE_HOME/dbs/initBOSTON.ora


Modify the standby initialization parameter file copied from the primary node to remove RAC parameters and to include Data Guard parameters as illustrated in the following table:
Initialization Parameter Modifications

arameter
Category
Before After

RAC Parameters *.cluster_database=true
*.db_unique_name=CHICAGO
CHICAGO1.instance_name=CHICAGO1
CHICAGO2.instance_name=CHICAGO2
CHICAGO1.instance_number=1
CHICAGO2.instance_number=2
CHICAGO1.thread=1
CHICAGO2.thread=2
CHICAGO1.undo_tablespace=UNDOTBS1
CHICAGO2.undo_tablespace=UNDOTBS2
*.remote_listener=LISTENERS_CHICAGO
CHICAGO1.LOCAL_LISTENER=LISTENER_CHICAGO_HOST1
CHICAGO2.LOCAL_LISTENER=LISTENER_CHICAGO_HOST2
*.cluster_database=false
*.db_unique_name=BOSTON
*.instance_name=BOSTON
*.thread=1
*.undo_tablespace=UNDOTBS1


Data Guard Parameters
*.log_archive_config='dg_config=
(BOSTON,CHICAGO)'
*.log_archive_dest_2='service=CHICAGO1_SERV
valid_for=(online_logfiles,primary_role)
db_unique_name=CHICAGO'
*.db_file_name_convert='+DATA/CHICAGO/',
'+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.log_file_name_convert='+DATA/CHICAGO/',
'+DATA/BOSTON/','+RECOVERY/CHICAGO',
'+RECOVERY/BOSTON'
*.standby_file_management=auto
*.fal_server='CHICAGO1_SERV','CHICAGO2_SERV'
*.fal_client='BOSTON'
*.service_names='BOSTON'


Other parameters *.background_dump_dest=
/opt/oracle/admin/CHICAGO/bdump
*.core_dump_dest=
/opt/oracle/admin/CHICAGO/cdump
*.user_dump_dest=
/opt/oracle/admin/CHICAGO/udump
*.audit_file_dest=
/opt/oracle/admin/CHICAGO/adump
*.db_recovery_dest=’+RECOVERY’
*.log_archive_dest_1 =
'LOCATION=+DATA/CHICAGO/'
*.dispatchers=CHICAGOXDB

*.background_dump_dest=
/opt/oracle/admin/BOSTON/bdump
*.core_dump_dest=
/opt/oracle/admin/BOSTON/cdump
*.user_dump_dest=
/opt/oracle/admin/BOSTON/udump
*.audit_file_dest=
/opt/oracle/admin/BOSTON/adump
*.db_recovery_dest=’+RECOVERY’
*.log_archive_dest_1=
'LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.dispatchers=BOSTONXDB





For more information about these initialization parameters, see Chapter 13, “Initialization Parameters” in Oracle Data Guard Concepts and Administration manual.

If you are using an SPFILE instead of an initialization parameter file, then see the “Managing Initialization Parameters Using a Server Parameter File” section in the Oracle Database Administrator’s Guide for instructions on managing an SPFILE.

Connect to the ASM instance on the standby host, and create a directory within the DATA disk group that has the same name as the DB_UNIQUE_NAME of the physical standby database. For example:
SQL> alter diskgroup data add directory '+DATA/BOSTON';


Connect to the physical standby database, with the standby in the IDLE state, and create an SPFILE in the standby DATA disk group:
SQL> CREATE SPFILE='+DATA/BOSTON/spfileBOSTON.ora' FROM PFILE='?/dbs/initBOSTON.ora';


In the $ORACLE_HOME/dbs directory on the standby host, create a PFILE that is named initoracle_sid.ora that contains a pointer to the SPFILE. For example:
[oracle@boston_host1 oracle]$ cd $ORACLE_HOME/dbs
[oracle@boston_host1 dbs]$ echo "SPFILE='+DATA/BOSTON/spfileBOSTON.ora'" > initBOSTON.ora


Create the dump directories on the standby host as referenced in the standby initialization parameter file. For example:
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/bdump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/cdump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/udump
[oracle@boston_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/BOSTON/adump


After setting up the appropriate environment variables on the standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the physical standby database instance without mounting the control file.
SQL> STARTUP NOMOUNT


From the standby host, duplicate the primary database as a standby into the ASM disk group. For example:
$ rman target sys/oracle@CHICAGO1_SERV auxiliary /
RMAN> duplicate target database for standby;


Connect to the physical standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads


This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;


You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;


You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;


See the “Configure a Standby Redo Log” section in Oracle Data Guard Concepts and Administration manual for more information.

Start managed recovery and real-time apply on the standby database:
SQL> ALTER DATABASE recover managed standby database using current logfile disconnect;

Task 4: Configure The Primary Database For Data Guard
Configure the primary database initialization parameters to support both the primary and standby roles.
*.log_archive_config='dg_config=(BOSTON,CHICAGO)'
*.log_archive_dest_2='service=BOSTON
valid_for=(online_logfiles,primary_role)
db_unique_name=BOSTON'
*.db_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
*.log_file_name_convert='+DATA/BOSTON/',’+DATA/CHICAGO/', ’+RECOVERY/BOSTON’,’+RECOVERY/CHICAGO’
*.standby_file_management=auto
*.fal_server='BOSTON'
CHICAGO1.fal_client='CHICAGO1_SERV'
CHICAGO2.fal_client='CHICAGO2_SERV'
*.service_names=CHICAGO

For more information about these initialization parameters, see Chapter 13, “Initialization Parameters” in the Oracle Data Guard Concepts and Administration manual.

If you are using an SPFILE instead of an initialization parameter file, then see the “Managing Initialization Parameters Using a Server Parameter File” section in the Oracle Database Administrator’s Guide for instructions on managing an SPFILE.

Note that all the parameters listed above can be dynamically modified with the exception of the standby role parameters log_file_name_convert and db_file_name_convert. It is recommended to set the parameters with “scope=spfile” so that they can be put into effect upon the next role change.

Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 5 SIZE 10M,
GROUP 6 SIZE 10M,
GROUP 7 SIZE 10M;


SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 8 SIZE 10M,
GROUP 9 SIZE 10M,
GROUP 10 SIZE 10M;


These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;


You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;


You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;


See the “Configure a Standby Redo Log” section in Oracle Data Guard Concepts and Administration manual for more information.


Task 5: Verify Data Guard Configuration

On the physical standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;


On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:
SQL> alter system archive log current;


On the physical standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:
SQL> select sequence#, first_time, next_time
from v$archived_log order by sequence#;



References

Oracle Maximum Availability Architecture website on OTN
http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm

wrap utility

Wrap utility can encrypt sql files.
People usually use ".plb" to identify encrypted files.
The objects created by ".plb" files are also encrypted, for example, in the TEXT column of dba_resource.

The ".plb" file may lead to ORA-4030 in some oracle versions if it is corrupted.

See below for details.

Subject: ORA-04030 (PLS non-lib hp,PAR.C:parchk) While Running a Wrapped Script
Doc ID: Note:280168.1 Type: PROBLEM
Last Revision Date: 18-MAY-2007 Status: MODERATED


The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 9.2.0.5
This problem can occur on any platform.

Errors
ORA-04030

Symptoms
A wrapped plsql script is being run and we get the following error message:

CREATE OR REPLACE PROCEDURE p5 wrapped
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 659072 bytes (PLS non-lib hp,PAR.C:parchk:p

The plsql is wrapped and is not the ASCII source code.
Cause
Corrupted wrapped code. To see this we can use a small testcase.

1] We create a plsql script which has the following code in it:

create or replace procedure p1
as
x number;
begin
select count(*)
into x
from user_source;
end;
/

2] By running e.sql we can create the procedure fine.

SQL> @c:\e.sql
Procedure created.

3] We will wrap the plsql script.

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

D:\Ora92\BIN>wrap iname=c:\e.sql oname=c:\a.plb

PL/SQL Wrapper: Release 9.2.0.4.0- Production on Tue Jul 27 20:50:59 2004
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing c:\e.sql to c:\a.plb

D:\Ora92\BIN>exit

SQL> @c:\a.plb
Procedure created.

So the wrapped code is fine now and we are able to create the procedure

4] We will corrupt the wrapped code a.plb (opened the a.plb file with notepad/vi editor and added or deleted some characters)

SQL> set lines 5000
SQL> @c:\a.plb
CREATE OR REPLACE PROCEDURE p5 wrapped
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 659072 bytes (PLS non-lib hp,PAR.C:parchk:p

This has been reproduced on:
o 9.2.0.4 on Windows2000
o 8.1.7.4 on Linux
o 9.2.0.5 on Linux

Fix

The error message (memory related) is somewhat misleading. If we find the ORA-04030 with such an argument (PLS non-lib hp,PAR.C:parchk:) then we should check if the wrapped code is ok or not. We can either rewrap it or can possibly run the unwrapped code.

Note: On 10g DB the error message is proper and instead of giving a ORA-04030 it gives a relevent PLS- error :

SQL> @a.plb
Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -------------------------------------------
0/0 PLS-00801: internal error [pkg_read:ver]

This has been tested on 10.1.0.2 on Windows and Linux.

Manual Installation/Registration of Java Plug-in—Linux (for mozilla)

Manual Installation/Registration of Java Plug-in—Linux(for mozilla)

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


To install the Java Plug-in follow these steps.

Note:

Only Mozilla can be registered with Java Plug-in.
Only one Java Plug-in can be registered at a time. When you want to use a different version, unregister any other version and register the new one.
Example:

Suppose for JDK 5.0 you created a symbolic link in the Mozilla plugins directory to:

~/jdk1.5.0/jre/plugin/i386/ns7/libjavaplugin_oji.so

Then cd to the Mozilla plugins directory and remove the link as shown below:

rm libjavaplugin_oji.so

Also check that there are no links in the Mozilla components directory.


1. Uninstall any previous installations of Java Plug-in.

rm -fr $HOME/.netscape/java
rm $HOME/.netscape/plugins/javaplugin.so
rm $HOME/.netscape/plugins/libjavaplugin.so

One or more of these files may exist on your machine, depending on which previous versions of Java Plug-in you have installed.

2. For the Mozilla browser create a symbolic link to the Plug-in in the Mozilla plugins directory.

Create a symbolic link to the Java Plug-in libjavaplugin.so file in the Mozilla plugins directory.

cd /plugins (usually /usr/lib/mozilla/plugins)
ln -s /plugin/i386//libjavaplugin_oji.so . (if you haven't installed jre, you can use jre installed with oracle instead, which is usually in $ORACLE_HOME/jre/1.4.2/plugin/i386/ns610-gcc32/)

In the ln command line above, use ns7-gcc29 if Mozilla was compiled with gcc2.9.

Be sure to include the period at the end.

3. Start the Mozilla browser, or restart it if it is already up.

Note that if you have other Mozilla "objects" open before restarting (Messenger, Composer ...) you will need to shut them down as well.
In Mozilla do Help -> About Plug-ins to confirm that the Java Plug-in is loaded.



In addition, redhat enterprise linux 4 uses Konqueror as its default browser.
You can use "vnc://hostname:5901" to access vnc server directly.

The meaning of checkpoint scn in backupsets

backupset中checkpoint scn的意义


until scn XXXXXX:
Specifies an SCN as an upper limit. RMAN selects only files that can be used to restore or recover up to but not including the specified SCN. For example, RESTORE DATABASE UNTIL SCN 1000 chooses only backups that could be used to recover to SCN 1000.（出处：手册）

上面就是说，rman使用list backupset命令显示的checkpoint scn就是该backupset能recover到的最小scn


如果使用的是user-managed hot backup的话，就必须recover到end backup marker以后：
If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent"

（下面附了2篇note，第一篇辅助说明第一个问题，第二篇是上面那段话的出处。）

Subject: RMAN: Set Until Time does not Restore the Required Backup
Doc ID: Note:124788.1 Type: PROBLEM
Last Revision Date: 09-MAY-2005 Status: PUBLISHED


Problem Description
-------------------

You need to restore to a specific backup that was taken using RMAN and you
specify the set until time option but the backup used for the restore is not
the backup you wanted.

Each night the database is shutdown and then a startup mount is done. A backup
job is then run after this. It was discovered that a table had been dropped
approximately five minutes before the database was shutdown. You look at the
rc_backup_piece view and find the start_time of the nightly backup. You
subtract five minutes from the start_time and this is the time that you want
the point-in-time restore and recovery to finish at. You run a restore and
recovery job from RMAN and expect the previous night's backup to be selected.
This is not the case; the latest backup is selected. This is illustrated
below:


The following lists the two backups taken by rman.


List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
3993 34 413309153 0 413309090 43 10-NOV-00 16:05

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
3995 1 1 AVAILABLE 10-NOV-00 16:05 1bca5652_1_1

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36384 10-NOV-00 16:01
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36384 10-NOV-00 16:01
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36384 10-NOV-00 16:01
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36384 10-NOV-00 16:01




List of Backup Sets
Key Recid Stamp LV Set Stamp Set Count Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
4004 35 413309939 0 413309866 44 10-NOV-00 16:18

List of Backup Pieces
Key Pc# Cp# Status Completion Time Piece Name
------- --- --- ----------- ---------------------- ------------------------
4006 1 1 AVAILABLE 10-NOV-00 16:18 1cca56ta_1_1

List of Datafiles Included
File Name LV Type Ckp SCN Ckp Time
---- ------------------------------------- -- ---- ---------- -------------
1 /u01/app/oracle/oradata/test/systest.dbf 0 Full 36398 10-NOV-00 16:08
2 /u01/app/oracle/oradata/test/rbstest.dbf 0 Full 36398 10-NOV-00 16:08
3 /u01/app/oracle/oradata/test/temptest.dbf 0 Full 36398 10-NOV-00 16:08
4 /u01/app/oracle/oradata/test/tempusers.dbf 0 Full 36398 10-NOV-00 16:08


The table was dropped five minutes before the second backup was started,
so the following sql query was run against the rman user:

select bp_key,start_time,completion_time from rc_backup_piece;


BP_KEY START_TIME COMPLETION_TIME
---------- ------------------ ----------------------------
4006 10-nov-00 16:17 10-nov-00 16:18


The backup started at 16:17. The following RMAN job was set up and run:


RMAN> run {
2> set until time '10-nov-00 16:10';
3> allocate channel t1 type 'sbt_tape';
4> restore database;
5> recover database;
6> release channel t1;}


RMAN-03022: compiling command: set

RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: t1
RMAN-08500: channel t1: sid=11 devtype=SBT_TAPE
RMAN-08526: channel t1: MMS Version 2.2.0.1

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE
RMAN-03023: executing command: IRESTORE
RMAN-08016: channel t1: starting datafile backupset restore
RMAN-08502: set_count=44 set_stamp=413309866 creation_time=10-NOV-00 16:17
RMAN-08089: channel t1: specifying datafile(s) to restore from backup set
RMAN-08523: restoring datafile 00001 to /u01/app/oracle/oradata/test/systest.dbf
RMAN-08523: restoring datafile 00002 to /u01/app/oracle/oradata/test/rbstest.dbf
RMAN-08523: restoring datafile 00003 to /u01/app/oracle/oradata/test/temptest.dbf
RMAN-08523: restoring datafile 00004 to /u01/app/oracle/oradata/test/testusers.dbf
RMAN-08023: channel t1: restored backup piece 1
RMAN-08511: piece handle=1cca56ta_1_1 tag=NIGHTLY BACKUP params=NULL
RMAN-08024: channel t1: restore complete
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)
RMAN-03023: executing command: recover(3)
RMAN-08054: starting media recovery
RMAN-08055: media recovery complete

RMAN-03022: compiling command: recover(4)

RMAN-03022: compiling command: release
RMAN-03023: executing command: release
RMAN-08031: released channel: t1


The second backup is used, not the first backup as expected.


Solution Description
--------------------

The SET UNTIL TIME is the time the database be recovered to.
The SET UNTIL TIME should have been set prior to the database
being shutdown. Or more generic the SET UNTIL TIME should have
been set prior to the CHECKPOINT TIME of the datafiles (10-NOV-00 16:08)

The CHECKPOINT TIME of the datafiles will be different for an ONLINE backup,
and therefor the SET UNTIL TIME should be BEFORE the 'oldest' backup CHECKPOINT TIME
of the related datafiles in that backup.

References
----------

Oracle8i Recovery Manager User's Guide and Reference


Additional Search Words
-----------------------




Subject: Recovering a Dropped Table from a Full Database Backup
Doc ID: Note:96197.1 Type: TROUBLESHOOTING
Last Revision Date: 20-MAY-2007 Status: PUBLISHED


PURPOSE
-------

This bulletin outlines the steps to perform recovery to restore
a dropped table without recovering the entire database. The bulletin
assumes the reader is familiar with Oracle's recovery procedures which
are documented in the Oracle documentation set.

SCOPE & APPLICATION
----------------------

This bulletin discusses dropped table recovery using traditional (Oracle7)
backup and recovery procedures. These can equally be applied to Oracle8. It
does NOT discuss tablespace point in time recovery (TSPITR) or the Recovery
Manager (RMAN) duplicate database feature.

The examples in this bulletin are UNIX-based, but can be easily applied to
other platforms with little modification.


RECOVERING A DROPPED TABLE FROM A FULL DATABASE BACKUP
------------------------------------------------------

TERMINOLOGY
-----------

PROD machine - Host computer on which the production database runs. Also
denotes the ORACLE_SID of the production instance.
TEST machine - Host computer, physically distinct from the machine on which the
production database runs.
TEMP - The ORACLE_SID of the instance used to access the restored
database.
The backup - The set of backed up database structures (see REQUIREMENTS) used
as the basis for recovering the dropped table. Once restored,
this set of structures is referred to as the partially restored
database.

REQUIREMENTS
------------

The following data structures must be available from the backup:
- All system tablespace datafiles
- All rollback segment datafiles
- All datafiles in which the table to recovered is stored
- Archive logs - see Note (a)
Notes:
(a) If the restored datafiles are part of a hot backup, or you wish to
roll them forward, the required archivelogs must be available.

In an ideal world the partially restored database will be made available for
recovery on a TEST machine. However this may not always be possible, and
it may be necessary to restore to the same machine on which the 'source'
(PROD) database resides. The latter is NOT RECOMMENDED as a user error in the
recovery process can corrupt the production database.

If the datafiles comprising the partially restored database were in hot
backup mode at the time the table was dropped, it is necessary to restore
a previous backup. The reason for this is that the hot backup must be rolled
forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the
following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent"

I. RECOVERY ON A TEST MACHINE:
------------------------------

The following steps are to be used when performing recovery on a TEST machine,
the assumption being that there is no instance with the ORACLE_SID "PROD"
or "TEMP" already running on the TEST machine.

1. On the PROD database, connect as a privileged user and create an ASCII
dump of the controlfile. For example:

SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;

A trace file will be generated in the user_dump_dest destination. Rename
this trace file to ccf.sql and save it.

2. Create a directory on the TEST machine. The restored database will be
located in this directory. In this example the directory is called
/RESTORE. The only stipulation is that there is enough space to easily
accomodate the restored database and any archivelogs required for recovery.
If filesystem space is at a premium, the archivelogs may be optionally
restored to /RESTORE as required by recovery, and removed after they have
been applied.

3. Restore the backup to the /RESTORE directory.

4. Optionally, restore all archivelogs required for recovery to the /RESTORE
directory.

5. Copy the init.ora file (initPROD.ora) from the PROD database to the /RESTORE
directory on the TEST machine. This assumes that all database parameters are
specified in only the init.ora file. If an include files (ifile) is
referenced, also copy this file to /RESTORE.

6. Rename the initPROD.ora to initTEMP.ora on the TEST machine.

7. Edit the initTEMP.ora and make the following modifications:
a. control_files = /RESTORE/cntrlTEMP.dbf
b. if applicable, change any ifile references to point to the copied
include file
c. log_archive% parameters should be changed to reflect the restored
archivelogs:
log_archive_destination
log_archive_format
For example:
log_archive_destination=/RESTORE
log_archive_format=arch_%s.dbf
d. %dump_dest parameters should be changed to point to suitable directories

e. If audit is on, turn it off.

8. Ensure that the shell environment is set correctly. At the very least, the
following environment variables must be defined:
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

9. Connect as a privileged user. For example:

SVRMGR> connect / as sysdba

10. Start the instance, specifying TEMP's init.ora:

SVRMGR> startup nomount pfile=/RESTORE/initTEMP.ora

11. The ccf.sql file (created in Step 1) contains the syntax necessary to
create a new controlfile. Copy this script to the TEST machine and edit
it to save only the text between (and including) 'CREATE CONTROLFILE'
and it's terminating semi-colon. Modify the following sections:

a. LOGFILE. Edit this section to reflect the names of the online logs to
be created for the TEMP instance. If the PROD instance has a large
number of large online log members, it is advisable to specify a
reduced number of smaller log members. You MUST however specify at
least two online log groups.
b. DATAFILE. Edit this section to reflect the names of the restored
datafiles only.
c. Miscellaneous:
- Remove the REUSE keyword
- Optionally change the ARCHIVELOG keyword to NOARCHIVELOG (so the
TEMP database will not operate in archivelog mode)

An example of the completed ccf.sql script might be:

CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 20
MAXINSTANCES 1
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/RESTORE/log1PROD.dbf' SIZE 1M,
GROUP 2 '/RESTORE/log2PROD.dbf' SIZE 1M
DATAFILE
'/RESTORE/sys1PROD.dbf',
'/RESTORE/rbs1RBS.dbf',
'/RESTORE/users1PROD.dbf'
;

Then execute ccf.sql script to create control file(s).

12. Recover the database. The database is to be recovered to a time before
the table was dropped. There are two options here:
a. Time-based incomplete recovery. Database recovery is stopped at the
specified date and time.
b. Cancel-based incomplete recovery. Database recovery is stopped on
an archivelog boundary i.e. the granularity of cance-based recovery
is the archivelog.

Example of time-based recovery:

SVRMGR> recover database until time '2000-01-10:12:00:00' using backup controlfile

In the above example, apply archivelogs as requested. Recovery will stop
automatically at 12:00 on Januaru 10th, 2000.

Example of cancel-based recovery:

SVRMGR> recover database until cancel using backup controlfile

As soon as you have applied the last desired archivelog, type CANCEL
to stop recovery.

13. Open the database with the RESETLOGS option:

SVRMGR> alter database open resetlogs;

14. Export the table(s).

15. Import the table(s) into the PROD database.

16. Once the necessary tables have been imported, the TEMP instance can be
shutdown and all associated files removed. It is worthwhile verifying that
the import has completed successfully before removing the TEMP instance.

II. RECOVERY ON A PRODUCTION MACHINE:
-------------------------------------

If a TEST machine is not available for performing recovery, the PROD machine
can be used. It is important to exercise extreme caution when doing this. The
restored database will be operate under the TEST instance as before. During
this procedure the restored database's name is changed in order to avoid
problems in acquisition of the mount lock.

1. Take a FULL backup of the database running against the PROD instance
before performing any recovery.

2. While connected to the PROD instance, create an ASCII dump of the
controlfile:

SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;

3. Create a /RESTORE directory as before (Step 2 above).

4. Restore the backup (and optionally the archivelogs) to the /RESTORE
directory (Steps 3 and 4 above).

5. Create the initTEMP.ora file (Steps 5, 6, 7 above). In addition to the
changes already made, modify the db_name parameter, for example:
db_name=TEMP

6. Ensure that the shell environment is set correctly (Step 8 above):
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)

7. Start the TEMP instance (Steps 9, 10 above). It is critical to ensure
that the correct pfile is used to start the instance.

8. Modify the ccf.sql file (Step 11 above). It is critical to ensure that
the DATAFILE and LOGFILE names reference the backup location and NOT the
PROD instance database's files. In addition to the changes already made,
modify the first line to set the new database name, for example:
from: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
to: CREATE CONTROLFILE SET DATABASE "TEMP" RESETLOGS NOARCHIVELOG

9. Recover the database (Step 12 above).

10. Open the database (Step 13 above).

11. Export the table(s).

12. Import the tables(s).

13. Cleanup the TEMP instance (Step 16 above).

RELATED DOCUMENTS
-----------------
Oracle7 Server Administrator's Guide
Oracle8 Backup and Recovery Guide

Why do tablespaces not require hot backup mode or extra logging

Q: Why do tablespaces not require hot backup mode or extra logging
when using RMan for backups?

A: To understand why RMAN does not require extra logging or backup mode,
you must first understand why those features are required for non-RMAN
online backups.

A non-RMAN online backup consists of a non-Oracle tool, such as cp or
dd, backing up a datafile at the same time that DBWR is updating the
file. We can't prevent the tool from reading a particular block at the
exact same time that DBWR is updating that block. When that happens,
the non-Oracle tool might read a block in a half-updated state, so that
the block which is copied to the backup media might only have been
updated in its first half, while the second half contains older data.
This is called a "fractured block". If this backup needs to be restored
later, and that block needs to be recovered, recovery will fail because
that block is not usable.

The 'alter tablespace begin backup' command is our solution for the
fractured block problem. When a tablespace is in backup mode, and a
change is made to a data block, instead of logging just the changed
bytes to the redo log, we also log a copy of the entire block image
before the change, so that we can reconstruct this block if media
recovery finds that this block was fractured. That block image logging
is what causes extra redo to be generated while files are in backup
mode.

The reason that RMAN does not require extra logging is that it
guarantees that it will never back up a fractured block. We can make
that guarantee because we know the format of Oracle data blocks, and we
verify that each block that we read is complete before we copy it to the
backup. If we read a fractured block, we read the block again to obtain
a complete block before backing it up. non-Oracle tools are not able to
do the same thing because they do not know how to verify the contents of
an Oracle data block.

Backup mode has another effect, which is to 'freeze' the checkpoint in
the header of the file until the file is removed from backup mode.
We do this because we cannot guarantee that the third-party backup
tool will copy the file header prior to copying the data blocks.
RMAN does not need to freeze the file header checkpoint because we
know the order in which we will read the blocks, which enables us to
capture a known good checkpoint for the file.

The correct NLS_LANG setting in Unix Environments

Subject: The correct NLS_LANG setting in Unix Environments
Doc ID: Note:264157.1 Type: HOWTO
Last Revision Date: 02-AUG-2006 Status: PUBLISHED

In this Document
Goal
Solution
References



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



Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
Information in this document applies to any platform.
GoalHow to set the NLS_LANG correctly in Unix environments:

-1- Check which locale is set and correct this if needed.
-2- Check if the choosen locale is indeed installed and install this if needed.
-3- Check if you telnet/ssh software is properly set up.
-4- Set the NLS_LANG and Test.
-5- What to do if this is not working?
-6- More in depth debugging.
Solution

-1- Check which locale is set and correct this if needed.
---------------------------------------------------------

Please note that the ouput of this command is not exactly the same on all the Unix environments.
Contact your Unix vendor if you have questions.

The example used here is to configure your unix environment so that you can use Unicode (UTF8) with sqlplus on your unix box.

To see your current setup, use the "locale" command like this:

$ locale

example of output:

LANG=fr_FR
LC_CTYPE="fr_FR.iso885915@euro"
LC_COLLATE="fr_FR.iso885915@euro"
LC_MONETARY="fr_FR.iso885915@euro"
LC_NUMERIC="fr_FR.iso885915@euro"
LC_TIME="fr_FR.iso885915@euro"
LC_MESSAGES="fr_FR.iso885915@euro"
LC_ALL=fr_FR.iso885915@euro


Most Unix versions have this as default:

$ locale

LANG=
LC_CTYPE="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_MESSAGES="C"
LC_ALL=

"C" means US7ASCII , this implies that you can only display a-z, A-Z and 0-9 and nothing else.

We recommend to use UTF-8 when possible, this should look like:

$ locale

LANG=en_US
LC_CTYPE="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_ALL=en_US.UTF-8

When you have choosen a value, for example "en_US.UTF-8" on Linux, you can set it like this:

$ export LC_ALL=en_US.UTF-8

or

% setenv LC_ALL en_US.UTF-8

However you should set the needed variables in the user ".profile" file and then log out an in agian and check if the "locale" command returns the wanted output.
If you have questions on how to configure a user environment on your specific Unix/Linux flavor please consult your OS vendor.

-2- Check if the choosen locale is indeed installed and install this if needed.
-------------------------------------------------------------------------------

Please *do* check that you have the defined locale really installed.

To see all installed locales issue:

$ locale -a

sample output:

$ locale -a

POSIX
common
en_US.UTF-8
C
iso_8859_1
iso_8859_15
en_CA
en_CA.ISO8859-1
en_US
en_US.ISO8859-1
en_US.ISO8859-15
en_US.ISO8859-15@euro
fr_CA
fr_CA.ISO8859-1
th
th_TH
th_TH.TIS620
ja


This lists all installed locales for the unix box, for example "fr_FR.iso885915@euro" is missing in the list, so if you want to use it then you need to install this first.

If you set the user environment to a locale that is not installed you will not get an error but it will not work.
Please also note that you need to have installed the exact locale , if you have "fr_FR.UTF8" or "UTF-8" installed but want to use "en_US.UTF8" you need to install "en_US.UTF8".

In the example you see that "en_US.UTF-8" is listed , so we can use it on this server.

-3- Check if you telnet/ssh software is properly set up.
--------------------------------------------------------

You will need *also* to check that your TELNET/SSH software is correctly configured.
It's your telnet software is responible for the conversion of the Unix locale to the client's environment (most likly a windows system).

We suggest that you try first with the free PUTTY client.

Putty download site - http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

When using the "en_US.UTF-8" like in our example on the unix side change the following setting in Putty:

Open the configuration window, navigate to "Window" then to "Translation" and set "Received data assumed to be in which character set" to "UTF-8".

Then navigate to "Window" then to "Appearance" and choose a font in the "Font used in the terminal window" that supports the languages you want to use/see.
For non-asian languages you can use the default "Courier New".

On windows you can use the windows tool "character map" to see what characters are know in a font.

Putty UTF8 how to - http://www.laug.org/2004/11/putty-utf8-howto.html

If it works with Putty but not with your telnet/ssh package then please consult the vendor of your telnet/ssh software.

If you do not use telnet but a "real" unix display please see Note 265090.1 - How to check Unix terminal Environments for the capability to display extended characters.

-4- Set the NLS_LANG and Test.
------------------------------

Once you have 1) configured the locale correctly , 2) have checked that the used locale is really installed and 3) configured your telnet/ssh client, then you can use a NLS_LANG set to LC_TYPE values.
In our "en_US.UTF-8" example this means setting NLS_LANG=AMERICAN_AMERICA.UTF8 (note the difference in notation between UTF-8 (unix) and UTF8 (oracle) )

so, login with your Unix user and then

a) check with locale if it's correctly set (assuming here en_US.UTF8)

b) set the NLS_LANG to match the locale settings

$ export NLS_LANG=AMERICAN_AMERICA.UTF8

or

% setenv NLS_LANG AMERICAN_AMERICA.UTF8

c) connect with sqlplus to your database and select some data.

For example "select UNISTR('\20AC') from dual;" will give the euro symbol when selecting using a 9i or up database that can handle the euro (UTF8,WE8MSWIN1252,...) and a correct UTF-8 or ISO8859-15 Unix enviroment.

If this works, then set the NLS_LANG also in the profile of your user.

-5- What to do if this is not working?
--------------------------------------

If you do not see the expected characters then please double check your settings.

However it's also possible that you have wrong data in your database.

The easy way to check:

Use a windows client, download and install SQL developer http://www.oracle.com/technology/products/database/project_raptor/, connect to your database and see if your data is correctly displayed in that tool.
If it is visible in SQL developer then the data is correct in the database, if not then you have wrong data in your database.

The harder way:

If for example "select ename from scott.emp where empno='7369';" is a select that returns one row of your data then do "select dump(ename,1016),ename from scott.emp where empno='7369';".
You can then look up if the codes match the characters you expect for your database characterset ( select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'; ) in NOTE.282336 Charts of most current mono-byte Character sets or in case you have a (AL32)UTF8 database use Note:69518.1 Determining the codepoint for UTF8 characters.


If you can't figure out what's wrong log a tar, refer to this note and provide:
* the info asked in Note 226692.1 Finding out your NLS Setup.
* a spool (!not copy paste!) of the output of your version of the
"select dump(ename,1016),ename from scott.emp where empno='7369';" select .

It might also be good to follow Database Character Set Healthcheck,
same here, if you are stuck, log a tar and provide all gatherd information.

note to support: if a ct log's a TAR about this please *do* request a ARE review.

-6- More in depth debugging.
----------------------------

The steps 1-4 should be enough for 99% of the cases, the rest of the note is more in depth debugging

On some platforms, it can be usefull to use the following syntax to have more details about
the codepage really used:

$ locale LC_CTYPE | head

example of output in a HP-UX env:
""
""
"iso885915"
""
example of output in a Linux env:
upper;lowe ;alpha;digit;xdigit;space;print;graph;blank;cntrl;punct;alnum;combining;combining_level3
toupper;tolower;totitle
16
1
ISO-8859-15
70
84
1
0
1

$ locale LC_CTYPE | head
upper;lower;alpha;digit;xdigit;space;print;graph;blank;cntrl;punct;alnum;combining;combining_level3
toupper;tolower;totitle
16
6
UTF-8
70
84
1
0
1


On Solaris, AIX, TRU64, this syntax doesn't give interesting complementary information.
To find more details about these settings:
on Solaris, have a look in /usr/lib/locale.
on AIX, have a look in /usr/lib/nls/README
on TRU64, have a look in /usr/lib/nls
on HP-UX, have a look in /usr/lib/nls/config
on Linux, have a look in /usr/share/locale/locale.alias


How to check the codepoints managed by the O.S.:

To know which code point is generated for a character in a Unix Environment,
you can use the "od" command like this (examples with a iso-8859-1 locale):

$ od -xc
é^D^D
0000000 00e9
351 \0
0000001

as you can see the hexa-decimal code point e9 is corresponding to the "é" (lower e acute)
351 is the corresponding Octal value (Octal is the native mode of the od command).

You can also check the character corresponding to a code point using the "echo" command like this:

for Solaris, AIX, HP-UX, TRU64:

$echo '\0351'
é

for Linux:

$echo -e '\0351'
é

As you can see, echo uses the Octal value, so you need to convert in octal the value you want to check.


ReferencesNote 158577.1 - NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 179133.1 - The correct NLS_LANG in a Windows Environment
Note 265090.1 - How to check Unix terminal Environments for the capability to display extended characters.
http://www.xfree86.org/4.2.0/xfontsel.1.html - to see which fonts are known in the xwin env
http://www.xfree86.org/4.2.0/xlsfonts.1.html - to see what fonts are installed on the system
http://publib.boulder.ibm.com/infocenter/pseries/index.jsp?topic=/com.ibm.aix.doc/aixprggd/nlsgdrf/locale_env.htm - IBM locale settings reference
http://docs.hp.com/en/5187-3605/ch02.html - HP Encoding Characters Reference
http://docs.hp.com/en/UXL10N-90302/UXL10N-90302.pdf - HP Language configuration reference
http://developers.sun.com/dev/gadc/faq/locale.html - Sun Solaris locale reference
http://www.opengroup.org/onlinepubs/007908799/xbd/locale.html - UNIX Open Group locale specifications reference
Note 119119.1 - AL32UTF8 / UTF8 (unicode) Database Character Set Implications

The correct NLS_LANG in a Windows Environment

Subject: The correct NLS_LANG in a Windows Environment
Doc ID: Note:179133.1 Type: BULLETIN
Last Revision Date: 28-DEC-2005 Status: PUBLISHED


Content:
--------

1. Key concepts/terminology.
2. How to set up my NLS_LANG
3. The correct NLS_LANG for my Windows ANSI Code Page
4. The correct NLS_LANG for my DOS / Command Prompt OEM Code Page
5. How to check the NLS_LANG
6. List of common NLS_LANG to be set in Windows registry
7. List of common character sets to be used in a command prompt
8. How Windows uses Fonts to display the different charactersets

1. Key Concepts/terminology:
----------------------------

NLS_LANG consist of: NLS_LANG=_.

This note covers the part of NLS_LANG and provide
windows specific information in addition of
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?).
Please read that note first to have an idea how NLS_LANG works..

1.1 Windows and Dos Code Pages:
-------------------------------

On Windows systems, the encoding scheme (=Characterset) is specified by a Code Page.
Code Pages are defined to support specific languages or groups of languages
which share common writing systems.

From Oracle point of view the terms Code Page and Characterset mean the same.

Note that in non Chinese-Japanese-Korean environments, the Windows GUI and DOS
command prompt do not use the same code page (!).

As a result windows uses 2 different charactersets for the ANSI (sqlplusw.exe)
and to OEM (dos box - sqlplus.exe) environments

To illustrate the issue:
open notepad, type some "üéèç�" , save that file as c:\test.txt
(make sure that the encoding save as setting of notepad is set ot ANSI)
open a dos box
Start - Run - cmd - ok
and type

C:\Documents and Settings\user>edit c:\test.txt

you see "³ÚÞþÓ" in edit

Here you see a file in "ansi" encoding (notepad) displayed by the OEM
(dos box) environment of edit. You see clearly that windows do not use
the same characterset for the ANSI and CMD / DOS box environments.
For more questions about this, please contact Microsoft.

1.2 Fonts:
----------

A font is a collection of glyphs (from "hieroglyphs") that share common
appearances (typeface, character size). A font is used by the operating system
to convert a numeric value into a graphical representation on screen.

A font does not necessarly contain a graphical representation for all numeric
values defined in the code page you are using.
That's why you get sometimes black squares on the screen if you change fonts and the new
that font has no representation for a certain symbol.

The Windows "Character Set Map" utility can be used to see which glyphs are part
of a certain font.
On Windows 2000:
Start -> Programs -> Accessories -> System Tools -> Character Map
or
Start -> Run...
Type "charmap", and click "ok"

A font also implements a particular code page or set of code pages.
For example, the Arial font implements the code pages 1252, 1250, 1251, 1253,
1254, 1257.

For more in-depth info on fonts see point 8 in this note.

2. How to setup my NLS_LANG:
----------------------------

To specify the locale behaviour of your client Oracle software, you have to set
your NLS_LANG parameter.
It sets the language, territory and also the character set of your client.

For a short overview, it uses the following format:

NLS_LANG = LANGUAGE_TERRITORY.CHARACTERSET

where:
LANGUAGE specifies:
- language used for Oracle messages,
- day names and month names
TERRITORY specifies:
- monetary and numeric formats,
- territory and conventions for calculating week and day numbers
CHARACTERSET:
- controls the character set used by the client application
* or it matches your Windows code page
* or it set to UTF8 for an unicode application

The list of supported character sets,languages and territory
can also be found in the Oracle9i Globalization Support Guide,
Appendix A, Locale Data
Available online at the following URL:
http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage

4 important remarks:

* Setting the NLS_LANG to the characterset of the database MAY be correct
but IS NOT ALWAYS correct. Please DO NOT assume that NLS_LANG needs to be
ALWAYS the same as the database characterset. THIS IS NOT TRUE.

* The characterset defined with the NLS_LANG parameter does NOT CHANGE
your client's characterset, it is used to let Oracle know what characterset
you are USING on the client side, so Oracle can do the proper conversion.
You cannot just set NLS_LANG to the characterset you WANT.
If you need Hebrew support (for example) on an Cyrillic windows
then that windows need to be changed to have an 1255 ACP (see point 3),
just setting the nls_lang to hebrew will NOT allow you to retrieve/store hebrew.

* Another myth is that if you don't set the NLS_LANG on the client
it uses the NLS_LANG of the server. This is also NOT true!
The characterset part of the NLS_LANG parameter is never inherited from the server.
Please also see: Note 241047.1 The Priority of NLS Parameters Explained.

* Note that LANGUAGE and TERRITORY have nothing to do with the abillity to
*store* characters in a database.
A NLS_LANG set to JAPANESE_JAPAN.WE8MSWIN1252 will not allow you to store Japanese
as WE8MSWIN1252 dousn't know Japanese characters.

For and part of NLS_LANG see referenced notes
at the end of this note and Note 158577.1.

2.1 In the Registry:
--------------------

On Windows systems, you should make sure that you have set an NLS_LANG registry
subkey for each of your Oracle Homes:
You can easily modify this subkey with the Windows Registry Editor:
Start -> Run...
Type "regedit", and click "ok"
Edit the following registry entry:

for version 7:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

For version 8 , 8i and 9i:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\
where "x" is the unique number identifying the Oracle home.
HOME0 is the first installation

For version 10g:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_

There you have an entry with as name NLS_LANG

When starting an Oracle tools, like sqlplusw, it will read the content of
the oracle.key file located in the same directory to determine which registry
tree will be used, therefore which NLS_LANG subkey will be used.

Note:
-----
Some people are confused by finding a NLS_LANG set to "NA"
in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE when no version 7 was installed.
This is used for backwards compatibility, ignore this.
for 8.0, 8i and 9i you need to set the NLS_LANG in
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx\


2.2 As a System or User Environment Variable, in System properties:
-------------------------------------------------------------------

Although the Registry is the primary repository for settings on Windows, it is
not the only place where parameters can be set.
Even if not at all recommended, you can set the NLS_LANG as a System or User
Environment Variable in the System properties.
This setting will be used for ALL Oracle homes.
To check and modify them:

Right-click the 'My Computer' icon -> 'Properties'
Select the 'Advanced' Tab -> Click on 'Environment Variables'

The 'User Variables' list contains the settings for the specific OS user
currently logged on and the 'System variables' system-wide variables for all users.

Since these environment variables take precedence of the parameters already set
in your Registry, you should not set Oracle parameters at this location unless you
have a very good reason.
Particularly note the "ORACLE_HOME" parameter that is set on unix but NOT on windows.

2.3 As an Environment variable defined in the command prompt:
-------------------------------------------------------------

If you set the NLS_LANG as an environment variable in a Command prompt,
be aware that it will overrite the current NLS_LANG setting in the Registry
and also the System Properties.
In an MS-DOS command prompt, use the set command, for example:
C:\> set NLS_LANG=american_america.WE8PC850

3. The correct NLS_LANG for my Windows ANSI Code Page:
------------------------------------------------------

3.1 Determine your Windows ANSI code page:
------------------------------------------

The ACP (Ansi Code Page) is defined by the "default locale" setting of windows,
so if you have a UK Windows 2000 client and you want to input cyrillic (russian)
you need to change the ACP (by changing the "default locale") in order to be
able to input russian.
see Note 199926.1 How to change the ANSI Code Page (ACP) on Windows.

You'll find its value in the registry:
Start -> Run...
Type "regedit", and click "ok"
Browse the following registry entry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\
There you have (all the way below) an entry with as name ACP
The value of ACP is your current GUI Codepage, see the table in point 3.2
for the mapping to the oracle name.

Since there are many registry entries with very similar names, please
make sure that you are looking at the right place in the registry.

Again, if you need to change the "ACP" please see:
Note 199926.1 How to change the ANSI Code Page (ACP) on Windows
Do NOT simply change it in the registry.

Not all languages have a ANSI encoding, sqlplus only supports
ANSI and OEM charactersets.

The following URL provides a list of the default code pages
for a language for Windows 2000 / xp versions:

http://www.microsoft.com/globaldev/nlsweb/default.asp

OEM = the command line codepage (dos box), ANSI = the gui codepage

a ANSI codepage of 0 in this list means that the language is supported
by microsoft windows trough a unicode layer
and that there is no ANSI code page for that language

a OEM codepage of 1 in this list means that the language is supported
by microsoft windows trough a unicode layer
and that there is no OEM code page for that language

if you want to see / insert languages with a 0 or 1 on a windows client then
you need to use a Unicode client.
Sqlplusw.exe and sqlplus.exe are NOT unicode clients
You can use iSqlplus.
Note 231231.1 Quick setup of iSQL*Plus 9.2 as unicode (UTF8) client on windows.
Note 281847.1 How do I configure or test iSQL*Plus 10i?

note that the Honk Kong HKSCS is listed here:
http://www.microsoft.com/hk/hkscs/

3.2 Find the correspondent Oracle client character set:
-------------------------------------------------------

Find the Oracle client character set in the table below based
on the ACP you found in point 3.1.
Note that there is only ONE CORRECT value for a given ACP

Not all scripts / languages have ANSI support.
The following languages have NO ANSI support in windows:
Armenian, Divehi, Georgian, Gujarati, Hindi, Kannada, Oriya,
Konkani, Marathi, Punjabi, Sanskrit, Syriac, Tamil and Telugu.
(there are more... this list is just a example, see point 3.1 in this note )


ANSI CodePage (ACP) Oracle Client character set (3rd part of NLS_LANG)

1250 EE8MSWIN1250
1251 CL8MSWIN1251
1252 WE8MSWIN1252
1253 EL8MSWIN1253
1254 TR8MSWIN1254
1255 IW8MSWIN1255
1256 AR8MSWIN1256
1257 BLT8MSWIN1257
1258 VN8MSWIN1258
874 TH8TISASCII
932 JA16SJIS
936 ZHS16GBK
949 KO16MSWIN949
950 ZHT16MSWIN950 - except for Hong kong (see below)

This is the characterset used by the GUI sqlplus
(sqlplusW.exe/ plus80W.exe / plus33W.exe ) that you start trough
the windows start menu.

Please *DO* make the difference between the GUI sqlplus
and the "DOS mode" sqlplus (see point 4 in this note)


You can use UTF8 as Oracle client character set (=NLS_LANG) on Windows NT, 2000 and XP but
you will be limited to use only client programs that explicitly support this
configuration.

This is because the user interface of Win32 is not UTF8, therefore the client
*program* have to perform explicit conversions between UTF8 (used on Oracle
side) and UTF16 (used on Win32 side).

An example of such a program is Oracle Forms in version 5 and later on NT 4.0.
Note 105809.1 Character Set Support for Developer Tools
or iSqlPlus (from 817 onwards).

Note 231231.1 Quick setup of iSQL*Plus as unicode (UTF8) client on windows.
Note 281847.1 How do I configure or test iSQL*Plus 10i?

Contact Microsoft if you have questions about writing a unicode application.
a little (non-official, so nothing is endorsed by Oracle or Microsoft) intro:
http://www.jorendorff.com/articles/unicode/windows.html

From the other side, programs relying on ANSI Win32 API, like SQL*Plus,
older Oracle Forms , etc. cannot work with an NLS_LANG set to UTF8.

For euro support see:
Note 68790.1 RDBMS Support for the Euro Currency Symbol

For tools like sqlloader you need to set the NLS_LANG
to the characterset of the FILE you loading.
Note 227330.1 Character Sets & Conversion - Frequently Asked Questions
18. What is the best way to load non-US7ASCII characters using SQL*Loader?

For Export / Import please see:
Note 227332.1 NLS considerations in Import/Export

3.3 Set it in your Registry:
----------------------------

Use the Windows Registry Editor to set up the NLS_LANG in your Oracle Home
with the value you have just find above.
Section 2.1 gives you more details on how to use the Registry Editor for that
purpose.

4. The correct NLS_LANG for my DOS / Command Prompt OEM Code Page:
------------------------------------------------------------------

MS-DOS mode uses, with a few exceptions like CJK, a different code page
(called OEM code page) than Windows GUI (ANSI code page).

Meaning that before using an Oracle command line tool such as SQL*Plus
(sqlplus.exe/ plus80.exe / plus33.exe ) en svrmgrl in a command prompt
then you need to MANUALLY SET the NLS_LANG parameter as an environment
variable with the set DOS command BEFORE using the tool.

For Japanese, Korean, Simplified Chinese, and Traditional Chinese,
the MS-DOS OEM code page (CJK) is identical to the ANSI code page meaning that,
in this particular case, there is no need to set the NLS_LANG parameter in
MS-DOS mode.

Not all scripts / languages have OEM support.
The following languages have NO OEM support in windows:
Armenian, Divehi, Georgian, Gujarati, Hindi, Kannada, Oriya,
Konkani, Marathi, Punjabi, Sanskrit, Syriac, Tamil and Telugu.
(there are more... this list is just a example, see point 3.1 in this note )

In all other cases, you need to set it in order to overwrite the NLS_LANG
registry key already matching the ANSI code page. The new "MS-DOS dedicated"
NLS_LANG needs to match the MS-DOS OEM code page that could be retrieved by
typing chcp in a Command Prompt:

C:\> chcp
Active code page: 437
C:\> set NLS_LANG=american_america.US8PC437

If the NLS_LANG parameter for the MS-DOS mode session is not set appropriately,
error messages and data can be corrupted due to incorrect character set
conversion.

Use the following list to find the Oracle character set that fits to your MS-DOS
code page in use on your locale system:

MS-DOS code page Oracle Client character set (3rd part of NLS_LANG)
437 US8PC437
737 EL8PC737
850 WE8PC850
852 EE8PC852
857 TR8PC857
858 WE8PC858
861 IS8PC861
862 IW8PC1507
865 N8PC865
866 RU8PC866

There is no euro support in the Command Prompt OEM Code Pages, see:
Note 68790.1 RDBMS Support for the Euro Currency Symbol

For tools like sqlloader you need to set the NLS_LANG
to the characterset of the FILE you loading.
Note 227330.1 Character Sets & Conversion - Frequently Asked Questions
18. What is the best way to load non-US7ASCII characters using SQL*Loader?

For Export / Import please see:
Note 227332.1 NLS considerations in Import/Export


5. How to check the NLS_LANG:
-----------------------------

To check the NLS_LANG, you need to open a command prompt and to run sqlplus
in command line mode.

First, check if it's set in the environment:

SQL> host echo %NLS_LANG%

If this reports just %NLS_LANG% back, the variable is not set in the
environment. If it's set it reports something like
ENGLISH_UNITED KINGDOM.WE8PC850


If NLS_LANG is not set in the enviroment, you should check the value
in the registry:

SQL> @.[%NLS_LANG%].

If you get something like:
unable to open file ".[ENGLISH_UNITED KINGDOM.WE8ISO8859P1]."
the "file name" between the '[]' is the value of the registry parameter.

(This is NOT an error but just a "trick" to get the NLS_LANG value)

If you get this as result:
unable to open file ".[%NLS_LANG%]."
then the parameter NLS_LANG is also not set in the registry.

Note: the @.[%NLS_LANG%]. "trick" reports the NLS_LANG known by the sqlplus
executable, it will not read the registry itself.
But then you are not sure if the variable is set in the enviroment or in the
registry. That's the reason of checking with the host commando first.

6. List of common NLS_LANG's used in the Windows Registry:
----------------------------------------------------------

note: this is the correct setting for the GUI sqlplus version,
(sqlplusW.exe/ plus80W.exe / plus33W.exe )

If you are testing with "special" characters please DO use the gui
and not the "dos box" sqlplus.exe !

Not all scripts / languages have ANSI support.
The following languages have NO ANSI support in windows:
Armenian, Divehi, Georgian, Gujarati, Hindi, Kannada, Oriya,
Konkani, Marathi, Punjabi, Sanskrit, Syriac, Tamil and Telugu.
(there are more... this list is just a example, see point 3.1 in this note )

Operating System Locale NLS_LANG Value

Arabic (U.A.E.) ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
Bulgarian BULGARIAN_BULGARIA.CL8MSWIN1251
Catalan CATALAN_CATALONIA.WE8MSWIN1252
Chinese (PRC) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Chinese (Taiwan) TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
Chinese (Hong Kong HKCS) TRADITIONAL CHINESE_HONG KONG.ZHT16HKSCS
Chinese (Hong Kong HKCS2001) TRADITIONAL CHINESE_HONG KONG.ZHT16HKSCS31 (new in 10gR2)
(see http://www.microsoft.com/hk/hkscs/ how Microsoft supports HKCS and
http://www.info.gov.hk/digital21/eng/structure/cli_main.html )
Croatian CROATIAN_CROATIA.EE8MSWIN1250
Czech CZECH_CZECH REPUBLIC.EE8MSWIN1250
Danish DANISH_DENMARK.WE8MSWIN1252
Dutch (Netherlands) DUTCH_THE NETHERLANDS.WE8MSWIN1252
Dutch (belgium) DUTCH_BELGIUM.WE8MSWIN1252
English (United Kingdom) ENGLISH_UNITED KINGDOM.WE8MSWIN1252
English (United States) AMERICAN_AMERICA.WE8MSWIN1252
Estonian ESTONIAN_ESTONIA.BLT8MSWIN1257
Finnish FINNISH_FINLAND.WE8MSWIN1252
French (Canada) CANADIAN FRENCH_CANADA.WE8MSWIN1252
French (France) FRENCH_FRANCE.WE8MSWIN1252
German (Germany) GERMAN_GERMANY.WE8MSWIN1252
Greek GREEK_GREECE.EL8MSWIN1253
Hebrew HEBREW_ISRAEL.IW8MSWIN1255
Hungarian HUNGARIAN_HUNGARY.EE8MSWIN1250
Icelandic ICELANDIC_ICELAND.WE8MSWIN1252
Indonesian INDONESIAN_INDONESIA.WE8MSWIN1252
Italian (Italy) ITALIAN_ITALY.WE8MSWIN1252
Japanese JAPANESE_JAPAN.JA16SJIS
Korean KOREAN_KOREA.KO16MSWIN949
Latvian LATVIAN_LATVIA.BLT8MSWIN1257
Lithuanian LITHUANIAN_LITHUANIA.BLT8MSWIN1257
Norwegian NORWEGIAN_NORWAY.WE8MSWIN1252
Polish POLISH_POLAND.EE8MSWIN1250
Portuguese (Brazil) BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
Portuguese (Portugal) PORTUGUESE_PORTUGAL.WE8MSWIN1252
Romanian ROMANIAN_ROMANIA.EE8MSWIN1250
Russian RUSSIAN_CIS.CL8MSWIN1251
Slovak SLOVAK_SLOVAKIA.EE8MSWIN1250
Spanish (Spain) SPANISH_SPAIN.WE8MSWIN1252
Swedish SWEDISH_SWEDEN.WE8MSWIN1252
Thai THAI_THAILAND.TH8TISASCII
Spanish (Mexico) MEXICAN SPANISH_MEXICO.WE8MSWIN1252
Spanish (Venezuela) LATIN AMERICAN SPANISH_VENEZUELA.WE8MSWIN1252
Turkish TURKISH_TURKEY.TR8MSWIN1254
Ukrainian UKRAINIAN_UKRAINE.CL8MSWIN1251
Vietnamese VIETNAMESE_VIETNAM.VN8MSWIN1258



7. List of common NLS_LANG's used in the Command Prompt (DOS box):
------------------------------------------------------------------

note: this is the correct setting for the DOS BOX sqlplus version,
(sqlplus.exe/ plus80.exe / plus33.exe )

Not all scripts / languages have OEM support.
The following languages have NO OEM support in windows:
Armenian, Divehi, Georgian, Gujarati, Hindi, Kannada, Oriya,
Konkani, Marathi, Punjabi, Sanskrit, Syriac, Tamil and Telugu.
(there are more... this list is just a example, see point 3.1 in this note )


Operating System Locale Oracle Client character set (3rd part of NLS_LANG)

Arabic AR8ASMO8X
Catalan WE8PC850
Chinese (PRC) ZHS16GBK
Chinese (Taiwan) ZHT16MSWIN950
Czech EE8PC852
Danish WE8PC850
Dutch WE8PC850
English (United Kingdom) WE8PC850
English (United States) US8PC437
Finnish WE8PC850
French WE8PC850
German WE8PC850
Greek EL8PC737
Hebrew IW8PC1507
Hungarian EE8PC852
Italian WE8PC850
Japanese JA16SJIS
Korean KO16MSWIN949
Norwegian WE8PC850
Polish EE8PC852
Portuguese WE8PC850
Romanian EE8PC852
Russian RU8PC866
Slovak EE8PC852
Slovenian EE8PC852
Spanish WE8PC850
Swedish WE8PC850
Turkish TR8PC857


8. How Windows uses Fonts to display the different charactersets:
-----------------------------------------------------------------

We assume you have an UTF8 database with correctly stored UTF8 codepoints.

On Windows there are two kinds of tools / applications:

1)A fully Unicode enabled applications which accepts Unicode codepoints and
which can render them. It's the application that needs to deal with the Unicode,
Windows provides the unicode API but the GUI system itself is NOT Unicode
"by nature".
A fully Unicode application can only show one glyph for a given Unicode
code point. So there is NO confusion possible here, this application will need
to use a full unicode font. If you have a full unicode application, then you
need to set the NLS_LANG to UTF8.

Note that there are currently NOT many applications like this and if it's not
explicitly mentioned by the vendor it's most likely an ANSI application (see
below). So DON'T set the NLS_LANG to UTF8 if you are not sure!

Tcode capable client that is included in the database is iSQLPLus.
See Note 231231.1 Quick setup of iSQL*Plus as unicode client on windowisandard ANSI application (like sqlplusw.exe) cannot use Unicode
code points. So the Unicode code point stored in the database needs to be
CONVERTED to a ANSI code point. This is done by setting NLS_LANG (as described
in further on in this note and in Note 158577.1.
This allows oracle to map the unicode point to the characterset of the client,
(and here comes the tricky part)but this is NOT the same as a font.

If you want to display Arabic for example then you need to set the Windoabic. That way Windows knows what are valid codepoints and
can use the FONT engine to DISPLAY the codepoints (this results in glyphs).
Windows passes the codepoint and the "page" to the rendering engine.
This "page" defines the glyphs for the codepoints for a certain
characterset/codepage.

Because there are osible positions for a ANSI application, and one
font contains normally glyphs for different languages this "page" is used to
select from a FONT that has (for example) all the glyphs for Cyrillic, Arabic
and West-European the "page" for arabian.

So lets say you have a Arabic setup that works, you change manually the "Page"
of a FONT and ask to display the glyph for ANSI codepoint XX. Now 1 of 2 things
can happen:

1) There is a character defined on that position for the CHARACTERSET of that
"Page", so the creator of the font has forseen a glyph and this is displayed
(but this is NOT the character expected or wanted as its stored as a different
character in the database!).

2) There is NO character defined on that position for the CHARACTERSET of that
"Page" so the creator of the font has NOT forseen a glyph and you get "garbage"
or black squares (normally you should see a black square but a ? or ? are also
possible, this depends on the error handling defined in the FONT).

The above is also possible if you have an non-Unicode characterset for the
database.

For more information see also:
Note 137127.1 Character Sets, Code Pages, Fonts and the NLS_LANG Value

Related Documents:
==================
Note 241047.1 The Priority of NLS Parameters Explained.
Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 137127.1 Character Sets, Code Pages, Fonts and the NLS_LANG Value
Note 199926.1 How to change the ANSI Code Page (ACP)nd Windows 2000

Note 226558.1 An example insillic data into a database on23706.1> Using Locale Builder to view the definition of character sets
Note 132453.1 How to Change the Displayed Font in SQL*Plus (GUI) on WinNT
Note 231231.1 Quick setup of iSQL*Plus as unicode (UTF8) client on windows.
Note 165259.1 How to set NLS Variables for different Applications using the same ORACLE_HOME

Oracle8i Installation Guide for Windows NT, Part Number A85302-01
Appendix D - National Language Support
http://otn.oracle.com/documentation/oracle8i.html

- Oracle9i Database Installation Guide for Windows, Part Number A90162-01
Appendix E - Globalization Support
http://otn.oracle.com/documentation/oracle9i.html

- This Microsoft web site:
http://www.microsoft.com/globaldev/reference/oslocversion.mspx
provides a list of the default code pages for all Windows versions.


- updating/changing the NLS_LANG in the registry to the correct value:
this can easely be done by importing a .reg file who will then delete the old and add the new registry value.
like shown in http://www.winguides.com/article.php/8/

like:

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\]
"NLS_LANG"=-
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
"NLS_LANG"=-
"NLS_LANG"="DUTCH_BELGIUM.WE8MSWIN1252"


For further NLS / Globalization information you may start here:
Note 267942.1 Globalization Technology (NLS) Knowledge Browser