In 11g RAC SCAN Listener Down due to Upgrade in Firewall

Hi Guy's,

Last week all the users are not able to connect the RAC-database.

Scan Listener Services are Down After Firewall Restart

Because of this issue users are not able to connect, when I check the status, I saw scan listener was down.

C:\Users\oracle> crsctl status res -t

ora.LISTENER.lsnr

               ONLINE  OFFLINE      oradb1

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  OFFLINE

ora.scan2.vip

      1        ONLINE  OFFLINE

  >>>>>> <<<<<>>>>>>>>>>>>>><<<<<<<<<<<<<

C:\Users\oracle> srvctl start scan_listener

C:\Users\oracle> srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node oradb2

SCAN VIP scan2 is enabled

SCAN VIP scan2 is not running

SCAN VIP scan3 is enabled

SCAN VIP scan3 is running on node oradb2

C:\Users\oracle> srvctl start scan_listener

PRCC-1014 : LISTENER_SCAN1 was already running

PRCR-1004 : Resource ora.LISTENER_SCAN1.lsnr is already running

PRCR-1079 : Failed to start resource ora.LISTENER_SCAN1.lsnr

CRS-5702: Resource 'ora.LISTENER_SCAN1.lsnr' is already running on 'oradb2'

PRCC-1014 : LISTENER_SCAN3 was already running

PRCR-1004 : Resource ora.LISTENER_SCAN3.lsnr is already running

PRCR-1079 : Failed to start resource ora.LISTENER_SCAN3.lsnr

CRS-5702: Resource 'ora.LISTENER_SCAN3.lsnr' is already running on 'oradb2'

Still Node1 Listener Down

C:\Users\oracle>srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): oradb2

C:\Users\oracle>srvctl status listener -n oradb1

Listener LISTENER is enabled on node(s): oradb1

Listener LISTENER is not running on node(s): oradb1

Now  Check Listener Again

C:\Users\oracle>srvctl start listener -n oradb1

C:\Users\oracle>srvctl status listener -n oradb1

Listener LISTENER is enabled on node(s): oradb1

Listener LISTENER is running on node(s): oradb1

C:\Users\oracle> srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node oradb2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node oradb1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node oradb2


> Now all the Listeners are Up.



Happy Learning.

Deleting Trace files in Oracle

Hi Guy's,

You can delete trace files which will generate in your database by using this command.
in windows environment.

> I am deleting 30 days old  trc files you can go as per your requirement.


C:\Users\oracle> forfiles /p d:\app\oracle\diag\rdbms\testdb2\testdb2\trace /m *.trc /d -30 -c "cmd /c del/q @FILE"





Happy Learning.

Number of CPU's in Oracle Database Server

Hi Guy's,

Pls check this query to find the Database Server CPU's.



SQL> set pagesize 299
           set lines 299
           select STAT_NAME,to_char(VALUE) as VALUE  ,COMMENTS from v$osstat where stat_name  IN                            ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS' union
          select STAT_NAME,VALUE/1024/1024/1024 || ' GB'  ,COMMENTS from v$osstat where stat_name 
          IN ('PHYSICAL_MEMORY_BYTES') ;

STAT_NAME                                       VALUE                                  COMMENTS
------------------------------------------------------------------------------------------------------------------------------

NUM_CPUS                                             24                                        Number of active CPUs

NUM_CPU_CORES                                12                                        Number of CPU cores

NUM_CPU_SOCKETS                            2                                          Number of physical CPU sockets

PHYSICAL_MEMORY_BYTES   95.952686309814453125 GB          Physical memory size in bytes

Happy Learning..

How to change existing Database Characterset in Oracle

Hi Guy's,
I got the task to change the database character-set to Arabic Language for an existing UAT database.

Note : - Incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set.

• Database character set migration has two stages Data Scanning & Data Conversion. Before you change the database character set.
• We need to identify possible database character set conversion problems and truncation of data. This step is called data scanning, for example number of schema objects where the column widths need to be expanded and the extent of the data that does not exist in the target character repertoire.
• Before using the Database Character Set Scanner, you must run the csminst.sql script to set up the necessary system tables on the database that you plan to scan.
• The csminst.sql script needs to be run only once. The script performs the following tasks to prepare the database for scanning:

1. Creates a user named CSMIG
2. Assigns the necessary privileges to CSMIG
3. Assigns the default tablespace to CSMIG
4. Creates the Character Set Scanner system tables under CSMIG

• First check the existing character-set of a database.

        SQL> select * from nls_database_parameters;

• Now execute csminst.sql script,

Steps: -
SQL> @D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\csminst.sql
User created.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
User altered.
1 row created.
1 row updated.
Table created.
drop public synonym csm$parameters
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$query
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$tables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$columns
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$extables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$errors
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$langid
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$charsetid
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$indexes
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$constraints
 ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$triggers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Table created.
drop public synonym csm$dictusers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
18 rows created.
View created.
drop public synonym csmv$tables
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$columns
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.
View created.
drop public synonym csmv$errors
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$indexes
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$constraints
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
drop public synonym csmv$triggers
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
View created.
View created.
View created.

View created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now run CSSCAN utility:

• Using CSSCAN we can scan the data in the database and generate report that lists any issues involves in changing NLScharacter set of database.

• When you run CSSCAN it opens 4 options of database Scan.

1. Full Database Scan:  Scanner reads and verifies the character data of all tables belonging to all users in the database including the data dictionary (such as SYS and SYSTEM users), and it reports on the effects of the simulated migration to the new database character set. It scans all schema objects including stored packages, procedures and functions, and object definitions stored as part of the data dictionary.

2. User Scan: The Database Character Set Scanner reads and verifies character data of all tables belonging to the specified user and reports on the effects on the tables of changing the character set.

3. Table Scan : The Database Character Set Scanner reads and verifies the character data of the specified tables, and reports the effects on the tables of changing the character set.

4. Column Scan : The Database Character Set Scanner reads and verifies the character data of the specified columns, and reports the effects on the tables of changing the character set.

• I want to SCAN full database, so I selected (1) option
• Mention New Character-set which you want.

D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN> CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Sun Apr 28 15:00:24 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: system
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1
Current database character set is WE8MSWIN1252.
Enter new database character set name: > AR8MSWIN1256
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..): 1 >
Enumerating tables to scan...

. process 1 scanning SYS.AUD$[AAASZOAABAAAAkAAAA]
. process 1 scanning SYS.AUD$[AAASZOAAKAAAACAAAA]
 process 1 scanning HMIS.AHTRNHST_B4_OPENING_FINAL_BILL[AABfSAAAEAACVAAAAA]
 process 1 scanning HMIS.AHTRNHST_B4_OPENING_FINAL_BILL[AABfSAAAHAAEMiAAAA]
 process 1 scanning HMIS.OTORDERS[AABf4YAAHAAEIiAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAGAAARAAAAA]
 process 1 scanning HMIS.OTORDERS[AABf4YAAEAACRAAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAEAACdQAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAFAACjSAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAGAAA96YAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAHAAERCAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAEAACZgAAAA]
 process 1 scanning HMIS.HMIS_AUDIT_TRAIL[AABgLHAAFAACbDYAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAEAAABAAAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAFAAAEKAAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAGAAAsOAAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAGAAAlsAAAA]
 process 1 scanning HMIS.OTOCURNC_2[AABfP6AAGAAAA0AAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAFAAAGwAAAA]
 process 1 scanning HMIS.SHDESCPT[AABf3fAAGAAAveAAAA]
 process 1 scanning HMIS.OTOCURNC_2[AABfP6AAFAAAAiAAAA]
 process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
 process 1 scanning IDMS7.GLDOCENT[AABfvWAAHAAB84AAAA]
 process 1 scanning HMIS.SHDESCPT[AABf3fAAFAAAJYAAAA]
 process 1 scanning IDMS7.ACRTEMP1[AABf22AAEAAADgAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAEAAChQAAAA]
 process 1 scanning IDMS7.GLWORKFH[AABft+AAGAAAbiAAAA]
 process 1 scanning IDMS7.INVDTL[AABfs7AAGAAAVAAAAA]
 process 1 scanning IDMS7.INVSRVITM[AABfsxAAHAAEUyAAAA]
 process 1 scanning HMIS.SHSHTDTA_2[AABfQBAAGAAADEAAAA]
 process 1 scanning IDMS7.GLWORKFD[AABfuHAAGAAAhiAAAA]
 process 1 scanning HMIS.AMPATBIO[AABgQBAAGAAA/mAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAGAAAfiAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAFAACpSAAAA]
 process 1 scanning IDMS7.GLDOCHDR[AABfvPAAGAAAjsAAAA]
 process 1 scanning IDMS7.GL_MSTBCH[AABft0AAGAAAXSAAAA]
 process 1 scanning IDMS7.INVHDR[AABfs2AAGAAATAAAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_D[AABfuCAAHAAEWyAAAA]
 process 1 scanning IDMS7.GL_DTLBCH[AABft3AAGAAAZSAAAA]
 process 1 scanning HMIS.MJBAKBON[AABgGRAAHAAEa6AAAA]
 process 1 scanning IDMS7.CSH_TRN[AABfysAAGAAAo+AAAA]
 process 1 scanning HMIS.MJBAKBON[AABgGRAAGAAA60AAAA]
 process 1 scanning IDMS7.GLWORKFD_TMP_G[AABfuAAAGAAAdiAAAA]
 process 1 scanning IDMS7.TOTTAB1[AABfeqAAGAAAJ+AAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAEAACZhQAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAGAAAxuwAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAFAACrSAAAA]
 process 1 scanning HMIS.DRGMODLOG[AABf3sAAHAAEYyAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAEAACmSAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAFAACukAAAA]
 process 1 scanning SYS.HISTGRM$[AAAAGlAABAAAAqAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAGAAAHiIAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAFAAADkAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAGAAA9aAAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAGAAAPmAAAA]
 process 1 scanning HMIS.MMTRACK_BKUP[AABfS4AAHAAEd0AAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAHAAEh0AAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAHAAEh0QAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAEAACpioAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAGAAA9GgAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAGAAAHgoAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAHAAB8kAAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAFAACzkAAAA]
 process 1 scanning HMIS.OTOCUTRC[AABf4WAAGAAAy2AAAA]
 process 1 scanning IDMS7.ITH[AABfr8AAEAAAAsAAAA]
 process 1 scanning HMIS.MAPPER[AABgGoAAFAACykAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAFAAANqAAAA]
 process 1 scanning IDMS7.ITH_AZZA[AABfQXAAGAAAFK4AAA]
 process 1 scanning HMIS.OTOCUTRC[AABf4WAAFAAAJ+AAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAHAAEg0AAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAEAACoiAAAA]
 process 1 scanning SYS.WRI$_ADV_SQLT_PLANS[AAABcLAACAAAA5IAAA]
 process 1 scanning IDMS7.ITH_AZZA[AABfQXAAFAAC0kAAAA]
 process 1 scanning HMIS.LABRESULT[AABgI6AAHAAEf0AAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAEAACriAAAA]
 process 1 scanning HMIS.MARCVLOG[AABgGiAAFAAC2UAAAA]
 process 1 scanning HMIS.MMMEDREPORT[AABgWfAAFAACtaAAAA]
 process 1 scanning HMIS.OVRLOG[AABfSsAAEAACpiAAAA]
 process 1 scanning HMIS.MMLABTEST[AABfQHAAGAAAFEgAAA]
 process 1 scanning SYSTEM.SYS_IMPORT_FULL_01[AAA3GvAABAAAWMAAAA]
 process 1 scanning SYSTEM.SYS_IMPORT_FULL_02[AAA3HjAABAAAhbAAAA]
. process 1 scanning SYS.SQLOBJ$
. process 1 scanning SYS.SQLOBJ$DATA
. process 1 scanning SYS.CHNF$_CLAUSES
. process 1 scanning SYS.CHNF$_GROUP_FILTER_IOT
. process 1 scanning SYS.RECENT_RESOURCE_INCARNATIONS$
. process 1 scanning SYS.WRR$_REPLAY_SCN_ORDER
. process 1 scanning SYS.WRR$_REPLAY_SEQ_DATA
. process 1 scanning SYS.WRR$_WORKLOAD_ATTRIBUTES
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_T
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_H
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_G
. process 1 scanning SYS.AQ$_SCHEDULER$_EVENT_QTAB_I
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_T
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_H
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_G
. process 1 scanning SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_I
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_T
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_H
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_G
. process 1 scanning SYS.AQ$_SCHEDULER_FILEWATCHER_QT_I
. process 1 scanning SYS.AQ$_ALERT_QT_T
. process 1 scanning SYS.AQ$_ALERT_QT_H
. process 1 scanning SYS.AQ$_ALERT_QT_G
. process 1 scanning SYS.AQ$_ALERT_QT_I
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_T
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_H
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_G
. process 1 scanning SYS.AQ$_AQ$_MEM_MC_I
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_T
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_H
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_G
. process 1 scanning SYS.AQ$_AQ_PROP_TABLE_I
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_T
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_H
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_G
. process 1 scanning SYS.AQ$_KUPC$DATAPUMP_QUETAB_I
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_T
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_H
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_G
. process 1 scanning SYS.AQ$_SYS$SERVICE_METRICS_TAB_I
. process 1 scanning DBSNMP.BSLN_TIMEGROUPS
. process 1 scanning EXFSYS.EXF$ATTRSET
. process 1 scanning EXFSYS.EXF$EXPRSET
. process 1 scanning EXFSYS.EXF$EXPSETPRIVS
. process 1 scanning EXFSYS.EXF$ATTRLIST
. process 1 scanning EXFSYS.EXF$ASUDFLIST
. process 1 scanning EXFSYS.EXF$IDXSECOBJ
. process 1 scanning EXFSYS.EXF$EXPSETSTATS
. process 1 scanning CTXSYS.DR$PARAMETER
. process 1 scanning CTXSYS.DR$SQE
. process 1 scanning CTXSYS.DR$THS_FPHRASE
. process 1 scanning CTXSYS.DR$SECTION_ATTRIBUTE
. process 1 scanning CTXSYS.DR$STOPWORD
. process 1 scanning CTXSYS.DR$PENDING
. process 1 scanning CTXSYS.DR$ONLINE_PENDING
. process 1 scanning CTXSYS.DR$PARALLEL
. process 1 scanning CTXSYS.DR$DBO
. process 1 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 1 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$ERRCODE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 1 scanning EXFSYS.RLM$EVENTSTRUCT
. process 1 scanning EXFSYS.RLM$RULESET
. process 1 scanning EXFSYS.RLM$RULESETPRIVS
Creating Database Scan Summary Report...
Creating Individual Exception Report...
ORA-01455: converting column overflows integer datatype
CSS-00168: failed to report individual exceptions
CSS-00122: failed to create scan report
Scanner terminated successfully.

D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Apr 28 15:28:12 2019
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
INSTANCE_NAME           STATUS
---------------------------------------------------
iectest2                                 OPEN

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

SQL>  @D:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\csalter.plb

0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old   6:     if (UPPER('&conf') <> 'Y') then
new   6:     if (UPPER('y') <> 'Y') then
Checking data validity...
Exceptional data found in scanner result
PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.
SQL> alter system set job_queue_processes=0;
System altered.
SQL> alter system set AQ_TM_PROCESSES=0;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> alter system set nls_length_semantics=CHAR scope=both;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

SQL> alter database character set INTERNAL_USE AR8MSWIN1256;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2.0911E+10 bytes
Fixed Size                  2264328 bytes
Variable Size            5905580792 bytes
Database Buffers         1.4965E+10 bytes
Redo Buffers               38170624 bytes
Database mounted.
Database opened.

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
                                           NOW CHECK THE NEW CHARACTER-SET
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


SQL> select * from nls_database_parameters ORDER BY PARAMETER;

PARAMETER                                          VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                                   GREGORIAN
NLS_CHARACTERSET                         AR8MSWIN1256
NLS_COMP                                               BINARY
NLS_CSMIG_SCHEMA_VERSION            5
NLS_CURRENCY                                         $
NLS_DATE_FORMAT                       DD-MON-RR
NLS_DATE_LANGUAGE                 AMERICAN
NLS_DUAL_CURRENCY                           $
NLS_ISO_CURRENCY                      AMERICA
NLS_LANGUAGE                             AMERICAN
NLS_LENGTH_SEMANTICS                BYTE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_NCHAR_CONV_EXCP                FALSE
NLS_NUMERIC_CHARACTERS             .,
NLS_RDBMS_VERSION                      11.2.0.3.0
NLS_SORT                                              BINARY
NLS_TERRITORY                                 AMERICA
NLS_TIMESTAMP_FORMAT             DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                   HH.MI.SSXFF AM TZR

21 rows selected.


Happy Learning…..