Posts Tagged ‘Oracle Database’

Change Oracle Database Character Set WE8MSWIN1252 to AL32UTF8

April 15th, 2018, posted in Oracle Queries
Share

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

 

 

 

 

 

 

 

 

 

Changing character set for Oracle database.

My scenario is to support Arabic data format.

My existing oracle database character set is : WE8MSWIN1252

My requirement is to change this character set to  AL32UTF8 ( Which will support Arabic format)

Step :1

Set your ORACLE_HOME and SID .

Windows :

SET  ORACLE_HOME= E:\app\7stl\product\11.1.0\db_1

SET SID=ORCL

Linux : Use export command to set ORACLE_HOME and SID.
How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

Step 2 :

Connect sqlplus through command prompt.

Enter user name as below shown in screenshot.
user-name:    / as sysdba

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

step 3:

Shutdown your database immediate or normal by using below command.

shutdown normal
How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

Step 4 :

mount your database by using below command

startup mount

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

 

step 5:

Now execute below commands

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.

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

 

 

Step 6:

SQL> ALTER DATABASE OPEN;
Database altered.

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

Step 7:

Now change the character set by using below command.

ALTER DATABASE CHARACTER SET AL32UTF8;

If you facing this error ” ORA-12712: new character set must be a superset of old character set”
use below command to overcome from this issue.

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

If you facing below error while executing above command then follow below steps:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30556: functional index is defined on the column to be modified

Execute this command first then followed by character set command.

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

SQL> alter system set “_system_trig_enabled”=FALSE

System altered.

Step 8:

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

Step 9:

SQL> SHUTDOWN NORMAL;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

 

 

Now query your Database to confirm the change in CHARACTER SET.

Use below query :

select * from database_properties where PROPERTY_NAME in (‘NLS_CHARACTERSET’,  ‘NLS_NCHAR_CHARACTERSET’);

How to change Oracle database character set WE8MSWIN1252 to AL32UTF8,Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252,How to change Oracle database character set AL32UTF8,How to change Oracle database character set english to arabic,Oracle dba,oracle apps dba,oracle database character set,oracle database, character set,nls database parameters,database parameters,oracle nls database parameters,oralce NLS LANGUAGE,Oracle database NLS LANGUAGE

 

 

 

 

 

 

Your character set has changed successfully.

Share

Fixing the ORA-27102: out of memory Error in Oracle on Solaris 10

April 8th, 2018, posted in Oracle Queries, Solaris
Share

Symptom:

As part of a database tuning effort you increase the SGA/PGA sizes; and Oracle greets with anORA-27102: out of memoryerror message. The system had enough free memory to serve the needs of Oracle.

SQL> startup
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument

Diagnosis

$ oerr ORA 27102
27102, 00000, "out of memory"
// \*Cause: Out of memory
// \*Action: Consult the trace file for details

 

Not so helpful. Let’s look the alert log for some clues.

 

% tail -2 alert.log
WARNING: EINVAL creating segment of size 0x000000028a006000
fix shm parameters in /etc/system or equivalent

 

Oracle is trying to create a 10G shared memory segment (depends on SGA/PGA sizes), but operating system (Solaris in this example) responded with an invalid argument (EINVAL) error message. There is a little hint about setting shm parameters in/etc/system.

Prior to Solaris 10,shmsys:shminfo_shmmaxparameter has to be set in/etc/systemwith maximum memory segment value that can be created. 8M is the default value on Solaris 9 and prior versions; where as 1/4th of the physical memory is the default on Solaris 10 and later. On a Solaris 10 (or later) system, it can be verified as shown below:

 

% prtconf | grep Mem
Memory size: 32760 Megabytes
% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)
% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

 

Now it is clear that the system is using the default value of 8G in this scenario, where as the application (Oracle) is trying to create a memory segment (10G) larger than 8G. Hence the failure.

So, the solution is to configure the system with a value large enough for the shared segment being created, so Oracle succeeds in starting up the database instance.

On Solaris 9 and prior releases, it can be done by adding the following line to/etc/system, followed by a reboot for the system to pick up the new value.

set shminfo_shmmax = 0x000000028a006000Howevershminfo_shmmaxparameter was obsoleted with the release of Solaris 10; and Sun doesn’t recommend setting this parameter in/etc/systemeven though it works as expected.

On Solaris 10 and later, this value can be changed dynamically on a per project basis with the help of resource control facilities . This is how we do it on Solaris 10 and later:

 

% prctl -n project.max-shm-memory -r -v 10G -i project 3
% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      10.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

 

Note that changes made with theprctlcommand on a running system are temporary, and will be lost when the system is rebooted. To make the changes permanent, create a project withprojaddcommand and associate it with the user account as shown below:

 

% projadd -p 3  -c 'eBS benchmark' -U oracle -G dba  -K 'project.max-shm-memory=(privileged,10G,deny)' OASB
% usermod -K project=OASB oracle

 

Finally make sure the project is created withprojects -lorcat /etc/projectcommands.

 

% projects -l
...
...
OASB
        projid : 3
        comment: "eBS benchmark"
        users  : oracle
        groups : dba
        attribs: project.max-shm-memory=(privileged,10737418240,deny)
% cat /etc/project
...
...
OASB:3:eBS benchmark:oracle:dba:project.max-shm-memory=(privileged,10737418240,deny)

 

With these changes, Oracle would start the database up normally.

 

SQL> startup
ORACLE instance started.
Total System Global Area 1.0905E+10 bytes
Fixed Size                  1316080 bytes
Variable Size            4429966096 bytes
Database Buffers         6442450944 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.

 


 

Addendum : Oracle RAC settings

Anonymous Bob suggested the following settings for Oracle RAC in the form of a comment for the benefit of others who run into similar issue(s) when running Oracle RAC. I’m pasting the comment as is (Disclaimer: I have not verified these settings):

Thanks for a great explanation, I would like to add one comment that will help those with an Oracle RAC installation. Modifying the default project covers oracle processes great and is all that is needed for a single instance DB. In RAC however, the CRS process starts the DB and it is a root owned process and root does not use the default project. To fix ORA-27102 issue for RAC I added the following lines to an init script that runs before the init.crs script fires.

 

# Recommended Oracle RAC system params
ndd -set /dev/udp udp_xmit_hiwat 65536
ndd -set /dev/udp udp_recv_hiwat 65536
# For root processes like crsd
prctl -n project.max-shm-memory -r -v 8G -i project system
prctl -n project.max-shm-ids -r -v 512 -i project system
# For oracle processes like sqlplus
prctl -n project.max-shm-memory -r -v 8G -i project default
prctl -n project.max-shm-ids -r -v 512 -i project default

So simple yet it took me a week working with Oracle and SUN to come up with that answer…Hope that helps someone out.

Share

ORA-00376 file 2 cannot be read at this time ORA-01548 UNDO Needs Recovery

February 27th, 2018, posted in Oracle Queries
Share

ORA-00376: file 2 cannot be read at this time ,

ORA-01548 UNDO “Needs Recovery”:

Error:

Wed May 21 07:52:20 2014
Errors in file c:\oracle\product\10.2.0\admin\ \bdump\ _cjq0_3412.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’

Wed May 21 07:52:20 2014
Errors in file c:\oracle\product\10.2.0\admin\ \bdump\ _cjq0_3412.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’

SQL> select file_name from dba_data_files where tablespace_name=’UNDOTBS1′;

FILE_NAME
 --------------------------------------------------------------------------------
 C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF
 C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS02.DBF

SQL>

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
 drop tablespace UNDOTBS1 including contents and datafiles
 *
 ERROR at line 1:
 ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace

SQL>

Solution:

From the below SQL it shows that UNDOTBS1 contains a few corrupt undo segments

SQL> select segment_name,status,tablespace_name from  dba_rollback_segs where status not in (‘ONLINE’,’OFFLINE’);

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
 ------------------------------ ---------------- ------------------------------
 _SYSSMU1$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU2$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU3$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU4$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU5$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU6$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU7$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU8$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU9$                      NEEDS RECOVERY   UNDOTBS1
 _SYSSMU10$                     NEEDS RECOVERY   UNDOTBS1

10 rows selected.

SQL>

ORA-00376 file 2 cannot be read at this time,ORA-01548 UNDO Needs Recovery,UNDO Needs Recovery,Needs Recovery,ORA-01548,ORA-00376,cannot be read at this time,Oracle dba,oracle apps,oracle database,oracle dba database,oracle need recovery,oracle recovery,database recovery,ORA 01548,ORA 00376,Ora error,apps dba,core dba,oracle dba,dba oracl,Oracle tablespace

 

 

 

 

 

To get past this problem:

  1. create a pfile from the current spfile,
  2. change UNDO_MANAGEMENT  to ‘MANUAL’ and UNDO_TABLESPACE to ‘SYSTEM’ in the pfile
  3. add the _corrupt_rollback_segments init parameter to the pfile    “_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)”
  4. startup the database using “startup pfile=’<location/name of pfile>’ “;
  5. drop corrupt UNDOTBS1 uncluding contents and datafiles
  6. create a new UNDO tablespace
  7. shutdown database again, and startup using the existing spfile (not pfile)

 

SQL> create pfile from spfile;

File created.

SQL>

*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS2′
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

 

 

SQL> startup pfile=’C:\oracle\product\10.2.0\db_1\database\INITwicustest.ora’;

ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2065408 bytes
Variable Size             788532224 bytes
Database Buffers          452984832 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> create UNDO tablespace UNDOTBS1 datafile ‘C:\ORACLE\ORADATA\ \DATA\ \UNDOTBS01.DBF’ size 250M;

Tablespace created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Wicus>sqlplus sys as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 21 07:56:13 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:
 Connected to an idle instance.
SQL> startup
 ORACLE instance started.

Total System Global Area 1258291200 bytes
 Fixed Size                  2065408 bytes
 Variable Size             788532224 bytes
 Database Buffers          452984832 bytes
 Redo Buffers               14708736 bytes
 Database mounted.
 Database opened.
 SQL>

 

Share

adcfgclone.pl dbTechStack – RC-50004 Error occurred in CloneContext Target System Domain Name

February 4th, 2018, posted in Oracle, Solaris
Share

RC-50004: Error occurred in CloneContext – Target System Domain Name in

adcfgclone.pl dbTechStack :

Error:

Target System Domain Name : worths.co.za
RC-50004: Error occurred in CloneContext: 
null
Check Clone Context logfile
/ebsdbprd_app/oracle/product/ebsdbprd/db/tech_st/11.2.0/appsutil/clone/bin/CloneContext_0418120037.log for details.

ERROR: Context creation not completed successfully.
For additional details review the file /tmp/adcfgclone_4849738.err if present.


adcfgclone.pl,oracle apps,oracle dba,oracle apps dba,oracle,dba,oracle error,ora error,oracle errors,adcfgclone.pl dbTechStack RC-50004 Error occurred in CloneContext Target System Domain Name,Target System Domain Name,adcfgclone.pl dbTechStack,RC-50004 Error,RC-50004,Error,clone error,oracle clone error,apps error,database error,oracle database,database oracle error,database,APPS-domain-50004,APPS domain 50004,RC 50004,RC 50004 Error

 

Solution :
Ensure that your hostname is fully populated in the /etc/hosts file

Not like this:

3 $ grep ebsdbprdp7 /etc/hosts
19.36.148.157   ebsdbprdp7
w@ebsdbprdp7:/
4 $

Should look like this

3 $ grep ebsdbprdp7 /etc/hosts
19.36.148.157   ebsdbprdp7.domain.co.za  ebsdbprdp7
w@ebsdbprdp7:/
4 $
Share

For Multiple Server Connections – Single Putty Window – Multiple Tabs

January 28th, 2018, posted in Oracle, Solaris, Windows
Share

Is it possible to have a tabbed putty like firefox or internet explorer ?? Single Putty Window,Multiple Tabs,For Multiple Server Connections,Putty Window,Multiple Tab,Multiple Server Connections,Putty,Multiple Server,DBA,Oracle DBA,Oracle Database,Oracle,Oracle SQL,
Yes you can, check out below URL !!

http://www.thegeekstuff.com/2008/08/turbocharge-putty-with-12-powerful-add-ons-software-for-geeks-3/

Look for Putty connection Manager.

Share