Perform Switchover and Failover with Oracle Data Guard Broker

This post guides you to perform Switchover and Failover with Oracle Data Guard Broker.

In this serries:

Switchover

Running this command on primary or standby:

dgmgrl sys/syspassword
DGMGRL> switchover to dg10
 Performing switchover NOW, please wait…

Result will be something like this:

DGMGRL> switchover to dg10
 Performing switchover NOW, please wait…
 New primary database "dg10" is opening…
 Operation requires shutdown of instance "DG9" on database "dg9"
 Shutting down instance "DG9"…
 ORA-01017: invalid username/password; logon denied
 Warning: You are no longer connected to ORACLE.
 Please complete the following steps to finish switchover:
         shut down instance "DG9" of database "dg9"
         start up instance "DG9" of database "dg9"

You may need to restart both dg10 – new active database and dg9 – new standby manually

sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;

Now login to dgmgrl and check

DGMGRL> show configuration;
 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg10 - Primary database
     dg9  - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS

DG10 now new primary database, DG9 became physical standby

DGMGRL> show database dg9
 Database - dg9
 Role:            PHYSICAL STANDBY
   Intended State:  APPLY-ON
   Transport Lag:   0 seconds
   Apply Lag:       0 seconds
   Real Time Query: ON
   Instance(s):
     DG9
 Database Status:
 SUCCESS

Check each database with transport lag and apply lag

DGMGRL> show database dg10
 Database - dg10
 Role:            PRIMARY
   Intended State:  TRANSPORT-ON
   Instance(s):
     DG9
 Database Status:
 SUCCESS

Check dg10 with TRANSPORT-ON

Now switch back

DGMGRL> switchover to dg9;
 Performing switchover NOW, please wait…

If you have some errors while switching, please refer to “Some typical errors” section at the end of this post.

Failover & Reinstate

Run this command on STANDBY because FAILOVER is in case your PRIMARY is damaged.

DGMGRL> FAILOVER to dg10;
 Performing failover NOW, please wait…

In just few seconds, its done. Check again

DGMGRL> FAILOVER to dg10;
 Performing failover NOW, please wait…
 Failover succeeded, new primary is "dg10"
 DGMGRL> show configuration
 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg10 - Primary database
     dg9  - Physical standby database (disabled)
       ORA-16661: the standby database needs to be reinstated
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS

Now if we got dg9 back without datafiles damaged -> just reinstate and add to dg10 the primary

If dg9 is back with datafiles damaged, then we need to restore the datafiles and then reinstate.

Restoring the datafiles is done from RMAN with some commands like this:

rman target /
STARTUP MOUNT;
RESTORE DATAFILE 2;
SELECT FLASHBACK_ON FROM V$DATABASE;
EXIT

Reinstate

Reinstate can be understand as re-register the database to the data guard configuration, so, you need to prepare database restored as above. Using those commands:

DGMGRL> reinstate database dg9;
 Reinstating database "dg9", please wait…

You will have something like this:

DGMGRL> reinstate database dg9;
 Reinstating database "dg9", please wait…
 Operation requires shutdown of instance "DG9" on database "dg9"
 Shutting down instance "DG9"…
 ORA-01017: invalid username/password; logon denied
 Warning: You are no longer connected to ORACLE.
 Please complete the following steps and reissue the REINSTATE command:
         shut down instance "DG9" of database "dg9"
         start up and mount instance "DG9" of database "dg9"

Now you need to restart mount database DG9 manually, you will have result like:

SQL> startup mount;
 ORACLE instance started.
 Total System Global Area 1603411968 bytes
 Fixed Size                  2213776 bytes
 Variable Size             436209776 bytes
 Database Buffers         1157627904 bytes
 Redo Buffers                7360512 bytes
 Database mounted.

Now run the reinstate again at the DG10 – PRIMARY database:

DGMGRL> reinstate database dg9
 Reinstating database "dg9", please wait…

Note: You may have to do it twice (reinstate and restart mount the database).

Finally you will have some successful screens:

DGMGRL> reinstate database dg9
 Reinstating database "dg9", please wait…
 Reinstatement of database "dg9" succeeded

Now check the configuration:

DGMGRL> show configuration
 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg10 - Primary database
     dg9  - Physical standby database
       Error: ORA-16766: Redo Apply is stopped
 Fast-Start Failover: DISABLED
 Configuration Status:
 ERROR

Now dg9 is back but with some errors. Check the log we have

The log at:

tail -200f /u01/app/oracle/diag/rdbms/dg9/DG9/trace/alert_DG9.log

Result is:

Additional information: 3
 Recovery Slave PR00 previously exited with exception 38701
 Errors in file /u01/app/oracle/diag/rdbms/dg9/DG9/trace/DG9_mrp0_16080.trc:
 ORA-38701: Flashback database log 18 seq 65 thread 1: "/u01/app/oracle/flash_recovery_area/DG9/flashback/o1_mf_gwo46ctq_.flb"
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory

Please check at the Some Typical Errors section below for ORA-27037 & ORA-38701.

Some Typical Errors & Solutions

ORA-16610: command “Broker automatic health check” in progress and DGM-17017: unable to determine configuration status

Solution: You need to restart primary and physical standby database manually.

DGMGRL> show configuration;
 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg9  - Primary database
     dg10 - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 ORA-16610: command "Broker automatic health check" in progress
 DGM-17017: unable to determine configuration status

ORA-16778, ORA-16810 solution

Solution: You may need for a while and retry, the databases is preparing for synchronization.

DGMGRL> show configuration
 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg9  - Primary database
       Error: ORA-16778: redo transport error for one or more databases
 dg10 - Physical standby database   
Error: ORA-16810: multiple errors or warnings detected for the database
 Fast-Start Failover: DISABLED
 Configuration Status:
 ERROR

ORA-38760: This database instance failed to turn on flashback database and Slave exiting with ORA-38760 exception

Slave exiting with ORA-38760 exception is a general error which means you can relay on this error and need to find more specific error

Same with ORA-38760. In my case I got more detail error: Error: ORA-16766: Redo Apply is stopped

Error: ORA-16766: Redo Apply is stopped

Solution:

This message mean there was something to you standby database. Check the log at:

tail -200f /u01/app/oracle/diag/rdbms/dg10/DG9/trace/alert_DG9.log

You will see something like:

RFS[6]: Assigned to RFS process 44261
 RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 11634
 RFS[6]: Selected log 8 for thread 1 sequence 159 dbid 1381157600 branch 1023448483
 Fri Nov 15 12:30:14 2019
 Archived Log entry 168 added for thread 1 sequence 159 ID 0x5261d062 dest 1:
 Fri Nov 15 12:30:17 2019
 Media Recovery Log /home/data/flash_recovery_area/DG10/archivelog/2019_11_15/o1_mf_1_159_gwwfz6o4_.arc
 Media Recovery Waiting for thread 1 sequence 160 (in transit)
 Recovery of Online Redo Log: Thread 1 Group 7 Seq 160 Reading mem 0
   Mem# 0: /u01/app/oracle/oradata/DG9/standby_redo01.log
 Fri Nov 15 12:30:26 2019
 RFS[7]: Assigned to RFS process 44263
 RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 11648
 Errors in file /u01/app/oracle/diag/rdbms/dg10/DG9/trace/DG9_rfs_44263.trc:
 ORA-16401: archive log rejected by Remote File Server (RFS)

Now do crosscheck archivelog and full backup on PRIMARY:

rman target /
RMAN> crosscheck archivelog all;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

If you have huge datafiles and archivelogs, please consider delete them first using:

DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';

Now copy all the backed up and archivelog to STANDBY:

scp -r oracle@dg9:/home/data/flash_recovery_area/DG9/archivelog/ /home/data/flash_recovery_area/DG9

scp -r oracle@dg9:/home/data/flash_recovery_area/DG9/backupset/ /home/data/flash_recovery_area/DG9

Next, crosscheck archivelog and backup on STANDBY

rman target /
RMAN> crosscheck archivelog all;
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Now stop/start STANDBY database manually and retry with DGMGRL.

ORA-27037 & ORA-38701 Solution

 ORA-38701: Flashback database log 18 seq 65 thread 1: "/u01/app/oracle/flash_recovery_area/DG9/flashback/o1_mf_gwo46ctq_.flb"
 ORA-27037: unable to obtain file status
 

These errors happen when you have some trouble with your flashback even if you restore point list is empty:

RMAN> list restore point all;
 using target database control file instead of recovery catalog
 SCN              RSP Time  Type       Time      Name
 
 RMAN>

Solution:

Do exactly as follow:

  1. Shutdown the database immediate
  2. startup mount
  3. turn off flashback: alter database flashback off;
  4. Open the database (read only if on standby)
  5. shutdown the database immediate (again)
  6. startup mount
  7. Turn on flashback (alter database flashback on)
  8. Open the database (read only if on standby)
  9. Check dgmgrl again

How to shutdown Oracle database server with Oracle data guard properly

This tutorial shows you how to shutdown Oracle database server with Oracle Data Guard running on it properly.

In this serries:

Stop transport and apply process first

Using broker. Run on standby or primary.

DGMGRL> edit database dg10 set state=apply-off;
DGMGRL> edit database dg9 set state=transport-off;

Stop listeners

Run this command on both servers:

[oracle@dg10 admin]$ lsnrctl stop
 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 05-NOV-2019 13:30:46
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg10.localdomain)(PORT=1521)))
 The command completed successfully

Shutdown database immediate

Shutdown the databases

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

Runs on both primary and standby.

Install Oracle Data Guard on Oracle Database 11GR2 Steps by Steps (physical standby)

This is my post to guide you install and configure Oracle Data Guard with Physical Standby on Oracle Database 11G R2 steps by steps.

In this serries:

Our topology

Assume that we have 2 server, we will install Oracle Data Guard on them. The primary server will be DG9. The standby server will be DG10.

Primary DG9 -------------------> Physical Standby DG10
172.16.23.41                     172.16.23.42
Oracle Linux 6.10                Oracle Linux 6.10
Oracle Database 11GR2            Oracle Database 11GR2

Setup Primary Server

Step 1: Install the database

Install primary server as normal with Oracle Linux 6.10 operating system and Oracle database 11G R2 onto that os.

Now you have a complete database with:

  • IP: 172.16.23.41
  • ORACLE_UNQNAME=DG9
  • ORACLE_SID=DG9

Replace or create you listener.ora file at /u01/app/oracle/product/11.2.0.1/db_1/network/admin with the following:

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dg9.localdomain)(PORT = 1521))
     )
   )
 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = DG9_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)
       (SID_NAME = DG9)
     )
   )
 ADR_BASE_LISTENER = /u01/app/oracle

You should backup your original listener.ora file before doing.

Step 2: Enable archivelog mode

Run this command to check if the database if in archivelog mode or not:

 SELECT log_mode FROM v$database; 

Switch to archivelog mode if not using:

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE ARCHIVELOG; 
ALTER DATABASE OPEN; 

Enable forced logging

 ALTER DATABASE FORCE LOGGING; 

Step3: Setup paramters

Set LOG_ARCHIVE_CONFIG

Check DB_NAME and DB_UNIQUE_NAME:

SQL> show parameter _name;
 NAME                                 TYPE        VALUE
 
 db_file_name_convert                 string
 db_name                              string      DG9
 db_unique_name                       string      DG9
 global_names                         boolean     FALSE
 instance_name                        string      DG9
 lock_name_space                      string
 log_file_name_convert                string
 service_names                        string      DG9

Note that the primary and standby must have the same DB_NAME but different DB_UNIQUE_NAME.

In this case, they will have DB_NAME=DG9 and DB_UNIQUE_NAME=DG9 and DG10 simultaneously. We have to set LOG_ARCHIVE_CONFIG using DB_UNIQUE_NAME.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG9,DG10)'; 

Set remote log destinations

Here we’ll use FRA (Fast Recovery Area) but you can use any other location if needed.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

The SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.

Set LOG_ARCHIVE_FORMAT, LOG_ARCHIVE_MAX_PROCESSES and REMOTE_LOGIN_PASSWORDFILE

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; 

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30; 

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; 

Set FAL_SERVER, STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET FAL_SERVER=DG10;
 
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DG10','DG9' SCOPE=SPFILE;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

Edit tnsnames.ora

Edit /etc/hosts file to:

172.16.23.41 dg9.localdomain dg9
172.16.23.42 dg10.localdomain dg10

Edit tnsnames.ora at $ORACLE_HOME/network/admin/tnsnames.ora:

DG9 =   
  (DESCRIPTION =     
    (ADDRESS_LIST =       
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg9)(PORT = 1521))     
    )     
    (CONNECT_DATA =  (SERVICE_NAME = DG9)     
  )   
) 


DG10 =   
  (DESCRIPTION =     
    (ADDRESS_LIST =       
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg10)(PORT = 1521)
    )     
  )     
  (CONNECT_DATA =       (SERVICE_NAME = DG10)     
  )   
)

Backup primary database

$ rman target=/ 
RMAN> BACKUP DATABASE PLUS ARCHIVELOG; 

Create standby controlfile and pfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/dg10.ctl'; 

CREATE PFILE='/tmp/initDG10.ora' FROM SPFILE; 

Edit the newly created PFILE to:

*.db_unique_name='DG10' 
*.fal_server='DG9' 
*.log_archive_dest_2='SERVICE=DG9 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG9' 

That’s all for now you have to do with primary. Now we need to setup physical standby DG10.

Setup Standby Server

All steps below run on Standby server.

Install physical standby server with Oracle Linux 6.10 operating system described here.

Perform all Step 1 to Step 5 in this tutorial to configure Oracle database with following parameter:

  • IP: 172.16.23.42
  • /etc/hosts: 172.16.23.42 dg10.localdomain dg10
  • /etc/hostname: dg10
  • ORACLE_UNQNAME=DG10
  • ORACLE_SID=DG9

Step 6: Run Installer and select options

At first step, if you have MOS (My Oracle Support) then fill in. If you don’t, uncheck and press next.

oraerr.com-install-oracle-data-guard-on-oracle-11g
oraerr.com-install-oracle-data-guard-on-oracle-11g

Select install database software only.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3

Choose Single Instance

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5

Choose Enterprise Edition

oraerr.com-install-oracle-database-on-ol6.10-setup-02
oraerr.com-install-oracle-database-on-ol6.10-setup-02

Choose location

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10

Choose Ignore All and Next/Finish. Now waiting for Oracle to install software on your server.

oraerr.com-install-oracle-data-guard-on-oracle-11g-02
oraerr.com-install-oracle-data-guard-on-oracle-11g-02

Run the scripts and finish the installation as normal. As installed software only, you’ll have an idle instance (mounted by not open):

$ sqlplus / as sysdba
 SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 4 14:46:59 2019
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 Connected to an idle instance.
 SQL>

Step 7: Standby server configuration

Create or replace your listerner.ora with following:

LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dg10.localdomain)(PORT = 1521))
     )
   )
 SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = DG10_DGMGRL)
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0.1/db_1)
       (SID_NAME = DG9)
     )
   )
 ADR_BASE_LISTENER = /u01/app/oracle

Edit the /etc/hosts file to:

172.16.23.41 dg9.localdomain dg9
172.16.23.42 dg10.localdomain dg10 

Create necessary folders on standby

$ mkdir -p /u01/app/oracle/oradata/DG9 
$ mkdir -p /u01/app/oracle/flash_recovery_area/DG9 
$ mkdir -p /u01/app/oracle/admin/DG9/adump 

Copy controlfile file from primary to standby:

scp oracle@dg9:/tmp/dg10.ctl /u01/app/oracle/oradata/DG9/control01.ctl 

cp /u01/app/oracle/oradata/DG9/control01.ctl /u01/app/oracle/flash_recovery_area/DG9/control02.ctl 

Copy archivelogs and backup

scp -r oracle@dg9:/u01/app/oracle/flash_recovery_area/DG9/archivelog/ /u01/app/oracle/flash_recovery_area/DG9

scp -r oracle@dg9:/u01/app/oracle/flash_recovery_area/DG9/backupset/ /u01/app/oracle/flash_recovery_area/DG9

Copy remote login passsword file

scp oracle@dg9:$ORACLE_HOME/dbs/orapwDG9 $ORACLE_HOME/dbs 

Copy pfile

scp oracle@dg9:/tmp/initDG10.ora /tmp/initDG10.ora 

Start listener

lsnrctl start

Restore backup on standby

Set if ORACLE_SID not set yet

export ORACLE_SID=DB11G 

Create SPFILE from PFILE

CREATE SPFILE FROM PFILE='/tmp/initDG10.ora'; 

Restore backup files

rman target /
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create online redo logs on standby (should be the same with primary, by default, Oracle database creates 3 redo log file (redo01.log, …) and 50MB each.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo01.log') SIZE 50M; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo02.log') SIZE 50M; 

ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DG9/online_redo03.log') SIZE 50M; 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

Create standby redo log

Run on BOTH primary and standby server

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo01.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo02.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo03.log') SIZE 50M; 

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DG9/standby_redo04.log') SIZE 50M; 

The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs.

Step 8: Start apply process

Background redo apply. Control is returned to the session once the apply process is started. Run on STANDBY server.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

If you need to cancel apply process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

After you start the apply process, the primary database will continously backup to standby. The standby database is mounted but not opened.

Common errors during startup apply process

ORA-01153: an incompatible media recovery is active

How to fix:

Cancel and re-apply the process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 Database altered.
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 
 Database altered.

ORA-16826: apply service state is inconsistent with the DelayMins property

How to fix:

This Problem can only happen if Managed Recovery is started manually which should not be done at all if there is an active Data Guard Broker configuration.

Typically the Data Guard Broker should start Managed Recovery—>should not apply process manually if there’s broker.

Solution is cancel the manual apply and re-enable via broker.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 Database altered.
SQL> ALTER SYSTEM SET dg_broker_start=true;
 System altered.

DGMGRL> edit database dg10 set state=apply-on;
 Succeeded.

Enable broker

Run this command to enable the broker

ALTER SYSTEM SET dg_broker_start=true;

Now you can access DGMGRL with following command:

$ dgmgrl sys/Password1@dg9

Create configuration

DGMGRL> CREATE CONFIGURATION mydgconfig AS PRIMARY DATABASE IS DG9 CONNECT IDENTIFIER IS DG9; 

Add standby database (run on PRIMARY):

DGMGRL> ADD DATABASE DG10 AS CONNECT IDENTIFIER IS DG10 MAINTAINED AS PHYSICAL; 

Enable the configuration

DGMGRL> ENABLE CONFIGURATION; 

Check the sync with show configuration command

 DGMGRL> SHOW CONFIGURATION;

 Configuration - mydgconfig
 Protection Mode: MaxPerformance
   Databases:
     dg9  - Primary database
     dg10 - Physical standby database
 Fast-Start Failover: DISABLED
 Configuration Status:
 SUCCESS

Step 9: Final check and configures

Check the protection mode, by default is Maximum performance.

SELECT protection_mode FROM v$database; 
  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.

Protection mode switching

 -- Maximum Availability. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; 

-- Maximum Performance. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG10 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 

-- Maximum Protection. 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DG100 AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DG10'; 

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; 
ALTER DATABASE OPEN; 

Oracle Data Guard Database Switchover

Switch primary to standby

 -- Convert primary database to standby 
CONNECT / AS SYSDBA 
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY; 

-- Shutdown primary database 
SHUTDOWN IMMEDIATE; 

-- Mount old primary database as standby database 
STARTUP NOMOUNT; 
ALTER DATABASE MOUNT STANDBY DATABASE; 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 

Switch standby to primary

 -- Convert standby database to primary 
CONNECT / AS SYSDBA 
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 

-- Shutdown standby database 
SHUTDOWN IMMEDIATE; 

-- Open old standby database as primary 
STARTUP; 

Oracle Data Guard Failover

if the primary server down.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; 
ALTER DATABASE ACTIVATE STANDBY DATABASE; 

Oracle: Create table with examples

This tutorial helps you to create table in Oracle.

Oracle table creating script examples

With tablespace and storage declared.

CREATE TABLE oraerr_users 
( 
    usrno NUMBER(5) PRIMARY KEY, 
    uname VARCHAR2(15) NOT NULL, 
    job VARCHAR2(10), 
    mgr NUMBER(5), 
    regdate DATE DEFAULT (sysdate), 
    photo BLOB, 
    sal NUMBER(7,2), 
    clickrate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080), 
    comm NUMBER(7,2), 
    refno NUMBER(3) NOT NULL CONSTRAINT admin_dept_fkey REFERENCES oraerr_departments (department_id)
) 
TABLESPACE oraerr_tbs 
STORAGE ( INITIAL 50K); 

COMMENT ON TABLE oraerr_users IS 'Oraerr.com user table'; 

Note on that script:

  • BLOB is a part of LOBs, LOBs are used to store semi-structured data (such as an XML tree) and unstructured data (such as the stream of bits in a color image).
  • clickrate is a virtual column, this column computes the employee’s hourly rate as the yearly salary divided by 2,080
  • COMMENT statement is used to store a comment for the table
  • refno is a FOREIGN KEY which references to ORAERR_DEPARTMENTS’s department_id column.
  • You have to create tablespace ORAERR_TBS before executing this script.
  • If no tablespace statement found, Oracle will use default schema’s tablespace for the tables.

Create partition for existing tables in Oracle: complete example

Here is a complete script to create/replace partition for existing table in Oracle database. You just need to modify your table names and run.

We have a TBL_CUSTOMERS table in schema named SCHEMANAME as script:

CREATE TABLE SCHEMANAME.TBL_CUSTOMERS
 (
   ID                 NUMBER(38)                 NOT NULL,
   CUS_NAME           VARCHAR2(255 BYTE),
   CUS_PHONE          VARCHAR2(50 BYTE),
   CUS_ADDRESS        VARCHAR2(255 BYTE),
   CUS_PROVINCE_CODE  CHAR(3 BYTE),
   CUS_PROVINCE_NAME  VARCHAR2(255 BYTE),
   CUS_DISTRICT_CODE  CHAR(5 BYTE),
   CUS_ACCOUNT_ID     NUMBER(38)                 NOT NULL,
   CREATED_DATE       DATE                       DEFAULT SYSDATE,
   CUS_WARDS_CODE     VARCHAR2(10 BYTE),
   CUS_WARDS_NAME     VARCHAR2(255 BYTE)
 )

This table as PRIMARY KEY named ID :

CREATE UNIQUE INDEX TBL_CUSTOMERS_PK ON  TBL_CUSTOMERS (ID);

ALTER TABLE  TBL_CUSTOMERS ADD (
CONSTRAINT TBL_CUSTOMERS_PK PRIMARY KEY (ID) 
USING INDEX TBL_CUSTOMERS_PK ENABLE VALIDATE);

This table already has some data, now we’ll need to create partition for this table. Just using this script below:

--create a temp table from original
CREATE TABLE SCHEMANAME.TBL_CUSTOMERS_TEMP 
 PARTITION BY RANGE(CREATED_DATE)
 (
 PARTITION P201906 VALUES LESS THAN (TO_DATE('20190701', 'YYYYMMDD')),
 PARTITION P201907 VALUES LESS THAN (TO_DATE('20190801', 'YYYYMMDD')), 
 PARTITION P201908 VALUES LESS THAN (TO_DATE('20190901', 'YYYYMMDD')), 
 PARTITION P201909 VALUES LESS THAN (TO_DATE('20191001', 'YYYYMMDD')), 
 PARTITION P201910 VALUES LESS THAN (TO_DATE('20191101', 'YYYYMMDD')), 
 PARTITION P201911 VALUES LESS THAN (TO_DATE('20191201', 'YYYYMMDD')), 
 PARTITION P201912 VALUES LESS THAN (TO_DATE('20200101', 'YYYYMMDD')) 
 ) 
 AS SELECT * FROM  SCHEMANAME.TBL_CUSTOMERS;

--create index for temp table (oraerr.com)
CREATE UNIQUE INDEX  SCHEMANAME.TBL_CUSTOMERS_PK_NEW ON  SCHEMANAME.TBL_CUSTOMERS_TEMP (ID);

--create PK on that index. No schemaname at CONSTRAINT
ALTER TABLE  SCHEMANAME.TBL_CUSTOMERS_TEMP ADD (CONSTRAINT  TBL_CUSTOMERS_PK_NEW  PRIMARY KEY (ID) USING INDEX  SCHEMANAME.TBL_CUSTOMERS_PK_NEW ENABLE VALIDATE);

--rename original table. No schemaname at new name
 ALTER TABLE SCHEMANAME.TBL_CUSTOMERS RENAME TO TBL_CUSTOMERS_OLD;

--rename newly created table. We specify the Schema name here.
ALTER TABLE SCHEMANAME.TBL_CUSTOMERS_TEMP RENAME TO TBL_CUSTOMERS;

And done.

We have new table with partition and remains PK as original. Here I added SCHEMANAME for you to do this from SYS (other user than table’s user)

All you have to do is to replace:

  • the SCHEMANAME with your SCHEMA NAME,
  • TBL_CUSTOMERS with your table name,
  • CREATED_DATE with your column of partitioning,
  • and ID with your table’s primary key or index.
  • Add more partitions if needed

You can drop TBL_CUSTOMERS_OLD from now because we have TBL_CUSTOMERS ourselves.

How to configure X11 forwarding to install Oracle databases

This tutorial helps you configure X11 forwarding properly to install Oracle databases via remote terminal from Windows.

In this series:

If you are using Putty from Windows then first you need to install XMing – an X-Server that starts on top of your desktop. Download Xming and install it. Download PuTTY (if using Windows) and save it on your hard disk.

Start XMing as default.

Start PuTTY. In the PuTTY Configuration section, on the left panel, select Connection → SSH → X11. On the right panel, click on the Enable X11 forwarding checkbox. Set the X display location as :0.0. Enter the hostname or IP address in the Host Name textbox.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config

Now login as oracle user, run the installer. Please remember to run as oracle user and run the install file.

cd database
./runInstaller

You’ll have your X-application runs on your Windows via X11 forwarding configured.

Install Oracle Linux 5,6,7 from USB with ISO file

These steps can also applied to install Oracle Linux 5,6, 7 from USB with ISO. Here, I take the Oracle Linux 6 to demonstrate but you can do the same with other versions.

In this serries:

Step 1: Download official Oracle Linux ISO from eDelivery:

Select as follow:

  1. Choose Release
  2. Type “Oracle Linux” and press Search button
  3. Press Add to cart your Oracle Linux version
oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-01
oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-01

Now press “Check out” then you will see something like:

oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-05
oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-05

Now press Continue, Accept the terms and conditions and press exactly at the link pointed below. For Oracle Linux 7.4 and for version Oracle Linux 6.9, it looks like this:

oraerr.com-install-oracle-linux-7.4-using-usb-download-iso
oraerr.com-install-oracle-linux-7.4-using-usb-download-iso

You will have an ZIP file which has an ISO file inside or an ISO file already. Extract the ZIP file if you got ZIP download. Filename may be different in your case.

Create OL6 installation USB from ISO

  • Download Rufus: https://rufus.ie/ and run Rufus to create installation USB
  • In some cases, you have error from Rufus like: This image is either non-bootable, or it uses a boot or compression method that is not supported by Rufus, please use Universal USB Installer.
  • Another good solution is Unetbootin, all of them are reliable. If you got Not support message from Rufus and UUI, you can try Unetbootin. It depends on your ISO file and your Oracle Linux.
  • After finish setting up the USB, copy file .iso to the root of the USB (yes, the source ISO file that you’ve burned to USB). This step helps you pass the Missing ISO 9660 Image error while installing Oracle Linux 6 from USB.
oraerr.com-install-oracle-linux-6-using-usb-02
oraerr.com-install-oracle-linux-6-using-usb-02

Step 2: Install Oracle Linux 6 via USB

Now insert the USB, set your server boot from USB in Legacy/BIOS mode (not UEFI) and Setup screen appears. Depends on your server, for DELL R420, press F11 to select boot mode at startup.

Just Next for simple steps on setup screen. Here I only note important ones.

oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-02-1
oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-02-1

Choose Use All Space to use whole disk space for Oracle database.

If you didn’t copy the ISO file in step 1, you’ll get Missing ISO 9660 Image error.

oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-04
oraerr.com-install-oracle-linux-6-from-usb-with-iso-file-04

In this step, choose Desktops on the left, choose all below items on the right to install a normal GNOME desktop (with GUI):

  • Desktop
  • Desktop Platform
  • Fonts
  • General Purpose Desktop
  • Graphical Administration Tools
  • X Windows System

If you don’t select as above -> you’ll have console GUI only.

After all, you just need to press REBOOT button to finish the setup process.

Install Oracle 11g R2 Database On Oracle Linux 6 with EM (Enterprise Manager)

Just go through these steps, you will have Oracle 11GR2 database up and running on Oracle Linux 6.10 with Enterprise Manager (EM)

Note on Oracle version support: Starting with Oracle Database 11g Release 2 (11.2.0.3), Oracle Linux 6 and Red Hat Enterprise Linux 6 are supported on Linux x86 and Linux x86-64. … Starting with Oracle Database 11g Release 2 (11.2.0.4), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64. (link here)

In this serries:

Step 1: Install basic packages

Please make sure that you set the DNS Servers to 8.8.8.8 or sometimes you will get error like this will executing yum command:

# yum -y update
 Loaded plugins: refresh-packagekit, security, ulninfo
 Setting up Update Process
 https://yum.oracle.com/repo/OracleLinux/OL6/UEKR4/x86_64/repodata/repomd.xml: [Errno 14] PYCURL ERROR 6 - "Couldn't resolve host 'yum.oracle.com'"
 Trying other mirror.
 Error: Cannot retrieve repository metadata (repomd.xml) for repository: public_ol6_UEKR4. Please verify its path and try again

To fix [Errno 14] PYCURL ERROR 6 – “Couldn’t resolve host ‘yum.oracle.com’ simply check and set your DNS nameserver to 8.8.8.8 and restart server’s network.

Run below commands as root user:

sudo yum install -y wget net-tools telnet curl wget gcc binutils  compat-libstdc++-33 compat-libstdc++-33.i686 gcc-c++ glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libaio libaio-0.3.105 libaio.i686 libaio-devel libaio-devel.i686 libXext libXext.i686 libXtst libXtst.i686 libX11 libX11.i686 libXau libXau.i686 libxcb libxcb.i686 libXi libXi.i686 unixODBC unixODBC-devel zlib-devel elfutils-libelf-devel sysstat make htop ntpdate 

Then:

yum -y update 

Step 2: Download the Oracle database software

You have 2 options to download:

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-download
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-download

After finish downloading you will have 2 or more ZIP files. Unzip all of them to a single directory called database with unzip command.

unzip linux.x64_11gR2_database_1of2.zip 
unzip linux.x64_11gR2_database_2of2.zip 

Step 3: Edit Host file

Edit /etc/hosts file

172.16.23.41 dg9.localdomain dg9

Edit /etc/hostname

dg9.localdomain

Install Oracle Prerequisites

yum install -y oracle-rdbms-server-11gR2-preinstall && yum update -y

Check if the “/etc/sysctl.conf” file is something like this (the number may be differrent because we already run preinstall above. Just a check step:

fs.aio-max-nr = 1048576 
fs.file-max = 6815744 
kernel.shmall = 2097152 
kernel.shmmax = 536870912 
kernel.shmmni = 4096 
# semaphores: semmsl, semmns, semopm, semmni 
kernel.sem = 250 32000 100 128 
net.ipv4.ip_local_port_range = 9000 65500 
net.core.rmem_default=262144 
net.core.rmem_max=4194304 
net.core.wmem_default=262144 
net.core.wmem_max=1048586 

Step 4: Additional Setup

Edit /etc/selinux/config file:

SELINUX=permissive

Then run the command

setenforce Permissive

Stop and disable FirewallD

service iptables status
service iptables stop
chkconfig iptables off

Create installation directory

mkdir -p /u01/app/oracle/product/11.2.0.1/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Add these lines into the end of /home/oracle/.bash_profile file

# Oracle Settings 
TMP=/tmp; 
export TMP TMPDIR=$TMP; 
export TMPDIR ORACLE_HOSTNAME=dg9.localdomain; export ORACLE_HOSTNAME 
ORACLE_UNQNAME=DG9; export ORACLE_UNQNAME 
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME ORACLE_SID=DG9; export ORACLE_SID 
ORACLE_TERM=xterm; export ORACLE_TERM 
PATH=/usr/sbin:$PATH; export PATH 
PATH=$ORACLE_HOME/bin:$PATH; export PATH 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH 

Step 5: Start the installation

Install XMing and Putty configuration for Windows

If you are using Putty from Windows then first you need to install XMing – an X-Server that starts on top of your desktop. Download Xming and install it. Download PuTTY (if using Windows) and save it on your hard disk.

Start XMing as default.

Start PuTTY. In the PuTTY Configuration section, on the left panel, select Connection → SSH → X11. On the right panel, click on the Enable X11 forwarding checkbox. Set the X display location as :0.0. Enter the hostname or IP address in the Host Name textbox.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config

Now login as oracle user, run the installer. Please remember to run as oracle user and run the install file.

cd database
./runInstaller

You’ll have your X-application runs on your Windows via X11 forwarding configure.

Note: I have a separated post about X11 forwarding configuration here.

Step 6: Some important options while installing

At first step, if you have MOS (My Oracle Support) then fill in. If you don’t, uncheck and press next.

oraerr.com-install-oracle-database-on-ol6.10-setup
oraerr.com-install-oracle-database-on-ol6.10-setup

Next screen

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-1
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-1

Choose “Create and configure a database”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-2
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-2

Choose “Server class”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3

Then “Single Instance database installation” as we’re not using RAC.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-4
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-4

Choose “Advanced install”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5

Choose “Enterprise Edition” depends on your license. No image here means just press NEXT.

oraerr.com-install-oracle-database-on-ol6.10-setup-02
oraerr.com-install-oracle-database-on-ol6.10-setup-02

Leave Oracle Base & Software location default as we set above. Same with Inventory Directory next screen. Choose General Purpose/Transaction Processing on the next screen.

oraerr.com-install-oracle-database-on-ol6.10-setup-03
oraerr.com-install-oracle-database-on-ol6.10-setup-03

Fill db name and SID

It is a very important step. Please remember to use CAPITALIZED words as both 2 names = DG9 (in this case). Because your automatically created spfile will be spfile[SID].ora -> if you stay with uncapitalized words -> file not found at database startup -> need to specify spfile to load manually.

Your EM Configuration file will be:

$ORACLE_HOME/[DB_UNIQUENAME]_[Captialized SID]/

Your spfile will be:

$ORACLE_HOME/dbs/spfile[captitalized SID]

Your OC4J configuration will be:

$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_[DB_UNIQUENAME]_[Capitalized SID]

So, CAPITALIZED both SID & DATABASE NAME + Set both equal to DG9 (same value)

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-7
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-7

Configure Memory. Set it to about 70-80% your total Available Memory.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-8
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-8

Character sets

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-9
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-9

Set the password

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10

In the last window, you may need to choose Ignore all checkbox and press Next. In my experience, those library will not affect too much in the operation of the database. Press Finish and wait for Oracle database to be installed on the machine.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-11
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-11

Step 7: Fixing some errors (if any) and finalize installation

Fix invoking “ins_emagent.mk” error

While installing, you will receive some error like error while invoking ins_emagent.mk then edit “$ORACLE_HOME/sysman/lib/ins_emagent.mk” file, change

$(MK_EMAGENT_NMECTL) 

to:

$(MK_EMAGENT_NMECTL) -lnnz11

Fix invoking “ctx/lib/ins_ctx.mk” error

If you get error while invoking “ctx/lib/ins_ctx.mk“, create this fix.sh file as root:

# Fix ctx/lib/ins_ctx.mk
 
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
 
cat << __EOF__ > /tmp/memcpy_wrap.c
#include <stddef.h>
#include <string.h>
 
asm (".symver wrap_memcpy, memcpy@GLIBC_2.14");
void *wrap_memcpy(void *dest, const void *src, size_t n) {
return memcpy(dest, src, n);
}
__EOF__
 
if [[ -e "${ORACLE_HOME}/ctx/lib/ins_ctx.mk" ]]; then
sed -i -e 's/\$(INSO_LINK)/\$(INSO_LINK) -Wl,--wrap=memcpy_wrap \$(ORACLE_HOME)\/ctx\/lib\/memcpy_wrap.o/g' ${ORACLE_HOME}/ctx/lib/ins_ctx.mk
gcc -c /tmp/memcpy_wrap.c -o ${ORACLE_HOME}/ctx/lib/memcpy_wrap.o && rm /tmp/memcpy_wrap.c
fi

Now run this sh file and press RETRY button.

Fix ORA-00845: MEMORY_TARGET not supported on this system

You need to reduce the MEMORY parameter setting.

Fix ORA-19502: write error on file “…” and ORA-27072: File I/O error

You need to free more space on disk. Not enough disk space left for Oracle install. If you don’t see those errors, then congratulation.

Run the script to finalize the install.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-12
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-12

Login to server as root user and run the scripts above. You will have something like this:

[root@dg9 ~]# /u01/app/oraInventory/orainstRoot.sh
 Changing permissions of /u01/app/oraInventory.
 Adding read,write permissions for group.
 Removing read,write,execute permissions for world.
 Changing groupname of /u01/app/oraInventory to oracle.
 The execution of the script is complete.
 [root@dg9 ~]# /u01/app/oracle/product/11.2.0.1/db_1/root.sh
 Running Oracle 11g root.sh script…
 The following environment variables are set as:
     ORACLE_OWNER= oracle
     ORACLE_HOME=  /u01/app/oracle/product/11.2.0.1/db_1
 Enter the full pathname of the local bin directory: [/usr/local/bin]:
    Copying dbhome to /usr/local/bin …
    Copying oraenv to /usr/local/bin …
    Copying coraenv to /usr/local/bin …
 Creating /etc/oratab file…
 Entries will be added to the /etc/oratab file as needed by
 Database Configuration Assistant when a database is created
 Finished running generic part of root.sh script.
 Now product-specific root actions will be performed.
 Finished product-specific root actions.

When the successful dialog appears, press Close button to finish.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-13
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-13

Now you can access the EM control page from your browser.

https://172.16.23.41:1158/em

You may need to ignore SSL certificate error on your browser. EM will ask you for username/password to login the system. Enter your SYS/Password to login. You will have a webpage like this:

oraerr.com-install-oracle-database-on-ol6.10-setup-05
oraerr.com-install-oracle-database-on-ol6.10-setup-05

You may check/restart/start/stop the EM with those commands:

emctl status dbconsole
emctl start dbconsole
emctl stop dbconsole

Step 8: Schedule Oracle database to start automatically after reboot

Edit the file “/etc/oratab” as follow:

DG9:/u01/app/oracle/product/11.2.0.1/db_1:Y 

Install Oracle 11g R2 Database On Oracle Linux 7.7 with EM (Enterprise Manager)

Just go through these steps, you will have Oracle 11GR2 database up and running on Oracle Linux 7.7 with Enterprise Manager (EM)

Note on Oracle version support: Starting with Oracle Database 11g Release 2 (11.2.0.3), Oracle Linux 6 and Red Hat Enterprise Linux 6 are supported on Linux x86 and Linux x86-64. … Starting with Oracle Database 11g Release 2 (11.2.0.4), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64. (link here)

In this serries:

Step 1: Install basic packages

Run below command as root user:

sudo yum install -y wget net-tools telnet curl wget gcc htop ntpdate binutils  compat-libstdc++-33 compat-libstdc++-33.i686 gcc-c++ glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libaio libaio.i686 libaio-devel libaio-devel.i686 libXext libXext.i686 libXtst libXtst.i686 libX11 libX11.i686 libXau libXau.i686 libxcb libxcb.i686 libXi libXi.i686 unixODBC unixODBC-devel zlib-devel elfutils-libelf-devel sysstat make && yum -y update 

Step 2: Download the Oracle database software

You have 2 options to download:

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-download
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-download

After finish downloading you will have 2 or more ZIP files. Unzip all of them to a single directory called database with unzip command.

unzip linux.x64_11gR2_database_1of2.zip 
unzip linux.x64_11gR2_database_2of2.zip 

Step 3: Edit Host file

Edit /etc/hosts file

172.16.23.41 dg9.localdomain dg9

Edit /etc/hostname

dg9.localdomain

Install Oracle Prerequisites

yum install -y oracle-rdbms-server-11gR2-preinstall && yum update

Check if the “/etc/sysctl.conf” file is something like this (the number may be differrent because we already run preinstall above. Just a check step:

fs.aio-max-nr = 1048576 
fs.file-max = 6815744 
kernel.shmall = 2097152 
kernel.shmmax = 536870912 
kernel.shmmni = 4096 
# semaphores: semmsl, semmns, semopm, semmni 
kernel.sem = 250 32000 100 128 
net.ipv4.ip_local_port_range = 9000 65500 
net.core.rmem_default=262144 
net.core.rmem_max=4194304 
net.core.wmem_default=262144 
net.core.wmem_max=1048586 

Step 4: Additional Setup

Edit /etc/selinux/config

SELINUX=permissive

Then run the command

setenforce Permissive

Stop and disable FirewallD

systemctl stop firewalld
systemctl disable firewalld

Create installation directory

mkdir -p /u01/app/oracle/product/11.2.0.1/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Add these lines into /home/oracle/.bash_profile

# Oracle Settings 
TMP=/tmp; 
export TMP TMPDIR=$TMP; 
export TMPDIR ORACLE_HOSTNAME=dg9.localdomain; export ORACLE_HOSTNAME 
ORACLE_UNQNAME=DG9; export ORACLE_UNQNAME 
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME ORACLE_SID=DG9; export ORACLE_SID 
ORACLE_TERM=xterm; export ORACLE_TERM 
PATH=/usr/sbin:$PATH; export PATH 
PATH=$ORACLE_HOME/bin:$PATH; export PATH 
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH 

Step 5: Start the installation

Install XMing and Putty configuration for Windows

If you are using Putty from Windows then first you need to install XMing – an X-Server that starts on top of your desktop. Download Xming and install it. Download PuTTY (if using Windows) and save it on your hard disk.

Start XMing as default.

Start PuTTY. In the PuTTY Configuration section, on the left panel, select Connection → SSH → X11. On the right panel, click on the Enable X11 forwarding checkbox. Set the X display location as :0.0. Enter the hostname or IP address in the Host Name textbox.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-putty-config

Now login as oracle user, run the installer. Please remember to run as oracle user and run the install file.

cd database
./runInstaller

You’ll have your X-application runs on your Windows via X11 forwarding configure.

Step 6: Some important options while installing

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-1
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-1

Choose “Create and configure a database”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-2
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-2

Choose “Server class”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-3

Then “Single Instance database installation” as we’re not using RAC.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-4
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-4

Choose “Advanced install”

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-5

Choose “Enterprise Edition” depends on your license. No image here means just press NEXT.

oraerr.com-install-oracle-database-on-ol6.10-setup-03
oraerr.com-install-oracle-database-on-ol7.7-setup-03

Fill db name and SID

It is a very important step. Please remember to use CAPITALIZED words as both 2 names = DG9 (in this case). Because your automatically created spfile will be spfile[SID].ora -> if you stay with uncapitalized words -> file not found at database startup -> need to specify spfile to load manually.

Your EM Configuration file will be:

$ORACLE_HOME/[DB_UNIQUENAME]_[Captialized SID]/

Your spfile will be:

$ORACLE_HOME/dbs/spfile[captitalized SID]

Your OC4J configuration will be:

$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_[DB_UNIQUENAME]_[Capitalized SID]

So, CAPITALIZED both SID & DATABASE NAME + Set both equal to DG9 (same value)

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-7
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-7

Configure Memory

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-8
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-8

Character sets

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-9
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-9

Set the password

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-10

In the last window, you may need to choose Ignore all checkbox and press Next. In my experience, those library will not affect too much in the operation of the database. Press Finish and wait for Oracle database to be installed on the machine.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-11
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-11

Step 7: Fixing some errors and finalize installation

Fix invoking “ins_emagent.mk” error

While installing, you will receive some error like error while invoking ins_emagent.mk then edit “$ORACLE_HOME/sysman/lib/ins_emagent.mk” file, change

$(MK_EMAGENT_NMECTL) 

to:

$(MK_EMAGENT_NMECTL) -lnnz11

Fix invoking “ctx/lib/ins_ctx.mk” error

If you get error while invoking “ctx/lib/ins_ctx.mk“, create this fix.sh file as root:

# Fix ctx/lib/ins_ctx.mk
 
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
 
cat << __EOF__ > /tmp/memcpy_wrap.c
#include <stddef.h>
#include <string.h>
 
asm (".symver wrap_memcpy, memcpy@GLIBC_2.14");
void *wrap_memcpy(void *dest, const void *src, size_t n) {
return memcpy(dest, src, n);
}
__EOF__
 
if [[ -e "${ORACLE_HOME}/ctx/lib/ins_ctx.mk" ]]; then
sed -i -e 's/\$(INSO_LINK)/\$(INSO_LINK) -Wl,--wrap=memcpy_wrap \$(ORACLE_HOME)\/ctx\/lib\/memcpy_wrap.o/g' ${ORACLE_HOME}/ctx/lib/ins_ctx.mk
gcc -c /tmp/memcpy_wrap.c -o ${ORACLE_HOME}/ctx/lib/memcpy_wrap.o && rm /tmp/memcpy_wrap.c
fi

Now run this sh file and press RETRY button.

Fix ORA-00845: MEMORY_TARGET not supported on this system

You need to reduce the MEMORY parameter setting.

Fix ORA-19502: write error on file “…” and ORA-27072: File I/O error

You need to free more space on disk. Not enough disk space left for Oracle install.

Run the script to finalize the install.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-12
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-12

When the successful dialog appears, press Close button to finish.

oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-13
oraerr.com-install-oracle-database-11gr2-oracle-linux-7-options-13

Now you can access the EM control page from your browser.

https://172.16.23.41:1158/em

You may need to ignore SSL certificate error on your browser.

oraerr.com-install-oracle-database-on-ol6.10-setup-05
oraerr.com-install-oracle-database-on-ol6.10-setup-05

You may check/restart/start/stop the EM with those commands:

emctl status dbconsole
emctl start dbconsole
emctl stop dbconsole

Step 8: Schedule Oracle database to start automatically after reboot

Edit the file “/etc/oratab” as follow:

DG9:/u01/app/oracle/product/11.2.0.1/db_1:Y 

How to free Oracle FRA space properly

Oracle Flash Recovery Area is full will made your database stop working for normal user and produce ORA-19815 warnings, ORA-00257: archiver error. Connect internal only, until freed.

So, you need to free up more space for Oracle FRA.

First of all, NEVER try to delete archive logs or backups MANUALLY with system command like “rm -rf”. In some articles, they guide people to do that but please DON’T. You need to delete the archive logs or backups using RMAN. Follow below steps to do that properly.

Step 1: Check your configured DB_RECOVERY_DEST_SIZE and increase it if available

Note: you can ignore this step and go straight to step 2 if you don’t want/don’t have enough space to take for FRA.

Using these commands:

$ sqlplus / as sysdba
SQL> show parameter DB_RECOVERY_FILE_DEST;

The result will be something likes this:

NAME                                 TYPE        VALUE
 
 db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                  _area
 db_recovery_file_dest_size           big integer 1000G

Here you can see DB_RECOVERY_FILE_DEST_SIZE equals 1TB and located in DB_RECOVERY_FILE_DEST: /u01/app/oracle/flash_recovery.

If you have more space, increase this size with following command:

alter system set DB_RECOVERY_FILE_DEST_SIZE=1010G scope=both;

Now your database will have more 10G to operate until it’s all filled up.

Step 2: Delete old archivelogs and backups in FRA

Using these commands:

cd /u01/app/oracle/flash_recovery_area/DBNAME
du -sh ./*

The result will be something like this:

$ du -sh ./*
 426G    ./archivelog
 173G    ./backupset
 17M     ./control02.ctl
 29G     ./flashback
 0       ./onlinelog

So you can see, the archivelog folder has a very large file size. We need to clear the archivelog.

RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete noprompt expired backup;
RMAN> delete noprompt expired archivelog all;

If you see not too much of archive log deleted and you archive log file size is still too big. Then:

RMAN> backup as compressed backupset archivelog until time 'sysdate-365';
RMAN> delete noprompt archivelog until time 'sysdate-365' backed up 1 times to device type disk;
RMAN> backup as compressed backupset archivelog until time 'sysdate-100';
RMAN> delete noprompt archivelog until time 'sysdate-100' backed up 1 times to device type disk; 
RMAN> backup as compressed backupset archivelog until time 'sysdate-1';
RMAN> delete noprompt archivelog until time 'sysdate-1' backed up 1 times to device type disk; 

Please note: the “backup as compressed backupset archivelog all;” will take you a lot of time and disk space to complete if you have a big size archivelog. So you have to divide into smaller periods.

Those above commands will delete most of archive logs until yesterday therefore, you will free significant amount of space.

Now check the space again:

du -sh ./*
 640M    ./archivelog
 197G    ./backupset
 17M     ./control02.ctl
 29G     ./flashback
 0       ./onlinelog

You can see, we saved more than 200GB by deleting the archive log.