Oct 11 2019

Oracle 19c R3 Real Application Clusters (RAC) Installation Steps on Oracle Linux 7.7

Category: AdministrationFatih Acar @ 11:07

Oracle Real Application Clusters (RAC) provides high availability (HA) and scalability to the Oracle Database without requiring any application changes. Oracle RAC allows multiple instances running on different servers to access the same physical database stored on shared storage. Database spans multiple hardware systems and yet appears as a single unified database to the application. This enables utilization of commodity hardware to reduce total cost of ownership and to provide a scalable computing environment that supports various application workloads.

Top Benefits of Real Application Clusters (RAC)

  • Ability to spread CPU load across multiple servers
  • Continuous Availability / High Availability (HA)

– Protection from single instance failures

– Protection from single server failures

  • RAC can take advantage of larger SGA sizes than can be accommodated by a single instance commodity server
  • Scalability

I will use VMware virtualization to servers. You can add disk for oracle asm storage with different iscsi driver. You have to adjust iscsi driver as virtual.

Oracle 19c RAC installation steps are a bit long. Firstly, you have to adjust system parameters of operating system on both node and create asm disks. After, you can start to install of grid infrastructure. You can create disk groups for DATA and FRA disk groups after install grid. Finally you can install Oracle RAC database on grid infrastructure and ASM disks. I divide the stages of installation three steps.

First Step : Configure Operation System on Both Servers

1. Upgrade All Packages

yum upgrade

Continue reading “Oracle 19c R3 Real Application Clusters (RAC) Installation Steps on Oracle Linux 7.7”

1,632 total views, 8 views today

Tags: Oracle, Oracle 19c, Oracle 19c RAC, Oracle Administration, Oracle RAC

Dec 02 2015

Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 17:30

You can move datafiles of tablespaces. Scenarios can be asm to file system or file system to file system or file system to file system or asm to asm. Below operations show how to move datafiles. You have to take a backup and database has to be archivelog mode before this operations because of that you do restore and recover operation.


1 – Database has to be Archivelog Mode
2 – Take Full Rman Backup
3 – Execute Rman Run block

Run Block Description:
Offline Tablespace.
Set Newname.
Restore Datafile To New Location.
Write New Location information to controlfile with switch datafile all.
Recover Datafile.
Online Tablespace.


List Tablespaces

SQL> select tablespace_name from dba_tablespaces;

List Datafile Directories

SQL> select tablespace_name,file_name from dba_data_files;

Move Datafile From Asm To File System

SET NEWNAME FOR DATAFILE ‘+DATA/dg/datafile/USERS.329.896267113’ TO ‘/tmp/USERS.329.896267113’;

Move Datafile From File System To From Asm
Continue reading “Move Datafiles Of Tablespaces With RMAN Set Newname in Oracle 11g”

9,346 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup, Oracle Rman Restore

Nov 15 2015

Auto Increment Primary Key With Trigger in Oracle 11g

Category: Administration,Procedure,SQLFatih Acar @ 14:50

You can provide auto increment primary with trigger by appling below operations.

Create Table

(id number primary key,
name varchar2(100));

Create Sequence

MAXVALUE 999999999

Create Trigger For Auto Increment

before insert on TESTUSER.TBL_TEST
for each row
if inserting then
if :NEW.”ID” is null then
select TESTUSER.SEQ_TEST_ID.nextval into :NEW.ID from dual;
end if;
end if;

Oracle 12c release has primary key auto increment feature with identity type column. You can use like below.
Continue reading “Auto Increment Primary Key With Trigger in Oracle 11g”

13,833 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle SQL Query

May 28 2015

How to Migrate Controlfile From File System to ASM Disk in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 13:36

You can migrate controlfile from file system to asm disk. You have to stop database and you can do that.

Stop Database

SQL> shutdown immediate;
SQL> startup nomount;

Migrate Controlfile

RMAN> restore controlfile to ‘+DATA’ from ‘/tmp/controlfile_currentlocation.ctl’;
RMAN> restore controlfile to ‘+FRA’ from ‘/tmp/controlfile_currentlocation.ctl’;

Continue reading “How to Migrate Controlfile From File System to ASM Disk in Oracle 11g”

14,595 total views, no views today

Tags: Oracle, Oracle Administration, Oracle Backup and Restore

Mar 09 2015

Oracle Cluster Service Control Commands CRSCTL

Category: AdministrationFatih Acar @ 09:54

crsctl is Oracle Clusterware management command. You can check, start, stop clusterware with crsctl. crsctl command directory is $GRID_HOME/bin.

You can do below operations with crsctl

  • Starting and stopping Oracle Clusterware resources
  • Enabling and disabling Oracle Clusterware daemons
  • Checking the health of the cluster
  • Managing resources that represent third-party applications
  • Integrating Intelligent Platform Management Interface (IPMI) with Oracle Clusterware to provide failure isolation support and to ensure cluster integrity
  • Debugging Oracle Clusterware components

List Status Of All Crs Services

crs_stat -t

Continue reading “Oracle Cluster Service Control Commands CRSCTL”

18,790 total views, no views today

Tags: crsctl, Oracle, Oracle Administration, Oracle Cluster Management

Apr 17 2014

What is Lightweight Job in Oracle

Category: Administration,ProcedureFatih Acar @ 14:46

Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

Lightweight jobs have the following characteristics:

  • Unlike regular jobs, they are not schema objects.
  • They have a significant improvement in create and drop time over regular jobs because they do not have the overhead of creating a schema object.
  • They have lower average session creation time than regular jobs.
  • They have a small footprint on disk for job metadata and runtime data.

You designate a lightweight job by setting the job_style job attribute to LIGHTWEIGHT. The other job style is REGULAR, which is the default.

Like programs and schedules, regular jobs are schema objects. In releases before Oracle Database 11g Release 1, regular jobs were the only job style supported by the Scheduler.

A regular job offers the maximum flexibility but does entail some overhead when it is created or dropped. The user has fine-grained control of the privileges on the job, and the job can have as its action a program or a stored procedure owned by another user.

Continue reading “What is Lightweight Job in Oracle”

18,921 total views, 5 views today

Tags: Database Administration, Oracle, Oracle Administration

Jan 06 2014

Restore And Recover Oracle 11g R2 Database on Windows 2008 R2 Server

Category: Administration,Backup And Recovery,Errors and SolutionsFatih Acar @ 15:43

You can restore and recover Oracle 11g R2 Database from RMAN backup on Windows 2008 R2 Server with below operations. You have to execute Command Prompt as Administrator for all operations.

Restore and Recover Database

Install Oracle software
Install Patch

Create ORACLE_HOME and ORACLE_SID in Server Environment Path.

Copy backup files to C:\orabackup

Create Oracle service with below command

cmd> oradim -NEW -SID YOURDBSID -STARTMODE manual -PFILE “$ORACLE_HOME\dbs\init.ora”

cmd> rman target /

RMAN> set dbid=12412312 Note : DBID writes in Control File backup name, if you use %F file name parameter.

RMAN> startup nomount force;

RMAN> restore spfile from ‘C:\orabackup\controlfilebackupfile’;

RMAN> exit;

cmd> sqlplus / as sysdba

SQL> create pfile=’C:\orabackup\pfile.ora’ from spfile;

You have to create archivelog directory and other directories in pfile.ora. You have to copy archivelog file to archivelog directory.

SQL> create spfile from pfile=’C:\orabackup\pfile.ora’;

SQL> shu abort;

SQL> startup nomount;

cmd> rman target /

RMAN> restore controlfile from ‘C:\orabackup\controlfilebackupfile’;

Control files restored to directories that are in pfile.ora

SQL> alter database mount;

If you use parallel backup to take backup, you can select backups like below.

RMAN> catalog backuppiece ‘C:\orabackup\backupfile1.bck’,’C:\orabackup\backupfile2.bck’;

RMAN> list backup;

You have to create directory for datafiles. Directories show in list backup;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

ORA-00392 log 3 of thread 1 is being cleared, operation not allowed
ORA-00312 online log 3 thread 1: ‘path’

If you get above error, you have to create path that writes in error. You get error because system can not create directory for redo logs.
You have to execute below SQL to clear logfile, you can open database.

SQL> alter database clear unarchived logfile group 3;

SQL> alter database open resetlogs;

SQL> select status from v$instance;

94,120 total views, no views today

Tags: Backup and Recovery, Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup

Dec 16 2013

ORA-03113: end-of-file on communication channel when startup

Category: Errors and SolutionsFatih Acar @ 15:37

If your fra disk or archivelog directory full, your db can not process any transaction. When you increase disk size, database does not recover itself. You have to do some process.



Continue reading “ORA-03113: end-of-file on communication channel when startup”

39,292 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Error Solutions

Dec 11 2013

Oracle Control Files Content

Category: AdministrationFatih Acar @ 11:14

A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle Database; no database administrator or user can edit a control file.

A control file contains

  • The database name
  • The timestamp of database creation
  • The names and locations of associated datafiles and redo log files
  • Tablespace information
  • Datafile offline ranges
  • The log history
  • Archived log information
  • Backup set and backup piece information
  • Backup datafile and redo log information
  • Datafile copy information
  • The current log sequence number
  • Checkpoint information

The database name and timestamp originate at database creation. The database name is taken from either the name specified by the DB_NAME initialization parameter or the name used in the CREATE DATABASE statement.

Each time that a datafile or a redo log file is added to, renamed in, or dropped from the database, the control file is updated to reflect this physical structure change. These changes are recorded so that:

Oracle Database can identify the datafiles and redo log files to open during database startup
Continue reading “Oracle Control Files Content”

13,656 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration

Dec 08 2013

Oracle Flashback Technology

Category: AdministrationFatih Acar @ 11:20

When authorized people make mistakes, you need the tools to correct these errors. Oracle Database provides a family of human error correction technology called Flashback. Flashback revolutionizes data recovery. In the past, it might take minutes to damage a database but hours to recover it. With Flashback technology, the time to correct errors equals the time it took to make the error. It is also easy to use a single, short command to recover the entire database instead of following some complex procedure.

Flashback is unique to Oracle Database and provides:

  • A SQL interface to quickly analyze and repair human errors.
  • Fine-grained surgical analysis and repair for localized damage, such as when the wrong customer order is deleted.
  • Correction of more widespread damage yet does it quickly to avoid long downtime, such as when all of this month’s customer orders have been deleted.
  • Recovery at all levels including the row, transaction, table, tablespace, and database wide.

Continue reading “Oracle Flashback Technology”

24,387 total views, no views today

Tags: Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore

Next Page »