Character set Utilities

Database Character set utilities

The Database character set scanner access the feasibility of migrating an oracle database to new database character set. This database characterset scanner checks all character data in the database & tests for the effects & problem of changing the character set encoding.

The method of migrate the database's character sets.

1. Export & Import utilities.
2. CSALTER Script
3. CSALTER with Export & Import utilities on selected tables.

Scan modes in the Database character set scanner.

1. Full database scan

2. User Scan

3. Table Scan

4. Column Scan

How to Install Chractet set scanner:

1. Create a user named CSMIG

2. Assign the necessary privileges to CSMIG

3. Assign the Default ts to CSMIG

3. Create the character set scanner system tables under CSMIG

Use csminst.sql script to modify default tblespace for CSMIG.

% cd $ORACLE_HOME/rdbms/admin
% sqlplus sys/password as sysdba
SQL> @csminst.sql


C:\Users\Babu>csscan help=y


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:41:12 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:

Example: CSSCAN SYSTEM/MANAGER

Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:

Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes owner of tables to be scanned
TABLE yes list of tables to scan
COLUMN yes list of columns to scan
EXCLUDE list of tables to exclude from scan
TOCHAR yes new database character set name
FROMCHAR current database character set name
TONCHAR new national character set name
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

C:\Users\Babu>csscan system/sys tochar=utf8 user=scott log=D:\scott_scan.log


Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Fri Sep 5 22:49:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 >

Enumerating tables to scan...

. process 1 scanning SCOTT.MASTER[AAAM7wAAEAAAAGhAAA]
. process 1 scanning SCOTT.DEPT[AAAMgxAAEAAAAAJAAA]
. process 1 scanning SCOTT.BONUS[AAAMg1AAEAAAAApAAA]
. process 1 scanning SCOTT.EMP[AAAMgzAAEAAAAAZAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

How a Database Is Quiesced

The database administrator uses the ALTER SYSTEM QUIESCE RESTRICTED statement to quiesce the database. Only users SYS and SYSTEM can issue the ALTER SYSTEM QUIESCE RESTRICTED statement. For all instances with the database open, issuing this statement has the following effect:

• Oracle Database instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.

• Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or terminate). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. If a query is carried out by multiple successive OCI fetches, Oracle Database does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch. Oracle Database also waits for all sessions (other than those of SYS or SYSTEM) that hold any shared resources (such as enqueues) to release those resources. After all these operations finish, Oracle Database places the database into quiesced state and finishes executing the QUIESCE RESTRICTED statement.

• If an instance is running in shared server mode, Oracle Database instructs the Database Resource Manager to block logins (other than SYS or SYSTEM) on that instance. If an instance is running in non-shared-server mode, Oracle Database does not impose any restrictions on user logins in that instance.

During the quiesced state, you cannot change the Resource Manager plan in any instance.
The ALTER SYSTEM UNQUIESCE statement puts all running instances back into normal mode, so that all blocked actions can proceed. An administrator can determine which sessions are blocking a quiesce from completing by querying the v$blocking_quiesce view.

Trace Analyzer

When you generate a raw Oracle SQL Trace file you are faced with the arduous task of analyzing this trace file. Of course, TKPROF has always been there to help you with this analysis. This post is not about TKPROF, it is about another, lesser known, utility called Trace Analyzer, that is everything TKPROF is and more.

What is Trace Analyzer?

Trace Analyzer is a utility that reads a raw SQL Trace generated by standard SQL Trace or by EVENT 10046 (Level 4, 8 or 12), and generates a comprehensive HTML report with performance related details. The HTML report contains information about the Oracle instance (i.e. Statspack type of information) as well as all the details found on TKPROF including a very nice explain plan. The HTML report page is hyper-linked so you can easily jump to different sections of the report.

Where can I get Trace Analyzer?

Trace Analyzer is available as a MetaLink download, note # 224270.1. After you download the zip file, you need to perform a few installation steps before you can use this utility.

How do I install Trace Analyzer?

Let’s go through the installation steps on Oracle XE (Windows XP with Oracle home = C:\oraclexe\app\oracle\product\10.2.0\server):

Unzip the content of the zip file into in your Oracle home folder. Your Oracle home should now contain a folder called “trca”.

In your DOS command window, go to the trca folder in your Oracle home directory

C:\>cd oraclexe\app\oracle\product\10.2.0\server\trca

Run SQL*Plus and connect as SYS

C:\oraclexe\app\oracle\product\10.2.0\server\trca>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 16 14:22:42 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Run the Trace Analyzer installation script

SQL> @tacreate.sql

... Creating TRCA$ UDUMP Directory

... Creating TRCANLZR user ...

Choose the TRCANLZR user's password.

Not specifying a password will result in the installation FAILING

Specify TRCANLZR password

Enter a password for the new user TRCANLZR

Enter value for trcanlzr_password: tracemeifucan

tracemeifucan

... Getting Host String ...

Specify Host String (TNS Alias) including @ symbol (if not applicable just hit e

nter) Just hit enter here

Specify Host String including @ prefix (i.e.: @CERBDEV)

Enter value for host_string:

Below are the list of online tablespaces in this database.

Decide which tablespace you wish to create the Trace Analyzer tables

and indexes.  This will also be the TRCANLZR user's default tablespace.

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for tools data is not supported.

TABLESPACE_NAME                CONTENTS

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

SYSAUX                         PERMANENT

TEMP                           TEMPORARY

UNDO                           UNDO

USERS                          PERMANENT

Specify TRCANLZR user's default tablespace

You have to enter the default tablespace here, otherwise the installation will fail. You may wish to create a new tablespace before installation to assign the TRCANLZR objects to it.

Enter value for default_tablespace: USERS

Using USERS for the default tablespace

Choose the TRCANLZR user's temporary tablespace.

Specifying the SYSTEM tablespace will result in the installation

FAILING, as using SYSTEM for the temporary tablespace is not recommended.

Specify TRCANLZR user's temporary tablespace.

You have to enter the temporary tablespace here, otherwise the installation will fail.

Enter value for temporary_tablespace: TEMP

Using TEMP for the temporary tablespace

...    

TACPKG complete. Please check tacpkg.lis for any errors.

That’s it. Trace Analyzer is intalled. You can now execute Trace Analyzer from any schema.

How do I use Trace Analyzer?

Use from SQL*Plus connected as the user that originated the SQL Trace:

SQL> connect hr

Enter password:

Connected.

To provide maximum trace data, start a Level 12 trace:

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Let Oracle collect trace data during the session

SQL> select count(*) from employees;

  COUNT(*)

----------

       107

SQL>  ALTER SESSION SET sql_trace = FALSE;

Take note of the trace file that was generated in the UDUMP directory on the server. Now, execute the trcanlzr.sql script passing the trace file name as the parameter. This generates the Trace Analyzer HTML report into your local client directory using SPOOL

SQL> @trcanlzr.sql xe_ora_832.trc

....

...generating report

Trace Analyzer Report "trcanlzr_832_1.html" has been created in C:\ORACLEXE\APP\

ORACLE\ADMIN\XE\UDUMP

...copying report

...report "trcanlzr_832_1.html" has been copied into local directory

...exiting now (hit enter key)

Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Or

connect sys/ora@dbname as sysdba

exec trac$i.trace_analyzer(‘urtrace_ora_sid.trc’)

Why is Trace Analyzer better than TKPROF?

Trace Analyzer is better than TKPROF because It:

• Provides the actual values of the bind variables in SQL.
• Provides the hottest blocks, optimizer statistics for indexes and tables and other information not available through TKPROF.
• Separates user recursive and internal recursive calls, unlike TKPROF.
• Provides more detailed wait event information.
• Generates an HTML report that is more readable and extensive than the text output generated by TKPROF.

However, unlike TKPROF, Trace Analyzer requires objects in the database and that means more configuration work on each database that needs the utility installed. Moreover, TKPROF executes more quickly than Trace Analyzer, but that’s because Trace Analyzer does more analysis than TKPROF.

Rman New Feature On 10.2.0

RMAN Encrypted Backups

RMAN now supports several forms of encryption for backups created as backup sets, whether on disk or on tape. Encryption can be based upon passwords provided through RMAN or transparent encryption capabilities based upon the Oracle Encryption Wallet. Once configured, existing RMAN backup procedures take advantage of encryption features with no change.

Flashback Database Enhancements

Flashback Database can now reverse the effects of OPEN RESETLOGS operations, returning a database to points in time in ancestor or even sibling incarnations. This allows its use in many more data recovery scenarios. It also integrates with guaranteed restore points.

Restore Points

Restore Points are aliases for SCNs, which eliminate the need to manually research and record SCNs or timestamps to use for Flashback Database and Flashback Table operations.

Guaranteed Restore Points

Guaranteed restore points ensure that RMAN FLASHBACK DATABASE can be used to return your database to a specific point in time. Using guaranteed restore points instead of regular logging for Flashback Database uses disk space more efficiently and reduces performance impact of flashback logging when the only requirement is return to a specific point in time. Used in this way, guaranteed restore points provide an efficient alternative to a storage snapshot.

Guaranteed restore points can also be used with normal Flashback Database logging, to guarantee FLASHBACK DATABASE works to any time as far back as the guaranteed restore point.

Incremental Roll Forward of Database Copy

RMAN incremental backups can now be used to update a standby database with changes from a primary since a given SCN.

Easy Conversion of Physical Standby Database to a Reporting Database

Easy conversion of a physical standby database to a reporting database and back to a standby is now possible, because Flashback Database can now reverse the activation of a standby database. A guaranteed restore point retains the state of the standby before activation, and after reporting the DBA can flash back the standby to that guaranteed restore point, use incremental backups to update the standby with changes from the primary during reporting, and resume managed recovery.

Database Transport Across Same Endian Platforms

RMAN now supports the CONVERT DATABASE command, which can prepare a whole database for transport to a new platform that uses the same endian format. Database transport across platforms provides a faster and easier way to move databases from one platform to another than previous solutions requiring the use of Data Pump.

Transportable Tablespaces from Backup

RMAN now automates the creation of transportable tablespace sets using backups instead of the datafiles of the running database. With a single RMAN command, you can now create transportable sets without making the source datafiles read-only.

Unused Block Compression

RMAN now creates more compact backups of datafiles, by skipping datafile blocks that are not currently used to store data. In previous releases, RMAN only supported NULL compression, which skipped space in datafiles that had never been allocated. No extra action is required on the part of the DBA to use this feature.

Temporary Datafiles Are Re-Created on RMAN Recovery

Temporary datafiles that belong to locally managed temporary tablespaces are automatically re-created during database recovery. This eliminates the need to manually create temporary tablespaces after recovery.

Support for Backup Vaulting in Media Managers

When used with a media manager that supports backup vaulting, RMAN RESTORE... PREVIEW now reports any backups that are currently stored remotely, and RMAN RESTORE... PREVIEW RECALL now initiates retrieval of vaulted backups for use in an actual RESTORE operation.

Backup and Recovery Enhancements in Enterprise Manager

Enterprise Manager now includes backup validation, enhanced backup reporting and scheduling, and automated creation and management of recovery catalog databases.