Oracle Utilities – NID, CSSCAN, ORAPWD, DBV, OERR

NID:

  • It used to change the DBID (Database Identifier) or DBNAME (Database Name) of an Oracle database.
  • The nid (short for New Identifier) tool is especially useful in scenarios like cloning databases, restoring backups, or renaming a database.

Options:

TARGET - Specifies the database connection to the target database. Usually in the form of username/password.
DBNAME    - Specifies the new database name if you want to change the database name.
SETNAME    - Indicates whether the database name should be changed. Default is NO. If changing the DBNAME, this must be set to YES.
LOGFILE    - Specifies the name and path of the log file that will store the output of the nid utility.
CHECKONLY -    Runs the nid utility in read-only mode to verify if the database is in a suitable state for changing the DBID or DBNAME.
DBID -    If provided, nid will attempt to change the DBID to the specified value. This option is rarely used, as Oracle typically generates a new DBID automatically.

Usage:

Changing DBID:

Required when cloning a database to prevent conflicts with the original database in environments like RMAN.
Ensures unique identification of the database in recovery catalog or backups.
run like 

nid target=sys/password

Renaming the Database (DBNAME):

Change the database name when required, such as when moving databases to new environments.
run like 

nid target=sys/password dbname=new_dbname setname=yes

Changing Both DBID and DBNAME:

Commonly used after cloning a database to completely differentiate the clone from the source database.
run like 

nid target=sys/password dbname=new_dbname

Working Scenario:

source dbname: orange
target dbname: apple

step 1: start listener and setup the tnsnames also

Step 2: take full backup and set environment variable

step 3: down the database and start with mount stage

SQL> shut immediate;
SQL> startup mount;

step 4: run nid tool

nid target=sys/sysdba@orange dbname=apple logfile=nid_apple.log

step 5: change the db_name parameter in the parameter file

SQL> shut immediate
SQL> startup nomount;
SQL> alter system set db_name=apple scope=spfile;
SQL> shut immediate;
SQL> startup nomount;
SQL> show parameter db_name;
SQL> shut immediate;

step 6: rename the spfile

cp spfileorange.ora spfileapple.ora

step 7: export target db name and startup the nomount stage

export ORACLE_SID=apple
sqlplus / as sysdba
SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open resetlogs;
SQL> show parameter local;
SQL> alter system register;

step 8: update Listener.ora and tnsname.ora files

CSSCAN and CSALTER:

  • A database character set defines a set of characters that can be stored in the database and defines how each character maps to a sequence of bytes stored in memory or disk to represent this character.
  • A character set is also known in the industry as a character encoding. You can think of a character encoding as a set of characters plus their encoding in bytes.

A database character set is used for the following:

VARCHAR2
CHAR
LONG
CLOB
SQL, PL/SQL, and Java stored code in the database

The CLOB data is stored in the database character set only if the character set is single-byte. Otherwise, it is stored in AL16UTF16, which is Unicode UTF-16 encoding in big-endian form, abbreviated as UTF-16BE.

The CSSCAN and CSALTER utilities are Oracle tools used for character set migration of a database.
These tools help in analyzing, verifying, and changing the database character set to ensure compatibility and avoid data loss or corruption.

Commonly Used Character Sets in Oracle 19c

1. Unicode Character Sets (Recommended)

Unicode character sets are preferred because they support multiple languages and ensure compatibility.
AL32UTF8    - UTF-8 encoding, supports all Unicode characters. Recommended for modern applications.
UTF8    - Older UTF-8 encoding (superseded by AL32UTF8).
UTFE    - UTF-8 encoding for EBCDIC platforms.
AL16UTF16    - UTF-16 encoding, used for NCHAR, NVARCHAR2, and NCLOB data types.

2. Single-Byte Character Sets

Single-byte character sets are typically used for specific languages or regions.
US7ASCII    - 7-bit ASCII encoding. Used for English-only data.
WE8MSWIN1252    - Windows Latin-1, supports Western European languages.
WE8ISO8859P1    - ISO Latin-1, supports Western European languages.
EE8ISO8859P2    - ISO Latin-2, supports Eastern European languages.
JA16EUC    - Extended UNIX Code for Japanese.
ZHS16GBK    - Simplified Chinese GBK.

3. Multi-Byte Character Sets

Used for languages with large character sets like Chinese, Japanese, and Korean.
JA16SJIS    - Shift-JIS encoding for Japanese.
ZHS16GB2312    - Simplified Chinese (GB2312).
ZHT16BIG5    - Traditional Chinese (Big-5).
KO16KSC5601    - Korean Standard Code (KSC5601).

4. National Character Sets

These are used for the NCHAR, NVARCHAR2, and NCLOB data types.
AL16UTF16    - UTF-16 encoding (default for national character sets).
UTF8    - UTF-8 encoding (optional for national character sets).


Recommended Character Set: AL32UTF8
Why?
Supports all Unicode characters.
Ideal for multilingual applications.
Ensures better compatibility and scalability.
Handles emojis, symbols, and special characters effectively.

How to Check the Current Character Set

Database Character Set:

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

National Character Set:

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';

Workflow for Character Set Migration:

Run CSSCAN to analyze compatibility.
Review the results and fix any data issues.
Backup the database.
Use CSALTER or the ALTER DATABASE command to change the character set.
Restart the database and verify the changes.

CSSCAN (Character Set Scanner)
The CSSCAN tool scans the database for potential issues that may arise when changing the character set. It identifies data that is incompatible with the target character set.

Key Features:

Scans the database for data that cannot be safely converted to the target character set.
Reports data truncation, data loss, or invalid representations.
Prepares the database for a smooth character set migration.

Syntax:
csscan user/password FULL=y TOCHAR=<new_character_set> LOGFILE=csscan.log

Common Options:

USER    Specifies the username/password for connecting to the database.
FULL    Specifies whether to scan the entire database (y) or just specific schemas (n).
FROMCHAR    The current database character set. By default, this is determined automatically.
TOCHAR    The target character set for the migration.
LOGFILE    Specifies the name of the log file that stores the results of the scan.
EXCLUDE    Excludes certain schemas or tables from the scan.
INCLUDE    Includes only the specified schemas or tables in the scan.
PROCESS    Number of worker threads to use for scanning.
REPLACE    Replaces previously generated log files.
FEEDBACK    Sets the number of rows processed before feedback is displayed (default: 1000).
Example:
Scanning a full database for conversion from WE8MSWIN1252 to AL32UTF8:
csscan user=sys/password FULL=y TOCHAR=AL32UTF8 LOGFILE=csscan.log

2. CSALTER (Character Set Alteration Utility)
The CSALTER tool is used to alter the database character set after ensuring compatibility using CSSCAN.
Starting with Oracle 12c, the process has been streamlined using the ALTER DATABASE CHARACTER SET SQL command, making CSALTER deprecated in newer versions.

Steps to Use CSALTER (if still supported in your version):

Run CSSCAN:

Perform a full scan of the database and ensure there are no serious issues.
CSSCAN "sys/sysdba@apple AS SYSDBA" FULL=Y

Backup the database.
Shut down and start the database in MOUNT mode.

Run CSALTER:

sqlplus / as sysdba
SQL> STARTUP RESTRICT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> @?/rdbms/admin/csalter.plb

Restart the Database:
SHUTDOWN IMMEDIATE;
STARTUP;

Common Options for CSALTER

  • CHARACTER SET    Specifies the new character set to be applied to the database.
  • NCHAR CHARACTER SET    Specifies the new national character set (optional).


Example:
Changing the database character set to AL32UTF8:
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

ORAPWD:

  • The ORAPWD utility in Oracle 19c is used to create and manage the password file, which stores the credentials for users with SYSDBA and SYSOPER privileges. 
  • This password file enables these privileged users to connect to the database remotely without requiring the database to be open.

Features of ORAPWD in Oracle 19c:

  1. Enables Remote Administration: Users with privileges like SYSDBA can connect remotely to administer the database.
  2. Supports Multiple Entries: The password file can hold multiple privileged user accounts.
  3. Encryption: Passwords in the file are stored in encrypted form for security.
  4. Single Sign-On (SSO): Shared across RAC instances in Real Application Clusters (RAC) setups.

Syntax:
orapwd FILE=<file_name> PASSWORD=<password> ENTRIES=<max_users> FORCE=<Y|N> FORMAT=<12|19> SYSBACKUP=<Y|N>

Parameters:

FILE    - Specifies the full path and name of the password file to be created or modified.
PASSWORD    - Sets the initial password for the SYS user.
ENTRIES    - Maximum number of users that the password file can hold. Default is 10.
FORCE    - If Y, overwrites an existing password file. Default is N.
FORMAT    - Specifies the format of the password file: 12 - For backward compatibility and 19 - For Oracle 19c and newer features.
SYSBACKUP    - Enables the SYSBACKUP privilege in the password file if set to Y.

how to create?

Single-Instance Database:

orapwd FILE=$ORACLE_HOME/dbs/orapwORCL PASSWORD=admin123 ENTRIES=20 FORMAT=19

FILE: Creates the password file as orapwORCL in the $ORACLE_HOME/dbs/ directory.
PASSWORD: Sets admin123 as the SYS user password.
ENTRIES: Allows up to 20 users with administrative privileges.
FORMAT: Uses the 19c password file format.

For RAC Environments:

In RAC, the password file is shared across instances, typically stored in ASM or a shared storage location:
orapwd FILE=+DATA/ORCL/orapwORCL PASSWORD=admin123 ENTRIES=50 FORMAT=19

Modifying a Password File
To add or remove users or change the password, you can use:

ALTER USER sys IDENTIFIED BY new_password CONTAINER=ALL;

Viewing Password File Configuration
Check if the database is using a password file:
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

DBV:

The DBV (Database Verification) utility in Oracle is a command-line tool used to verify the integrity of data blocks in a database. It checks for corruption in database files, ensuring that the database is free of physical corruption.

Syntax:

dbv file=<datafile_path> [blocksize=<size>] [start=<start_block>] [end=<end_block>] [feedback=<rows>] [userid=<user/password>] [logfile=<log_file_name>]

Parameters

file    - Full path to the data file to be verified.
blocksize    - Specifies the block size of the data file (default is 8192 bytes).
start    - The starting block for verification.
end    - The ending block for verification.
feedback    - Displays progress every specified number of blocks.
userid    - User credentials to access the database for specific validations (optional).
logfile    - Specifies the file where the output of DBV will be logged.

working scenario:

step 1: Verifying the TABLESPACE_NAME and its associated SEGMENT_NAME

SQL> select SEGMENT_NAME,TABLESPACE_NAME from dba_segments where OWNER=’SCOTT’;

step 2: Find the header block for the table 'emp'

SQL> SELECT header_block FROM dba_segments WHERE segment_name='emp';

step 3: list out the datafiles

SQL> select file_name from dba_data_files;

Step 4: corrupt the block

dd of=/u02/oradata/apple/user01.dbf bs=8192 conv=notrunc seek=201 << EOF  --check step 2 result add plus one like 200+1

step 5: To flush the buffer cache and select the emp table

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> select * from emp; --you got the block error

step 6: find the block corruption

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
(OR)
RMAN> backup validate database archivelog all;

step 7: verify the corruption use DBV

dbv file=/u02/oradata/apple/user01.dbf blocksize=8192

step 8: recover the block using RMAN

RMAN> recover datafile 2 block 201;

step 9: after verify

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
SQL> select * from emp;

Script to get list of DBV commands of all datafiles:

set echo off
set feedback off
set verify off
set pages 0
set termout off
set linesize 150
spool dbv.cmd
select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) from v$datafile;
spool off

OERR:

The OERR utility in Oracle is a command-line tool used to retrieve detailed error messages and their descriptions, based on Oracle's error codes. This tool is helpful for database administrators and developers when troubleshooting Oracle errors, as it provides the corresponding text description, possible causes, and suggestions for resolution.

Key Features of OERR

Error Code Lookup:

It provides descriptions of Oracle error messages using the error code.

Quick Access to Error Information:

Helps in quickly retrieving information about Oracle errors without searching through documentation.

Error Resolution:

Offers suggestions for resolving errors based on their error codes.

Localized Versions:

Supports various languages for error descriptions, if configured.

Syntax:

oerr <error_type> <error_code>

options:

<error_type>: The type of error, such as ORA for database-related errors or TNS for network-related errors.
<error_code>: The specific error number

Types of Errors

  1. ORA: For Oracle database errors.
  2. TNS: For network-related errors.
  3. PLS: For PL/SQL-related errors.
  4. SQL: For SQL errors.
  5. EXP: For export utility errors.
  6. RMAN: For RMAN (Recovery Manager) errors.

How to Use:

Retrieve an Oracle Database Error like ORA-12514 (TNS: listener does not currently know of service requested in connect descriptor)

oerr ora 12514

Retrieve a TNS Error like TNS-12170 error (TNS:Connect timeout occurred):

oerr tns 12170

Retrieve a SQL Error like SQL-01000 error (SQL: maximum open cursors exceeded):

oerr sql 1000

Retrieve a PL/SQL Error like PLS-00201 error (PL/SQL: identifier must be declared):

oerr pls 201

Comments

Post a Comment