Posts Tagged ‘shutdown immediate’

Oracle Database : How to Shutdown Oracle Database – Shutdown Basics

October 3rd, 2018, posted in Oracle Queries
Share

Oracle Database and Instance

The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

Database Shutdown

During a database shutdown we close the database and terminates the instance.

Different Modes in Database Shutdown
There are different modes to bring down the database:
1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort
No user session will be permitted once you issue any of these Shutdown commands.


Shutdown Immediate

– Oracle Database terminates any executing SQL statements and disconnects users.
– Active transactions are terminated and uncommitted changes are rolled back.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown immediate


Shutdown Transactional

– This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown transactional


Shutdown Normal

– The database waits for all connected users to disconnect before shutting down.
– It waits till all the current transactions end.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown normal


Shutdown Abort

– Oracle Closes the datafiles without any checkpoint.
– This is the fastest shutdown mode.
– Instance recovery is required in the next startup and hence it will take time.

$ sqlplus / as sysdba
SQL> shutdown abort



Different Phases in Database Shutdown


Close the Database

– Oracle writes the data in the SGA to the disk, updates the file headers and closes the online datafiles and the redo log files.
– But the database will still be mounted.

Dismount the Database

– After the database is closed, Oracle Database unmounts the database to disassociate it from the instance.
– After a database is unmounted, Oracle Database closes the control files of the database.
– At this point, the instance remains in memory.

Shutdown the Instance

– The last step is to remove the shared memory (SGA) and terminate the background processes.
Sometimes shutdown does not cleanup the SGA or background process completely. This can cause error during the next startup. In such situation we can force a instance startup

Share

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