Oct 12 2017

Oracle 12c R2 (12.2.0.1) Real Time Apply Data Guard Installation on Oracle Linux 7.3

Category: Administration,Backup And RecoveryFatih Acar @ 11:11

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Oracle Data Guard maintains these standby databases as copies of the production database.Then, if the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Oracle Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

With Oracle Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

Types of standby databases are Physical standby database, Logical standby database and Snapshot standby database.

I will demonstrate Physical standby database as working real time apply at this document. Most used type is Physical standby database type. You can investigate other types of standby database from Oracle docs.

I used Oracle RAC database as primary side and I used single instance database with asm file system as secondary side (Data Guard).

Firstly, you have to adjust system parameters of operating system on secondary side 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. After grid installation, you can install Oracle Data Guard database on grid infrastructure and ASM disks as software only. Finally you can restore and recover standby database from primary side and you can start synchronization apply after add standby redo log. I divide the stages of installation five steps.

You can find primary side (Oracle 12c RAC) installation steps here. I will use this infrastructure as primary side.

First Step : Configure Operation System on Secondery Side

1. Upgrade Packages

yum upgrade

yum install oracleasm-support

Continue reading “Oracle 12c R2 (12.2.0.1) Real Time Apply Data Guard Installation on Oracle Linux 7.3”

684 total views, 16 views today

Tags: Oracle 12c, Oracle Administration, Oracle Backup and Restore, Oracle Data Guard


Oct 10 2017

TNS-12547: TNS:lost contact and Linux Error: 32: Broken pipe While Connect Oracle 12c Data Guard

Category: Errors and SolutionsFatih Acar @ 15:39

I installed Oracle 12c Data Guard, everything was okey. But, I got an error like below when I wanted to connect data guard database with Toad or SQL Developer. tnsping was okey.

Error

SQL Developer :

Status : Failure -Test failed: IO Error: Got minus one from a read call

Listener.log

10-OCT-2017 15:01:16 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=testuser))(SERVICE_NAME=TDG)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=testuser))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.24)(PORT=49834)) * establish * TDG * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe

Solution

If you have grid user to manage listener, you have to give permission on $ORACLE_HOME/bin/oracle executable file. You have to give chmod 6751 permission. This provides that grid user can execute oracle file like oracle user. Oracle open process on OS when you connect to database. If you connect from remote with using listener, listener owner (grid) will want to open new process on OS but grid user cannot open process with using $ORACLE_HOME/bin/oracle file due to do not have permission. So you have to give 6751 permission to use $ORACLE_HOME/bin/oracle file.

[root@tdg bin] chmod 6751 oracle

You can see the same problem at 11g and 12c version of Oracle Data Guard. When oracle rac installation or single instance installation with grid, oracle automatically change permission of $ORACLE_HOME/bin/oracle file to 6751.

1,950 total views, 50 views today

Tags: Oracle 12c, Oracle Administration, Oracle Error Solutions


Mar 28 2017

Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database

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

Sometimes, you may need to change redo log size to perform optimum size for performance. If you want to change redo log size of both standby side and primary side, you can use below commands.

Current Status Of Redo Logs

Primary Side


SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 
GROUP#     Size in MB
-------    ----------
1          50
2          50
3          50
 
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 
GROUP#   Size in MB
-------  ----------
 4       50
 5       50
 6       50
 7       50

Standby Side



SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$loggroup by group#;
 
GROUP# Size in MB
------ -------------
 1     50
 2     50
 3     50
 
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 
GROUP# Size in MB
------ ----------
 4     50
 5     50
 6     50
 7     50

Change Size Of Redo Logs

Auto File Management Disable on Standby Side


SQL> alter system set standby_file_management=manual

System altered.

Change Redo Log Size Of Primary Side
Continue reading “Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database”

5,120 total views, 15 views today

Tags: Database Administration, Oracle Administration


Mar 28 2017

Oracle 11g R2 Open Data Guard Read Write Mode With Flashback Technology And Revert Restore Point

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

You can use flashback technology on standby side. If you want to make application test on database, you can use data guard as read write mode temporarily with flashback technology. You can create a restore point on standby side then you can do any transaction on standby side to test or any other purpose. After you complete your operations on the standby side, you can revert to restore point and again connect to primary side and apply logs from primary side.

This feature can be use for application test operations.

Perform Flashback Steps

Standby Side


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

Note : Check recovery destination and size. You have to have recovery dest and size.

SQL> show parameter recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /oracle/fra
db_recovery_file_dest_size	     big integer 10G
recovery_parallelism		     integer	 0

SQL> create restore point before_test guarantee flashback database;

Restore point created.

Primary Side


SQL> alter system archive log current;

System altered.

Note : Archive dest for standby log sync parameter set defer.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Continue reading “Oracle 11g R2 Open Data Guard Read Write Mode With Flashback Technology And Revert Restore Point”

3,985 total views, 25 views today

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


Mar 28 2017

Oracle 11g R2 Data Guard Manual Switchover Steps

Category: Administration,Backup And RecoveryFatih Acar @ 09:47

You can use the same steps to switchover for single data guard or multiple data guard configuration.

Switchover operation will convert primary side to data guard and data guard to primary side.

You have to control listeners whether it is running before switchover steps.

Switchover Steps

Primary Side


SQL> alter system archive log current;

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

SQL> shutdown immediate;

SQL> startup mount;

Data Guard Side
Continue reading “Oracle 11g R2 Data Guard Manual Switchover Steps”

2,327 total views, 6 views today

Tags: Database Administration, Oracle Administration, Oracle Data Guard, Oracle Data Guard Switchover


Mar 27 2017

Oracle 11g R2 Multi Real Time Apply Data Guard Configuration Steps

Category: Administration,Backup And RecoveryFatih Acar @ 16:42

Oracle 11g R2 database supports multi standby database structure up to 30 standby databases. We can use remote destinations for real time apply standby structures with todays network technologies. If your remote destination network is not good, you can use archivelog apply for remote destination with some delay.

System informations are like below.

System Informations

PRIMARY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.129
SID : PRI
HOSTNAME : primary.localdomain

FIRST STANDBY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.130
SID : DR1
HOSTNAME : dr1.localdomain

SECOND STANDBY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.131
SID : DR2
HOSTNAME : dr2.localdomain
Continue reading “Oracle 11g R2 Multi Real Time Apply Data Guard Configuration Steps”

1,599 total views, 12 views today

Tags: Database Administration, Oracle Administration


Mar 21 2017

Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps

Category: Administration,Backup And RecoveryFatih Acar @ 16:32

Oracle 11g R2 database supports cascade standby database structure. If you are using RAC on primary side, your database version has to be least 11.2.0.2 to support cascade standby structure. You can use to reduce the load on your primary database with using cascade standby database structure.

Primary database redo is written to the standby redo log as it is received at a cascading standby database. The redo is not immediately cascaded however. It is cascaded after the standby redo log file that it was written to has been archived locally. A cascaded destination will therefore always have a greater redo transport lag, with respect to the primary database, than the cascading standby database.

Restrictions

  • Cascading Structure is not supported by Data Guard Broker.
  • To use Oracle RAC on primary side, you have to use least 11.2.0.2 verion.
  • Cascaded standby database do not support Real Time Apply

I will configure cascade standby database structure like below schema.

System Informations

PRIMARY DATABASE
VERSION : 11.2.0.3
IP : 192.168.9.129
SID : PRI
HOSTNAME : primary.localdomain

Continue reading “Oracle 11g R2 Cascade Standby Data Guard Installation and Configuration Steps”

1,541 total views, 4 views today

Tags: Database Administration, Oracle Administration


Mar 03 2017

Oracle 12c R2 Downloadable Now

Category: AdministrationFatih Acar @ 16:27

You can download Oracle 12c R2 database now. Only Linux x64 and Solaris Operation Systems supported for now.

You can download with below link;

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Also you can find new features about of Oracle 12c R2 with below links;

https://docs.oracle.com/database/122/

https://blogs.oracle.com/sql/entry/12_things_developers_will_love

1,535 total views, no views today

Tags: Oracle 12c, Oracle Administration


Feb 03 2017

Round Up A Number By Protecting 2 Decimal in Oracle

Category: SQLFatih Acar @ 12:04

I will show how to round up a number by protecting 2 decimal. When I use round or ceil function, I can not have correct result. I want to round a number upwards with calculate 2 decimal.

I want result like below examples.

1.2445 > 1.25
1.21 > 1.21
1.521 > 1.53
1.1 > 1.1

You can use trunc function with add 0.009 to number.

Examples

select trunc(1.224 + 0.009,2) from dual;
1.23
select trunct(1.16 + 0.009,2) from dual;
1.16
select trunct(1.521 + 0.009,2) from dual;
1.53

2,810 total views, no views today

Tags: Oracle Administration, Oracle SQL Query, SQL


Dec 08 2016

Update Another Column On The Same Table With Trigger While Updating Table in Oracle

Category: Errors and Solutions,Procedure,SQLFatih Acar @ 11:01

If you want to do update another column when execute an update on table, you can use before update trigger. If you use after update trigger, you can get some errors. if you use pragma autonomous_transaction parameter as declare in trigger you can get errors like that are “ORA-00060: deadlock detected while waiting for resource”, “ORA-06512: at “trigger_name” line 7″,”ORA-04088:error during execution of trigger “trigger_name”” if you don’t use, you can get errors like that are “ORA-04091: table tablename is mutating, trigger/function may not see it”,”ORA-06512: at trigger_name”, line 6″,”ORA-04088: error during execution of trigger trigger_name”

You can resolve this problem with using before update trigger. You can update another column of updating the same table.

Trigger Example

My example is about that If status column of table1 change from 0 to 1, I will write another columns that are name, surname and studentno at the same table table1.

create table table1 (id number, status number, name varchar2(100), surname varchar2(100), studentno varchar2(100),address varchar2(500),city varchar2(100));

create or replace trigger trg_trigger_name_upt
before update of status on table1
referencing OLD as old NEW as new
for each row
v_studentno varchar2(100);
begin

IF :old.status = 0 AND :new.status = 1 and UPDATING THEN

:new.name := ‘Fatih’;
:new.surname := ‘Acar’;
v_studentno := seq_studentno.nextval;
:new.studentno := v_studentno;

END IF;

end;
/

5,041 total views, no views today

Tags: Oracle Administration, Oracle Error Solutions, Oracle SQL Query


Next Page »