Changing the Oracle Database Characterset.

Hello,

If you want to change your database character set due to do some requirements.

Let’s do it step by step

1. Check your current database settings

SQL> set line999
SQL> select * from v$nls_parameters;
PARAMETER                VALUE
--------------           --------------
NLS_LANGUAGE             AMERICAN
NLS_TERRITORY           AMERICA
NLS_CURRENCY            $
NLS_ISO_CURRENCY        AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR            GREGORIAN
NLS_DATE_FORMAT         DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_CHARACTERSET        UTF8
NLS_SORT                BINARY
NLS_TIME_FORMAT         HH.MI.SSXFF AM

PARAMETER               VALUE
--------------          --------------
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT      HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY       $
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_COMP                BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP     FALSE

2. Shutdown Database

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

3. Follow the below steps:

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET internal_use WE8ISO8859P9;
SQL> SHUTDOWN IMMEDIATE; 
SQL> startup;

It is OK!

Advertisements

How to Optimize Oracle UNDO Parameters

Hi,

In this article, We will discuss optimizing ORACLE UNDO parameters. Transaction undo information is stored in rollback segments until a commit or rollback statement was executed. Sometimes we get an error “ORA-01555:Snapshot Too Old” on long running queries.  This error occures when we set UNDO parameters incorrectly. With UNDO_RETENTION parameter, automatic undo management allows to specify how long undo information must be retained after commit. The default value of this parameter is 900s. (15 min). If you set   UNDO_MANAGEMENT  parameter to AUTO and create a UNDO tablespace then ORACLE will manage it.

So how should UNDO_RETENTION up period? The answer to this question is proportional to transcend size UNDO Tablespace. Criticality of data and according to the disk, you can give an area for the UNDO tablespace. Calculating the optimum size of the amount of time you should set this parameter UNDO_RETENTION.

Now do a little mathematical calculation. First going to check our Undo tablespace size with the following query;

SELECT SUM(a.bytes) as UNDO_SIZE
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Then find the number of undo block per second;

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) as UNDO_BLOCK_PER_SECOND
  FROM v$undostat;

Let’s find the database block size;

SELECT TO_NUMBER(value) as DB_BLOCK_SIZE
 FROM v$parameter
WHERE name = 'db_block_size';

Now, according to the result obtained from the three that we will calculate the optimum time for UNDO_RETENTION parameter. Will use the formula as follows;

UNDO RETENTION TIME=(UNDO SIZE)/(DB BLOCK SIZE × UNDO BLOCK PER SECOND)

Put the values into the formula above to calculate undo retention time.

Now let’s look at the event reversal. If you have enough disk space and critical database transactions. For example, you set UNDO retention time is 1 hour (3600 sec) to keep our committed undo data. In this situation, you must allocate the appropriate UNDO tablespace size according to undo retention time. In this case, formula;

UNDO SIZE=UNDO RETENTION TIME × DB BLOCK SIZE × UNDO BLOCK PER SECOND

Finally, we solve them all with a single query to find optimum UNDO_RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

To find UNDO tablespace size according to the UNDO RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      as NEEDED_UNDO_SIZE"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

You can find ORA-1555 error count that you’re wondering in the last few from the moment our database is opened with the following query;

  select '"ORA-01555 (Snapshot too old)" hata sayısı: '
  || sum(ssolderrcnt)
  from v$undostat;

Wishing to be healthy databases with optimum parameters 🙂

Talip Hakan ÖZTÜRK

Quiescing Oracle Database

Hi Friends,

In this article, I will write about quiescing an Oracle Database.  Quiescing a database that allows only DBA transactions, queries, fetches, or PL/SQL statements. In is situation there is no non-DBA sessions. DBA is defined as user SYS or SYSTEM. Other users who assigned with the DBA role, are not allowed to issue quiesce command.

To place a database into a quiesced state, connect sql*plus and  run the following statement;

SQL> ALTER SYSTEM QUIESCE RESTRICTED;

After quiescing database non-DBA active sessions will continue until become inactive. No inactive sessions are allowed to become active. If an inactive session want to become active then it will appear tobe hung. The session can resume when the database is unquiesced.

If you issue above statement in the RAC environment, then it affects all instances. When you issue above statement, you may wait a long time for active sessions to become inactive. In is situation, you can find sessions that are blocking the quiescing operation by following query;

SQL> select bq.sid, username, osuser, program, machine

from v$blocking_quiesce bq, v$session s

where bq.sid = s.sid;

To restore the database to normal operation, run the following statement;

SQL> ALTER SYSTEM UNQUIESCE;

You can query V$INSTANCE view to see active state .

SQL> select active_state from v$instance;

You can see 3 situation.  NORMAL – Database is normal state (unquiescing). QUIESCING – Being quiesced, but some non-DBA sessions are still active. There are active sessions QUIESCED – Quiesced. There is no non-DBA sessions are active.

Talip Hakan Öztürk

Applying 11.1.0.7 PSU (Patch Set Update) to 11.1.0.6 Database

11.1.0.7 PSU (Patch Set Update) to 11.1.0.6 Database
Pre – Applying checks
1- Take backup your Oracle Home directory
$ tar -cf ora11g.tar ora11g
2- Take full database backup
3- Download p6890831_111070_Linux-x86-64.zip file over Metalink
Applying PSU (patch set update)
1- The time_zone check is very important check. it needs to be made and you can check it with this query;
SQL>SELECT version FROM v$timezone_file;

If you are running anything other than version 4, check MetaLink document 568125.1

2- Shutdown the database
SQL>shutdown database
3- Stop the services
$ emctl stop dbconsole
$ lsnrctl stop
4- Unzip p6890831_111070_Linux-x86-64.zip installation file. Run OUI (Oracle Universal Installer) in terminal

$ unzip p6890831_111070_Linux-x86-64.zip
$ cd Disk1
$ ./runInstaller
On the welcome screen click “Next” button

5- Choose the name of oracle home and click “Next” button.

6- Uncheck “I wish to receive security updates via MetaLink” check box. If you want, you can configure this option after installation.

If you unchecked the check box, you will warned. Click “Yes” to continue.

7- After prerequisite checks you can start the installation.

8- After installation of rdbms PSU you must check permissions of newly created folders on operating system.
Now it is time of database upgrade

9- Pre-upgrade checks

SQL> STARTUP UPGRADE
SQL> SPOOL pre_upgrade_check.log
SQL> @?/rdbms/admin/utlu111i.sql
SQL> SPOOL OFF
Examine the pre_upgrade_check.log file. Examine tablespace sizes and database components status

10- Start database in upgrade mode and run the script catupgrd.sql

$ lsnrctl start
$ sqlplus / as sysdba
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
Examine the patch.log file. If there is error then you must solve the problem and rerun catupgrd.sql script.

11- If there is no error, shutdown database and startup normal.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

12- Post-Upgrade checks

SQL> SPOOL post_upgrade_check.log
SQL> @?/rdbms/admin/utlu111s.sql
SQL> SPOOL OFF
Examine post_upgrade_check.log file.

13- After upgrading database, some objects will be invalid. For recompiling run the following script.

SQL> @?/rdbms/admin/utlrp.sql

14- Check the status of database components.

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

Talip Hakan ÖZTÜRK

Physical Standby Database SWITCHOVER & FAILOVER Operations

SWITCHOVER:

Article I had written previously was the  creation standby database with RMAN active database duplication. In this article, I will write about  how to switchover from the previous article scenario.

In short, switchover is the planned role change. So, primary database will operate as standby and standby will operate as primary database. Spare our database interoperability testing, to do maintenance work in production (primary) database , etc. can make for things like switchover. Switchover process  can be done in Enterprise Manager, Data Guard Broker and also manually via sqlplus. In this article I will use sqlplus on the manual switch method. I will  remind you the servername  [server]  before each processing step .I want to make more of a reminder. If we run commands on linux, it will start with “$”,  on sqlplus is “SQL>” and, while on rman “RMAN>” as a start.

1. [istanbul] Switch log file on primary database.

SQL>alter system switch logfile;

2. [istanbul] Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_STANDBY” as result.

3. [istanbul] Switch primary database to standby database.

SQL>alter database commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

4. [istanbul] Defer for archive log apply. Because I didn’t set  my standby database as primary yet.

SQL>alter system set log_archive_dest_state_2=defer;

5. [Baku] Switch standby database to primary. Check switchover status before switching database.

SQL>select switchover_status from v$database;

You must see “TO_PRIMARY” as result. Now let’s swtich

SQL>alter database commit to switchover to primary;

SQL>shutdown immediate;

SQL>startup;

Our switchover process is successfully completed .
6. [Istanbul] Start real-time recovery process..

SQL>recover managed standby database using current logfile disconnect;

Finally let’s open our database with “Read Only with Apply”.

SQL>recover managed standby database cancel;

SQL>alter database open;

SQL>recover managed standby database using current logfile disconnect;

FAILOVER:

In short, the failover is the deformation of the production (primary) database and activating standby database as the primary. It is not reversible. When enabled, re-create the standby database. What to do in case of failover:

(Important note: Istanbul is the primary server and Baku is the standby server)

1. [Istanbul] If the primary database is accessible and running, then it must provided  to send redo buffer to the standby database.

SQL> alter system flush redo to standby_db_name;

SQL>alter system archive log current;

If you don’t receive an error, you can continue with step 5th. In this case, the system can be opened by zero data loss. If you receive an error, We continue with step 2 to open the system at least data loss.

2. [Baku] We must run the following query to learn last applied archive log sequence number.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

3. [Istanbul’dan Baku’ye] If you can access archive logs which are not copied to standby then copy archives to standby. After copy archive log files we must register them to standby database. This operation must be done for every thread.

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_102.arc ';

4. [Baku] Check the standby database for redo gap. If there is a gap then we must copy archive log files and register.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

SQL> alter database register physical logfile '/oracle/ora11g/dbs/arch/ TALIP_991834413_1_101.arc ';

As a result of the above query until it returns to zero.

5. [Baku] Stop the redo apply process in standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. [Baku] Finish to apply archive logs copied from primary.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

If you get an error, it means there are redo logs not applied. Consider 2th and 4th steps. You can also continue with following command;

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

In this situation you can open database in 8th step.  If you get no error, continuw with 7th step.

7. [Baku] Switch standby database to primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

8. [Baku] Open database.

SQL> ALTER DATABASE OPEN;

After opening standby database as primary with failover you must take full backup.

Failover ile Standby veritabanı primary olarak açıldığında mutlaka full yedek alınmalıdır. And as soon as possible  you must configure a new standby database. Please note that our data are very valuable for us 🙂

Talip Hakan Öztürk