Oracle LogMiner Tips And Tricks

LOGMINER :-

  • Logminer is a utility provided with the Oracle database server which mines the redologs or Archivelogs and data dictionary information to build the SQL statements and keeps the  contents of the redolog file in the fixed view called ” V$logmnr_contents “.
  • LogMiner tool can help the DBA to the find changed records in redo log  files by using a set of  PL/SQL  procedures  and  functions .
  • Internally Oracle uses the Log Miner technology for several other features,such as Flashback Transaction Backout,Streams, and Logical Standby Databases .Most often  LogMiner is used for recovery purposes when the data consists of just a few tables or a single code  change .

Steps for Configuring Logminer :-

 In this Scenario,we are checking that Username who Dropped the Table using LogMiner utility.

Setting  parameter UTL_FILE_DIR :

Normally we set the UTL_FILE_DIR parameter where you need to create dictionary file.From 12.2,we need to create directory object.

[oracle@orcl:~ orcldemo] mkdir -p /oradb/logminer

SQL> alter system set utl_file_dir='/u01/logminer' scope=spfile;

System altered.

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

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1006633808 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

SQL> show parameter UTL_FILE_DIR;

NAME          TYPE        VALUE
------------- ----------- -------------
utl_file_dir  string      /u01/logminer

Create and grant acccess to directory :

SQL> CREATE OR REPLACE DIRECTORY LOG_DIR AS '/oradb/logminer';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY LOG_DIR TO hari;

Grant succeeded.

Enable Supplemental logging:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 

Database altered.

Build Logminer :

Creation of Dictionary file :

The dictionary file is used to translate this data into a more meaningful format. Specify a filename for the dictionary and a directory path name for the file. This procedure creates the dictionary file.

The dictionary file is created using the BUILD procedure in the DBMS_LOGMNR_D package.

SQL> BEGIN
sys.DBMS_LOGMNR_D.build (
dictionary_filename => 'lgmnrdict.ora',
dictionary_location => 'LOG_DIR');
END;
/

PL/SQL procedure successfully completed.
SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------------
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log
/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log

3 rows selected.

Adding logfiles to get analyzed :

Adding logfiles using NEW procedure first and add all logfiles using ADD_LOGFILE procedure.

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

Starting logminer process :-

SQL> BEGIN
-- Start using all logs
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora');
END;
/ 

PL/SQL procedure successfully completed.

Querying  v$logmnr_contents :-

When the LogMiner session ends then v$logmnr_contents is no more accessible .  Its always better to copy contents of v$logmnr_contents to a user table and then perform  the analysis as it is quite expensive to query v$logmnr_contents .

Now we can catch that user who dropped the table, the user is ‘HARI’ and also we can check who created the table.

SQL> select username,table_name,sql_redo from v$logmnr_contents where seg_name='SAN';

USERNAME   TABLE_NAME  SQL_REDO
---------- ----------- -------------------------------------
HARI       SAN     create table san(num number,name varchar2(10));

SYS        SAN    ALTER TABLE "HARI"."SAN" RENAME TO                                     "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;                                                                                                      

HARI       SAN    drop table hari.san AS  
                  "BIN$dsifZpmIOhHgU4NWqMCSjA==$0" ;  

3 rows selected.                 

 

Filtering Data by SCN:

To filter data by SCN (system change number), use the STARTSCN and ENDSCN parameters,

Demo :-

Check the current_scn before perform DML transaction

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------
2619410

Execute DML statements

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(1,100);

1 row created.

SQL> insert into emp values(2,200);

1 row created.

SQL> insert into emp values(3,300);

1 row created.

SQL> commit;

Commit complete.

Check the current_scn before perform DML transaction

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
--------------------

 2619497

Add list of logfiles to get analyzed

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/

PL/SQL procedure successfully completed.

Specify SCN range to start logminer process

SQL> begin
DBMS_LOGMNR.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
startscn => 2619410,
endscn => 2619497);
END;
/ 

PL/SQL procedure successfully completed.

Check the log information v$logmnr_contents

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION   STATUS   SQL_REDO
----------  ------   -----------------------------------------------
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                         ('1','100');

INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values                                               
                     ('2','200');
INSERT      0        insert into "HARI"."EMP"("EMPNO","SAL") values
                     ('3','300');
Filtering Data By Time  : 

 

To filter data by time, set the STARTTIME and ENDTIME parameters in the DBMS_LOGMNR.START_LOGMNR procedure.

Demo :-

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:13:13

Perform DML operations

SQL> conn hari/hari;
Connected.
SQL> insert into emp values(5,500);

1 row created.

SQL> delete emp where empno=1;

1 row deleted.

SQL> commit;

Commit complete.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
27-SEP-2018 03:15:00

Add list of logfiles to get analyzed

SQL> BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_3_fm2fw8lr_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_2_fm2fvl50_.log');

DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/oradb/app/oracle/oradata/ORCLDEMO/onlinelog/o1_mf_1_fm2fvl47_.log');

END;
/ 

PL/SQL procedure successfully completed.

Specify time range to start the logminer process

begin
dbms_logmnr.start_logmnr (
dictfilename => '/oradb/logminer/lgmnrdict.ora',
starttime => TO_DATE('27-SEP-2018 03:13:13', 'DD-MON-YYYY HH:MI:SS'),
endtime => TO_DATE('27-SEP-2018 03:15:00', 'DD-MON-YYYY HH:MI:SS'));
end;
/

PL/SQL procedure successfully completed.

Check  v$logmnr_contents 

SQL> SELECT OPERATION, STATUS, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'HARI' AND TABLE_NAME = 'EMP';

OPERATION  STATUS  SQL_REDO
---------- ------  --------------------------------------------------
INSERT     0       insert into "HARI"."EMP"("EMPNO","SAL") values   
                   ('5','500');                   

DELETE     0       delete from "HARI"."EMP" where "EMPNO" = '1' and
                   "SAL" = '100' and ROWID = 'AAASWiAACAAAAqYAAA';

Stopping logminer process:

Once the analysis is complete, logminer should be stopped using the END_LOGMNR procedure.

SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();

PL/SQL procedure successfully completed.
Accessing LogMiner Information : 
LogMiner information is contained in the following views. We can use SQL to query them as we would any other view.
V$LOGMNR_CONTENTS  :  Shows changes made to user and table information.
V$LOGMNR_DICTIONARY : Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information shown includes the database name and status information.
V$LOGMNR_LOGS  :  Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS : Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step-by-Step One Node Rac Applying Psu Patch on 12c Grid and DB Home

Description:-

As we already seen how to configure Oracle One node RAC in 12cR1 and the relocation of the instance from one node to another node. In this article, let us apply the July’18 PSU patch to the same environment.

For Oracle One Node RAC configuration, please click here. Below is the configuration of the environment.

High Level steps for applying the Patch:-

  • Current OPatch Version
  • Upgrade Opatch utility
  • Prepare for Patching
  • Applying Patch
  • Patch Verification

Current OPatch Version:-

Step 1:- Current version of Opatch Tool in our environment

$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.

From the above output,the opatch version is 12.1.0.1.3. But as per our README document, the minimum OPatch utility version shoul be 12.2.0.1.12 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which is available for download from My Oracle Support patch 6880880 by selecting the 12.2.0.1.0 release.

Upgrade Opatch utility:-

Step 2:- Backup the existing Opatch folder

Backup the OPatch directory as root user for GRID_HOME and oracle user for ORACLE_HOME(Database) in both the nodes of the cluster. Otherwise, if we try to backup as oracle user in GRID_HOME, we will receive permission issues.

GRID_HOME:
$ su - root
$ cd /oradb/app/12.1.0.2/grid/
$ mv OPatch/ OPatch_bkp
$ unzip <PATH_TO_PATCH>/p6880880_122010_Linux-x86-64.zip -d .
$ chown -R oracle:oinstall OPatch
$ chmod -R 755 OPatch

ORACLE_HOME:
$ su - oracle
$ cd /oradb/app/oracle/product/12.1.0.2/db_1
$ mv OPatch/ OPatch_bkp
$ unzip <PATH_TO_PATCH>/p6880880_122010_Linux-x86-64.zip -d .
$ chmod -R 755 OPatch

Now, as oracle user verify the OPatch utility version.

GRID_HOME:-(Both Nodes)

$ export ORACLE_HOME=/oradb/app/12.1.0.2/grid
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

ORACLE_HOME:-(Both Nodes)

$ export ORACLE_HOME=/oradb/app/oracle/product/12.1.0.2/db_1
$ export PATH=$ORACLE_HOME/OPatch:$PATH
$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

Prepare for Patching:-

Step 3:- Preparing Node 1 to apply the PSU Patch

Now, login as root user and set the environmental variables

Applying Patch:-

Step 4:- Navigate to the patch location and follow the below steps to apply the patch.

$ cd <PATH_TO_PATCH>
$ unzip p27967747_121020_Linux-x86-64.zip
$ cd 27967747
$ $ORACLE_HOME/OPatch/opatchauto apply ./

OPatchauto session is initiated at Wed Sep 26 02:39:52 2018

System initialization log file is /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2018-09-26_02-40-10AM.log.

Session log file is /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2018-09-26_02-41-29AM.log
The id for this session is WYWB

Executing OPatch prereq operations to verify patch applicability on home /oradb/app/12.1.0.2/grid

Executing OPatch prereq operations to verify patch applicability on home /oradb/app/oracle/product/12.1.0.2/db_1
Patch applicability verified successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Patch applicability verified successfully on home /oradb/app/12.1.0.2/grid

Verifying SQL patch applicability on home /oradb/app/oracle/product/12.1.0.2/db_1
SQL patch applicability verified successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Preparing to bring down database service on home /oradb/app/oracle/product/12.1.0.2/db_1

WARNING: The service ORCL.oracledbwr.com configured on orcl will not be switched as it is not configured to run on any other node(s).
Successfully prepared home /oradb/app/oracle/product/12.1.0.2/db_1 to bring down database service

Relocating RACOne home before patching on home /oradb/app/oracle/product/12.1.0.2/db_1
Relocated RACOne home before patching on home /oradb/app/oracle/product/12.1.0.2/db_1

Bringing down CRS service on home /oradb/app/12.1.0.2/grid
Prepatch operation log file location: /oradb/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_prodrac101_2018-09-26_02-49-04AM.log
CRS service brought down successfully on home /oradb/app/12.1.0.2/grid

Performing prepatch operation on home /oradb/app/oracle/product/12.1.0.2/db_1
Perpatch operation completed successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Start applying binary patch on home /oradb/app/oracle/product/12.1.0.2/db_1
Binary patch applied successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

Performing postpatch operation on home /oradb/app/oracle/product/12.1.0.2/db_1
Postpatch operation completed successfully on home /oradb/app/oracle/product/12.1.0.2/db_1


Start applying binary patch on home /oradb/app/12.1.0.2/grid
Binary patch applied successfully on home /oradb/app/12.1.0.2/grid

Starting CRS service on home /oradb/app/12.1.0.2/grid
Postpatch operation log file location: /oradb/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_prodrac101_2018-09-26_03-42-46AM.log
CRS service started successfully on home /oradb/app/12.1.0.2/grid

Relocating back RACOne to home /oradb/app/oracle/product/12.1.0.2/db_1
Relocated back RACOne home successfully to home /oradb/app/oracle/product/12.1.0.2/db_1


Preparing home /oradb/app/oracle/product/12.1.0.2/db_1 after database service restarted
No step execution required.........


Trying to apply SQL patch on home /oradb/app/oracle/product/12.1.0.2/db_1
SQL patch applied successfully on home /oradb/app/oracle/product/12.1.0.2/db_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:prodrac101
RAC Home:/oradb/app/oracle/product/12.1.0.2/db_1
Version:12.1.0.2.0
Summary:

==Following patches were SKIPPED:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/26983807
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762277
Reason: This patch is not applicable to this specified target type - "rac_database"


==Following patches were SUCCESSFULLY applied:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27547329
Log: /oradb/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_02-55-50AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762253
Log: /oradb/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_02-55-50AM_1.log


Host:prodrac101
CRS Home:/oradb/app/12.1.0.2/grid
Version:12.1.0.2.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/26983807
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27547329
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762253
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

Patch: /mnt/hgfs/shared/soft/12102/July18_PSU/27967747/27762277
Log: /oradb/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2018-09-26_03-08-36AM_1.log

OPatchauto session completed at Wed Sep 26 04:03:09 2018
Time taken to complete the session 83 minutes, 17 seconds

Patch Verification:-

Step 5:- Once the patch has been applied successfully, verify it in the database like below.

$ sqlplus / as sysdba
SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 27547329
Action : APPLY
Action Time : 26-SEP-2018 04:03:06
Description : DATABASE PATCH SET UPDATE 12.1.0.2.180717
Logfile :
/oradb/app/oracle/cfgtoollogs/sqlpatch/27547329/22280349/27547329_apply_ORCL_201
8Sep26_04_00_51.log
Status : SUCCESS

PL/SQL procedure successfully completed.

Similarly, follow the same steps to apply the patch in Node 2.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Step-by-Step Applying PSU Patch in Oracle 12c For Single-Instance

Description:-

In this article we are going to see Oracle12c-Step-by-Step Applying PSU Patch in Oracle 12c For Single-Instance

High Level steps for applying the Patch 

  • Check current version of Opatch Tool
  • Upgrade the Opatch utility
  • Take the backup of ORACLE_HOME
  • Shutdown the Database and Listener
  • Apply PSU patch on ORACLE_HOME
  • Execute Post installation Scripts (datapatch)
  • Startup the Database and Listener
  • Check the dba_registry_sqlpatch .

Let’s Start the Demo:-

Step 1:- Current version of Opatch Tool in our environment 

[oracle@orcl:~ orcldemo] export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@orcl:~ orcldemo] $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.6

OPatch succeeded.

From the above output,the opatch version is 12.2.0.1.6.You must use the OPatch utility version 12.2.0.1.12 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which is available for download from My Oracle Support patch 6880880 by selecting the 12.2.0.1.0 release.

Upgrade Opatch utility :-

Step 2:- Backup the existing Opatch folder

[oracle@orcl:~ orcldemo] cd $ORACLE_HOME
[oracle@orcl:db_1 orcldemo] mv OPatch/ OPatch_bkp

[oracle@orcl:~ orcldemo] ls -lrt
-rwxrw-rw- 1 oracle oinstall 271289497 Sep 21 18:52 p28163133_122010_Linux-x86-64.zip
-rwxrw-rw- 1 oracle oinstall 99183505 Sep 22 00:51 p6880880_122010_Linux-x86-64.zip

Step 3:- Unzip 6880880 patch

[oracle@orcl:~ orcldemo] unzip p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
Archive: p6880880_122010_Linux-x86-64.zip
creating: /oradb/app/oracle/product/12.2.0.1/db_1/OPatch/

Step 4:- Check the opatch lsinventory

[oracle@orcl:~ orcldemo] $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /oradb/app/oracle/product/12.2.0.1/db_1
Central Inventory : /oradb/app/oraInventory
from : /oradb/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.1.4
Log file location : /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2018-09-22_00-58-49AM_1.log

Lsinventory Output file location : /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-09-22_00-58-49AM.txt
-----------------------------------------------------------------------
Local Machine Information::
Hostname: orcl.localdomain.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
----------------------------------------------------------------------
OPatch succeeded.

Step 5:- Take Backup of ORACLE_HOME (Rollback plan)

[oracle@orcl:~ orcldemo]$ tar -cvf oracle_home_Sep22_2018.tar $ORACLE_HOME

Step 6:- Shutdown Standby Database and Listener

[oracle@orcl:~ orcldemo]  export ORACLE_SID=orcldemo

[oracle@orcl:~ orcldemo] sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 22 01:17:15 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@orcl:~ orcldemo] lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 22-SEP-2018 01:20:00

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 22-SEP-2018 01:19:45
Uptime 0 days 0 hr. 0 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DG4SQL" has 1 instance(s).
Instance "DG4SQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Step 7 :- Apply PSU patch on ORACLE_HOME.

[oracle@orcl:~ orcldemo] unzip p28163133_122010_Linux-x86-64.zip

[oracle@orcl:28163133 orcldemo] ls -lrt
total 88
drwxr-x--- 13 oracle oinstall  4096 Jul  6 20:35 files
drwxr-x---  3 oracle oinstall  4096 Jul  6 20:35 etc
-rw-r--r--  1 oracle oinstall    21 Jul  6 20:35 README.txt
-rw-rw-r--  1 oracle oinstall 74345 Jul 16 07:46 README.html
[oracle@orcl:28163133 orcldemo] export PATH=/oradb/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH

[oracle@orcl:28163133 orcldemo] opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /oradb/app/oracle/product/12.2.0.1/db_1
Central Inventory : /oradb/app/oraInventory
from : /oradb/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.1.4
Log file location : /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2018-09-22_01-26-15AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28163133

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oradb/app/oracle/product/12.2.0.1/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28163133' to OH '/oradb/app/oracle/product/12.2.0.1/db_1'
ApplySession: Optional component(s) [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.precomp.common, 12.2.0.1.0...
Patching component oracle.has.deconfig, 12.2.0.1.0...
Patching component oracle.tfa, 12.2.0.1.0...
Patching component oracle.assistants.server, 12.2.0.1.0...
Patching component oracle.rdbms.rman, 12.2.0.1.0...
Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...
Patching component oracle.rdbms, 12.2.0.1.0...
Patching component oracle.rdbms.deconfig, 12.2.0.1.0...
Patching component oracle.rdbms.util, 12.2.0.1.0...
Patching component oracle.ldap.rsf, 12.2.0.1.0...
Patching component oracle.ctx, 12.2.0.1.0...
Patching component oracle.rdbms.rsf, 12.2.0.1.0...
Patching component oracle.nlsrtl.rsf, 12.2.0.1.0...
Patching component oracle.rdbms.dv, 12.2.0.1.0...
Patching component oracle.rdbms.lbac, 12.2.0.1.0...
Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...
Patching component oracle.oracore.rsf, 12.2.0.1.0...
Patching component oracle.xdk.rsf, 12.2.0.1.0...
Patching component oracle.rdbms.oci, 12.2.0.1.0...
Patching component oracle.ctx.rsf, 12.2.0.1.0...
Patching component oracle.ldap.client, 12.2.0.1.0...
Patching component oracle.ons, 12.2.0.1.0...
Patching component oracle.xdk, 12.2.0.1.0...
Patching component oracle.xdk.parser.java, 12.2.0.1.0...
Patching component oracle.rdbms.crs, 12.2.0.1.0...
Patching component oracle.precomp.lang, 12.2.0.1.0...
Patching component oracle.network.rsf, 12.2.0.1.0...
Patching component oracle.ldap.rsf.ic, 12.2.0.1.0...
Patching component oracle.sdo, 12.2.0.1.0...
Patch 28163133 successfully applied.
Log file location: /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2018-09-22_01-26-15AM_1.log

OPatch succeeded.

Step 8:- Startup the Database and Listener

[oracle@orcl:28163133 orcldemo] sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 22 01:33:17 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 8621232 bytes
Variable Size 1006633808 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
[oracle@orcl:28163133 orcldemo] lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 22-SEP-2018 01:34:20

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Starting /oradb/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /oradb/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.localdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 22-SEP-2018 01:34:21
Uptime 0 days 0 hr. 13 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/orcl/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "DG4SQL" has 1 instance(s).
Instance "DG4SQL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcldemo.localdomain.com" has 1 instance(s).
Instance "orcldemo", status READY, has 1 handler(s) for this service...
Service "orcldemoXDB.localdomain.com" has 1 instance(s).
Instance "orcldemo", status READY, has 1 handler(s) for this service...
The command completed successfully

Step 9:- Execute post patch steps and run datapatch command

[oracle@orcl:OPatch orcldemo] ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sat Sep 22 01:38:48 2018
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /oradb/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21552_2018_09_22_01_38_48/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
ID 180717 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
Nothing to roll back
The following patches will be applied:
28163133 (DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717)

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...
Patch 28163133 apply: SUCCESS
logfile: /oradb/app/oracle/cfgtoollogs/sqlpatch/28163133/22313390/28163133_apply_ORCLDEMO_2018Sep22_01_39_13.log (no errors)
SQL Patching tool complete on Sat Sep 22 01:41:53 2018

Step 10 :- After applying PSU patch,Check the DBA_REGISTRY_SQLPATCH

Step 11:- Check opatch lsinventory  and list of patches applied in ORACLE_HOME

[oracle@orcl:OPatch orcldemo] /oradb/app/oracle/product/12.2.0.1/db_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /oradb/app/oracle/product/12.2.0.1/db_1
Central Inventory : /oradb/app/oraInventory
from : /oradb/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.1.4
Log file location : /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2018-09-22_01-50-06AM_1.log

Lsinventory Output file location : /oradb/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/lsinv/lsinventory2018-09-22_01-50-06AM.txt

-----------------------------------------------------------------------
Local Machine Information::
Hostname: orcl.localdomain.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c 12.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 28163133 : applied on Sat Sep 22 01:30:43 IST 2018
Unique Patch ID: 22313390
Patch description: "Database Jul 2018 Release Update : 12.2.0.1.180717 (28163133)"
Created on 6 Jul 2018, 08:05:03 hrs PST8PDT
Bugs fixed:
8480838, 13554903, 14690846, 15931756, 16002385, 16727454, 16942578
17027695, 17533661, 17947871, 18308268, 18521691, 18594510, 18774543
19072655, 19211433, 19285025, 19327292, 19526548, 19614243, 19647894
19649997, 19721304, 20003668, 20087519, 20118035, 20324049, 20532077
20591151, 20620169, 20736227, 20756305, 20866970, 20976443, 21143725
21147908, 21159907, 21178363, 21186167, 21216226, 21320338, 21433452
21479706, 21520266, 21547051, 21981529, 21985256, 22007324, 22070853
22072543, 22087683, 22104866, 22179537, 22347493, 22364044, 22367053
22379010, 22446455, 22495673, 22503283, 22503297, 22504793, 22564336
22568728, 22581771, 22594071, 22599050, 22628825, 22645009, 22654475
22700845, 22729345, 22826067, 22843979, 22845846, 22864303, 22898198
22950945, 22970869, 22981722, 23019710, 23026585, 23035249, 23055900
23061453, 23065002, 23066146, 23080557, 23105538, 23125560, 23126545
23127945, 23151677, 23179662, 23184263, 23197730, 23234232, 23249829
23271203, 23300142, 23310101, 23312077, 23481673, 23491861, 23499160
23521523, 23527363, 23548817, 23572982, 23581777, 23588722, 23599216
23600861, 23602213, 23645516, 23665623, 23709062, 23715460, 23730961
23733981, 23735292, 23741944, 23746128, 23749454, 24010030, 24289874
24294174, 24303148, 24307571, 24308349, 24326444, 24326846, 24332831
24334708, 24336249, 24337882, 24341675, 24343905, 24345420, 24346821
24348685, 24350620, 24368004, 24373756, 24374976, 24376875, 24376878
24385983, 24401351, 24403922, 24415926, 24421668, 24423416, 24425056
24425998, 24435982, 24437162, 24443539, 24457597, 24461826, 24468470
24470606, 24473736, 24485034, 24485161, 24485174, 24486059, 24486237
24509056, 24534401, 24554533, 24555417, 24556967, 24560906, 24563422
24570598, 24573817, 24578718, 24578797, 24589081, 24589590, 24593740
24595699, 24600330, 24609592, 24609996, 24616637, 24617969, 24623975
24624166, 24642495, 24654629, 24655717, 24664211, 24668398, 24674197
24674955, 24676172, 24677696, 24680959, 24689376, 24692973, 24693290
24699619, 24710696, 24713381, 24714096, 24717183, 24717859, 24719799
24735430, 24737064, 24737403, 24737581, 24744383, 24744686, 24757934
24759556, 24760407, 24766309, 24786669, 24792678, 24793511, 24796092
24797119, 24800423, 24801152, 24802934, 24811725, 24812047, 24827228
24827654, 24831514, 24835919, 24843188, 24844549, 24845157, 24848746
24848923, 24850622, 24907917, 24908321, 24911709, 24912588, 24922704
24923080, 24923215, 24923338, 24923790, 24929210, 24938784, 24940060
24942749, 24953434, 24960044, 24966788, 24968162, 24976007, 24978100
25027852, 25029022, 25029423, 25034396, 25036474, 25044977, 25045228
25050160, 25051628, 25057811, 25058080, 25062592, 25063971, 25065563
25072986, 25078611, 25086233, 25087436, 25093872, 25098160, 25099339
25099497, 25099758, 25100063, 25100579, 25103996, 25107662, 25110233
25120284, 25121089, 25123585, 25124363, 25129925, 25140197, 25145163
25145215, 25150925, 25159176, 25162645, 25164293, 25166187, 25171084
25175723, 25176408, 25178032, 25178101, 25178179, 25179774, 25182817
25184555, 25186079, 25191872, 25192044, 25192729, 25199585, 25201454
25202355, 25203656, 25206864, 25207410, 25209912, 25210268, 25210499
25211628, 25223839, 25224242, 25225795, 25226665, 25227381, 25230945
25237577, 25240590, 25241448, 25241625, 25244807, 25248384, 25251648
25257085, 25259611, 25262869, 25263960, 25265499, 25287072, 25296876
25299227, 25305405, 25307368, 25313154, 25313411, 25316758, 25317989
25320555, 25328518, 25329664, 25335249, 25335360, 25335790, 25337332
25337640, 25348956, 25353983, 25357142, 25382812, 25383204, 25384462
25386748, 25388896, 25392535, 25395696, 25397936, 25405813, 25410017
25410180, 25410802, 25410877, 25411036, 25417050, 25417056, 25417958
25425451, 25425760, 25427662, 25429959, 25430120, 25433696, 25437699
25440818, 25444961, 25451531, 25455795, 25457409, 25459958, 25462714
25463844, 25472112, 25476149, 25478885, 25489342, 25489367, 25489607
25492379, 25498930, 25498994, 25516250, 25524955, 25528838, 25530080
25530814, 25536819, 25537470, 25539063, 25540738, 25546580, 25546608
25547901, 25551676, 25553616, 25554787, 25555252, 25557886, 25558986
25561296, 25569149, 25570929, 25575348, 25575628, 25579458, 25579761
25594901, 25597525, 25598473, 25600342, 25600421, 25602488, 25603923
25606091, 25607726, 25612095, 25614866, 25616268, 25616359, 25616417
25616645, 25631933, 25633101, 25634317, 25634348, 25635149, 25638456
25639019, 25643818, 25643931, 25646373, 25647325, 25648731, 25653109
25654459, 25654936, 25655390, 25655966, 25659655, 25660847, 25661819
25662088, 25662101, 25662524, 25669791, 25672640, 25674386, 25680221
25685152, 25687460, 25691904, 25694206, 25695903, 25700654, 25710420
25715167, 25717371, 25722055, 25722608, 25722720, 25728085, 25729507
25736747, 25739065, 25754606, 25757748, 25760195, 25764020, 25766822
25768681, 25772669, 25774077, 25775213, 25784002, 25785331, 25785441
25788879, 25789041, 25789277, 25789579, 25790353, 25797092, 25797124
25803545, 25807997, 25813931, 25822410, 25823754, 25825910, 25826740
25830492, 25832935, 25834581, 25838361, 25852885, 25856821, 25858672
25861398, 25865785, 25870579, 25871177, 25871639, 25871753, 25872127
25874050, 25874678, 25885148, 25888073, 25890056, 25895224, 25897615
25904273, 25904490, 25906117, 25911724, 25914276, 25919622, 25932524
25941836, 25943271, 25945130, 25947799, 25953857, 25954022, 25954054
25957038, 25963024, 25964954, 25967544, 25967985, 25970731, 25973152
25975723, 25977302, 25980605, 25980770, 25981498, 25982666, 25990907
25995938, 26006257, 26019148, 26024732, 26025681, 26029780, 26032573
26036748, 26037215, 26038086, 26039623, 26040483, 26045732, 26078437
26080410, 26083298, 26088426, 26088836, 26090767, 26091640, 26091786
26095327, 26095405, 26096382, 26108080, 26110632, 26111842, 26121990
26138085, 26149904, 26153977, 26169341, 26169345, 26170715, 26176002
26187943, 26189861, 26198757, 26198926, 26201113, 26223039, 26237431
26237773, 26242031, 26243698, 26244115, 26245237, 26249718, 26256131
26259265, 26261327, 26263328, 26263721, 26271001, 26308650, 26324769
26327624, 26330994, 26331743, 26333141, 26351334, 26353617, 26358670
26362821, 26366517, 26367012, 26374791, 26375250, 26380097, 26385189
26388538, 26396790, 26399626, 26412540, 26418088, 26420561, 26421667
26426526, 26430737, 26434999, 26435073, 26436168, 26438612, 26440749
26442308, 26444601, 26444887, 26446098, 26452606, 26475419, 26476244
26478970, 26479173, 26486365, 26492866, 26493289, 26498354, 26513709
26522439, 26523432, 26526726, 26537307, 26542135, 26544823, 26545688
26546070, 26546664, 26546754, 26548363, 26556014, 26569225, 26575788
26582460, 26584641, 26597140, 26599395, 26608137, 26609942, 26615291
26615690, 26623652, 26626879, 26629381, 26633355, 26635897, 26637273
26637824, 26639167, 26641610, 26650226, 26658759, 26659182, 26680105
26712331, 26714910, 26729494, 26729611, 26740700, 26744595, 26751106
26751171, 26758193, 26764561, 26765212, 26775602, 26784509, 26794786
26797591, 26802503, 26820076, 26822620, 26840654, 26849779, 26875822
26896659, 26898563, 26907327, 26908788, 26909100, 26911000, 26939314
26944190, 26967713, 26969321, 26970717, 26981902, 26983259, 26992964
27009164, 27034890, 27044297, 27052607, 27060167, 27060859, 27073314
27079140, 27087426, 27090765, 27110878, 27117822, 27119621, 27124624
27125872, 27133662, 27135993, 27138325, 27142373, 27153641, 27161071
27162405, 27163928, 27165231, 27169796, 27181537, 27199245, 27207110
27213224, 27229389, 27244337, 27250547, 27274536, 27285244, 27292213
27304410, 27305039, 27314206, 27314390, 27329612, 27333106, 27334316
27338912, 27338946, 27345231, 27346709, 27348081, 27349393, 27367194
27370965, 27375542, 27394703, 27395416, 27396624, 27396813, 27400598
27434193, 27439835, 27441326, 27442041, 27501373, 27501413, 27502420
27504770, 27508985, 27510959, 27534509, 27544973, 27548131, 27558861
27560602, 27595973, 27611612, 27613080, 27687880, 27688036, 27688692
27709046, 27748954, 27799032, 27847259, 27882176, 27959048, 27997875
28033429, 28040776, 28099662, 28140658, 28184554, 28188330, 28174827
-----------------------------------------------------------------------
OPatch succeeded.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Oracle12c-RAC One Node Switchover

In the previous article we have configured Oracle 12cR1 One Node RAC. Whereas here let us do some playful activities using the configured environment.Description:-

As I said already we have Oracle 12cR1 One Node RAC database configured in Nodes prodrac101 & prodrac102. Due to OS maintenance activity, we are in need to stop the oracle services in Node 1 and relocate them to Node 2 to reduce the downtime of the database and make sure the business continuity.

Let’s start the demo

Below is the database configuration output.

$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /oradb/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL/PARAMETERFILE/spfile.278.985981865
Password file: +DBWR_DATA/ORCL/PASSWORD/pwdorcl.276.985981257
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCLPOOL
Disk Groups: DBWR_FRA,DBWR_DATA
Mount point paths:
Services: ORCL.oracledbwr.com
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: ORCL
Candidate servers:
OSDBA group: dba
OSOPER group: oper
Database instances:
Database is policy managed

Note-down the serverpool name of the database is configured. Let us verify the instance is running on which node.

$ srvctl status database -d ORCL
Instance ORCL_1 is running on node prodrac101
Online relocation: INACTIVE

From the above output we can see that the instance is running in the first node. So, we will relocate the instance from Node 1(prodrac101) to Node 2(prodrac102).

Before we start the relocate process make sure the serverpool’s are configured properly. Say for example, below is configuration of serverpool in our environment.

$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: ORCLPOOL
Importance: 0, Min: 0, Max: 1
Category: hub
Candidate server names:

As we already know that our instance is running under “ORCLPOOL” serverpool from database configuration. In the above output we can see that the Max value of the serverpool is 1 and we need to change it value, otherwise the relocation process will get failed as below.

$ srvctl relocate database -d ORCL -n prodrac102 -w 5 -v
Online relocation failed, rolling back to original state
PRCD-1222 : Online relocation of database "ORCL" failed but database was restored to its original state
PRCR-1114 : Failed to relocate servers prodrac102 into server pool ora.ORCLPOOL
CRS-2598: Server pool 'ora.ORCLPOOL' is already at its maximum size of '1'

In order to avoid the above error, we need to increase the max value of the serverpool as below.

$ srvctl modify srvpool -g ORCLPOOL -l 1 -u 3 -i 999

Once the max value is increased, verify the configuration now.

$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: ORCLPOOL
Importance: 999, Min: 1, Max: 3
Category: hub
Candidate server names:

Now, we can start the relocation process.

$ srvctl relocate database -d ORCL -n prodrac102 -w 5 -v
Configuration updated to two instances
Instance ORCL_2 started
Services relocated
Waiting for up to 5 minutes for instance ORCL_1 to stop ...
Instance ORCL_1 stopped
Configuration updated to one instance

Now, verify the database configuration and on which node the instance is running.

$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /oradb/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL/PARAMETERFILE/spfile.278.985981865
Password file: +DBWR_DATA/ORCL/PASSWORD/pwdorcl.276.985981257
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCLPOOL
Disk Groups: DBWR_FRA,DBWR_DATA
Mount point paths:
Services: ORCL.oracledbwr.com
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: ORCL
Candidate servers:
OSDBA group: dba
OSOPER group: oper
Database instances:
Database is policy managed
$ srvctl status database -d ORCL
Instance ORCL_2 is running on node prodrac102
Online relocation: INACTIVE

Now, we are sure that the instance has been relocated from Node 1 (prodrac101) to Node 2 (prodrac102).

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle Materialized View Tips And Tricks

  1. A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data.
  2. A materialized view can query tables, views, and other materialized views.
  3. A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.

Syntax:-

CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT …;

Methods :-

BUILD :-

  • IMMEDIATE : The materialized view is populated immediately.This option is default one.
  • DEFERRED : The materialized view is populated on the first requested refresh.

Refresh types :

  • FAST : A fast refresh is attempted only there is a change in base table. If materialized view logs are not present against the source tables in advance, the creation fails.To maintain the history of change in the base table ,it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in source database in same schema as master table.Refresh fast will perform refresh according to the changes occurred in master table.
  • COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

  • ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
  • ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

Grant privileges to the user:-

GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.

GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.

Creating Database link to point remote database :-

SQL> conn india/india;
Connected.

CREATE DATABASE LINK TEST_LINK CONNECT TO HARI IDENTIFIED BY hari USING 'orcl';

COMPLETE refresh:-

Creating Materialized View

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS SELECT * FROM hari.dept@TEST_LINK;

Materialized view created.

Table data present in remote database:

SQL> select * from dept;

DEPTNO     DNAME              LOCATION
---------- ------------    ------------
10         ACCOUNTING           PAK
30         SALES                RUSSIA
70         PRODUCTION           ENG
75         prod                 engg
80         dev                  arts


SQL> insert into hari.dept values(100,'uat','tech');
1 row created.

SQL> commit;
Commit complete.

SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;

ROWID		       DEPTNO DNAME	       LOCATION
------------------ ---------- -------------    ---------
AAAW1/AAGAAAADbAAE	   10 ACCOUNTING	  PAK
AAAW1/AAGAAAADbAAF	   30 SALES		  RUSSIA
AAAW1/AAGAAAADbAAG	   70 PRODUCTION	  ENG
AAAW1/AAGAAAADbAAH	   75 prod		  engg
AAAW1/AAGAAAADbAAI	   80 dev		  arts

Manually using complete refresh using the DBMS_VIEW package.

SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

ROWID changed after refreshing MV.

SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;

ROWID              DEPTNO     DNAME                LOCATION
------------------ ---------- -------------------- ---------
AAAW1/AAGAAAADbAAA  10        ACCOUNTING           PAK
AAAW1/AAGAAAADbAAB  30        SALES                RUSSIA
AAAW1/AAGAAAADbAAC  70        PRODUCTION           ENG
AAAW1/AAGAAAADbAAD  75        prod                 engg
AAAW1/AAGAAAADbAAJ  80        dev                  arts
AAAW1/AAGAAAADbAAK  100       uat                  tech

6 rows selected.

ON COMMIT option is used to refresh the materialized view when the base table DEPT data gets committed.So we don’t need to manually run the DBMS_VIEW  package.

DEFERRED :-

Initially drop the existing materialized view,

SQL> drop MATERIALIZED VIEW dept_mv;

Materialized view dropped.


SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;

no rows selected

Materialized view creation using DEFERRED option :-

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS SELECT * FROM hari.dept@TEST_LINK;

Materialized view created.


SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;

ROWID              DEPTNO     DNAME                LOCATION
------------------ ---------- -------------------- ---------
AAAW2DAAGAAAADbAAA 10         ACCOUNTING           PAK
AAAW2DAAGAAAADbAAB 30         SALES                RUSSIA
AAAW2DAAGAAAADbAAC 70         PRODUCTION           ENG
AAAW2DAAGAAAADbAAD 75         prod                 engg
AAAW2DAAGAAAADbAAE 80         dev                  arts
AAAW2DAAGAAAADbAAF 100        uat                  tech

6 rows selected.

FAST refresh :-

Initially drop the existing materialized view,

SQL> drop MATERIALIZED VIEW dept_mv;

 Materialized view dropped.

Materialized view creation using FAST refresh :-

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH FAST with PRIMARY KEY AS SELECT * FROM hari.dept@TEST_LINK;
CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH FAST with PRIMARY KEY AS SELECT * FROM hari.dept@TEST_LINK
*
ERROR at line 1:
ORA-23413: table "HARI"."DEPT" does not have a materialized view log

Check  the primary key constraint to create MV log with primary key.

SQL> select distinct constraint_type from user_constraints where table_name='DEPT';

C
-
P

Materialized view log creation :-

  • A materialized view log is a schema object that records changes to a master table’s data so that a materialized view defined on the master table can be refreshed incrementally.
  • It  has to be created where the base table DEPT is present.
SQL> create materialized view log on dept with primary key;

Materialized view log created.

SQL> desc MLOG$_DEPT;
Name              Null?  Type
---------------   ------ -----------
DEPTNO                   NUMBER
SNAPTIME$$               DATE
DMLTYPE$$                VARCHAR2(1)
OLD_NEW$$                VARCHAR2(1)
CHANGE_VECTOR$$          RAW(255)
XID$$                    NUMBER

SQL> select * from MLOG$_DEPT;

no rows selected

Check the base table DEPT data and perform DML operation

SQL> select * from dept;

DEPTNO     DNAME                LOCATION
---------- -------------------- --------------------
10         ACCOUNTING           PAK
30         SALES                RUSSIA
70         PRODUCTION           ENG
75         prod                 engg
80         dev                  arts
100        uat                  tech
111        hat                  det

7 rows selected.

SQL> update dept set DNAME='upg' where DNAME='hat';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept;

DEPTNO     DNAME                LOCATION
---------- -------------------- --------------------
10         ACCOUNTING           PAK
30         SALES                RUSSIA
70         PRODUCTION           ENG
75         prod                 engg
80         dev                  arts
100        uat                  tech
111        upg                  det

SQL> select * from MLOG$_DEPT;

DEPTNO SNAPTIME$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$  XID$$
------- --------- --------- --------- --------------- ----------
111     19-SEP-18  U        U         04               2.5334E+15
75      19-SEP-18  D        O         00               1.6889E+15

For FAST refresh option,the table will not recreated all data only the datas changed will get updated.It will get information from MV log table and perform the refresh.

Manually run materialized view as FAST refresh

execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'F' );

PL/SQL procedure successfully completed.

SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;

ROWID              DEPTNO     DNAME		LOCATION
------------------ ---------- --------------    ----------
AAAW2HAAGAAAADbAAA  10        ACCOUNTING	   PAK
AAAW2HAAGAAAADbAAB  30        SALES		   RUSSIA
AAAW2HAAGAAAADbAAC  70        PRODUCTION	   ENG
AAAW2HAAGAAAADbAAE  80        dev		   arts
AAAW2HAAGAAAADbAAF  100       uat		   tech
AAAW2HAAGAAAADbAAA  111       upg		   det

6 rows selected.

No ROWID gets changed except for the update DML operation.SO i will not create entire new result set using new ROWID like refresh complete.

SQL> select * from MLOG$_DEPT;

no rows selected.

Once MV is got fast refreshed then entries mentioned in MV log will be removed.

FORCE refresh :-

First it will try to do FAST refresh.If MV view log is corrupted or not available,then it fail to refresh FAST and do complete refresh.

execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'FORCE' );

Scheduling MV Refresh :-

Create a procedure for MV refresh

SQL> CREATE OR REPLACE PROCEDURE refresh_mv_dept
  AS
   BEGIN
       DBMS_MVIEW.REFRESH('MV_DEPT');
    END;
    /

Procedure created.

Schedule DBMS_SCHEDULER job daily to refresh materialized view.

SQL> BEGIN
  2      DBMS_SCHEDULER.CREATE_JOB
  3      (
  4      job_name            => 'MY_MVIEW_REFRESH',
  5      job_type            => 'STORED_PROCEDURE',
  6      job_action          => 'REFRESH_MV_DEPT',
  7      number_of_arguments => 0,
  8      start_date          => SYSTIMESTAMP,
  9      repeat_interval     => 'FREQ=DAILY;',
10      end_date            => NULL,
11      enabled             => TRUE,
12      auto_drop           => FALSE,
13      comments            => 'This job refresh MV_DEPT every day'
14      );
15  END;
16  /

PL/SQL procedure successfully completed.

Enable MV Refresh:-

SQL> exec DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',TRUE);

PL/SQL procedure successfully completed.

Disable MV Refresh:-

SQL> exec DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',FALSE);

PL/SQL procedure successfully completed.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Install of Oracle12c RAC One Node On OEL 6.5 Using VMware

Description:-

In this article let us configure Oracle 12cR1 One Node RAC. Below is the server details we are going to configure.

High Level Steps:-
1) Pre-requisites for RAC Installation
2) SSH Configuration and running runcluvfy
3) Grid Infrastructure Installation
4) Database Binaries Installation
5) One Node RAC Database Creation

Pre-requisites for RAC Installation:-

Below is the ip-details of the public, private, virtual and scan configuration.

$ cat /etc/hosts

127.0.0.1      localhost.localdomain       localhost

#Public IP
192.168.1.211  prodrac101.oracledbwr.com   prodrac101
192.168.1.212  prodrac102.oracledbwr.com   prodrac102

#Private IP
192.168.2.211  prodprv101.oracledbwr.com   prodprv101
192.168.2.212  prodprv102.oracledbwr.com   prodprv102

#Virtual IP
192.168.1.214  prodvip101.oracledbwr.com   prodvip101
192.168.1.215  prodvip102.oracledbwr.com   prodvip102

#Scan IP
192.168.1.218  prodscn101.oracledbwr.com  prodscn101
192.168.1.219  prodscn101.oracledbwr.com  prodscn101
192.168.1.220  prodscn101.oracledbwr.com  prodscn101

The pre-requisites steps involved in One Node RAC installation is similar to normal two node RAC installation. You can refer here for the OS configuration and pre-requisites need to be done for One Node RAC installation(Follow upto Step 79 for OS configuration and pre-requisites).

SSH Configuration and running runcluvfy:-

Login into NODE1/NODE2 as oracle:

$ cd <path-to-grid-software>/sshsetup
$ ./sshUserSetup.sh -user oracle -hosts "prodrac101 prodrac102" -noPromptPassphrase

Run the above command in both the servers of the cluster we are going to configure One Node RAC.

Once the ssh is configured successfully, execute the runcluvfy to check whether all the pre-requisite for RAC installation has been done perfectly.

$ cd <path-to-grid-software>/
$ ./runcluvfy.sh stage -pre crsinst -n prodrac101,prodrac102 -r 12cR1 -orainv oinstall -fixup -verbose

Please check here for the cluvfy output.

Grid Infrastructure Installation:-

When the cluvfy completed successfully, follow the below steps for the Oracle 12cR1 grid installation. Go to the unzipped directory of the grid software and start the grid installation by executing the runInstaller.

$ cd <path-to-grid-software>/
$ ./runInstaller

Here, provide a cluster name and the scan-name of the cluster.

As similar to normal two node RAC installation, provide the public and virtual ip address name’s of the servers in the below page of the configuration.

Select a diskgroup where we need to place the OCR and voting disk of the cluster.

$ sh /oradb/app/oraInventory/orainstRoot.sh
Changing permissions of /oradb/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /oradb/app/oraInventory to oinstall.
The execution of the script is complete.

Node 1:-

$ sh /oradb/app/12.1.0.2/grid/root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oradb/app/12.1.0.2/grid

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 script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oradb/app/12.1.0.2/grid/crs/install/crsconfig_params
2018/09/01 22:25:44 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.

2018/09/01 22:26:14 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.

OLR initialization - successful
root wallet
root wallet cert
root cert export
peer wallet
profile reader wallet
pa wallet
peer wallet keys
pa wallet keys
peer cert request
pa cert request
peer cert
pa cert
peer root cert TP
profile reader root cert TP
pa root cert TP
peer pa cert TP
pa peer cert TP
profile reader pa cert TP
profile reader peer cert TP
peer user cert
pa user cert
2018/09/01 22:27:02 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.evmd' on 'prodrac101'
CRS-2672: Attempting to start 'ora.mdnsd' on 'prodrac101'
CRS-2676: Start of 'ora.mdnsd' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.evmd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'prodrac101'
CRS-2676: Start of 'ora.gpnpd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'prodrac101'
CRS-2672: Attempting to start 'ora.gipcd' on 'prodrac101'
CRS-2676: Start of 'ora.cssdmonitor' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.gipcd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'prodrac101'
CRS-2672: Attempting to start 'ora.diskmon' on 'prodrac101'
CRS-2676: Start of 'ora.diskmon' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.cssd' on 'prodrac101' succeeded

ASM created and started successfully.

Disk Group DBWR_DATA created successfully.

CRS-2672: Attempting to start 'ora.crf' on 'prodrac101'
CRS-2672: Attempting to start 'ora.storage' on 'prodrac101'
CRS-2676: Start of 'ora.storage' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.crf' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'prodrac101'
CRS-2676: Start of 'ora.crsd' on 'prodrac101' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk de3a592de9eb4feabf9fb4121f96c1ae.
Successfully replaced voting disk group with +DBWR_DATA.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE de3a592de9eb4feabf9fb4121f96c1ae (ORCL:DBWR_DATA) [DBWR_DATA]
Located 1 voting disk(s).
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'prodrac101'
CRS-2673: Attempting to stop 'ora.crsd' on 'prodrac101'
CRS-2677: Stop of 'ora.crsd' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.storage' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'prodrac101'
CRS-2677: Stop of 'ora.storage' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.evmd' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.crf' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.ctssd' on 'prodrac101'
CRS-2673: Attempting to stop 'ora.asm' on 'prodrac101'
CRS-2677: Stop of 'ora.mdnsd' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.crf' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'prodrac101' succeeded
CRS-2677: Stop of 'ora.asm' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'prodrac101'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'prodrac101'
CRS-2677: Stop of 'ora.cssd' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'prodrac101'
CRS-2677: Stop of 'ora.gipcd' on 'prodrac101' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'prodrac101' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'prodrac101'
CRS-2672: Attempting to start 'ora.evmd' on 'prodrac101'
CRS-2676: Start of 'ora.mdnsd' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.evmd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'prodrac101'
CRS-2676: Start of 'ora.gpnpd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'prodrac101'
CRS-2676: Start of 'ora.gipcd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'prodrac101'
CRS-2676: Start of 'ora.cssdmonitor' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'prodrac101'
CRS-2672: Attempting to start 'ora.diskmon' on 'prodrac101'
CRS-2676: Start of 'ora.diskmon' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.cssd' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'prodrac101'
CRS-2672: Attempting to start 'ora.ctssd' on 'prodrac101'
CRS-2676: Start of 'ora.ctssd' on 'prodrac101' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'prodrac101'
CRS-2676: Start of 'ora.asm' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'prodrac101'
CRS-2676: Start of 'ora.storage' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'prodrac101'
CRS-2676: Start of 'ora.crf' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'prodrac101'
CRS-2676: Start of 'ora.crsd' on 'prodrac101' succeeded
CRS-6023: Starting Oracle Cluster Ready Services-managed resources
CRS-6017: Processing resource auto-start for servers: prodrac101
CRS-6016: Resource auto-start has completed for server prodrac101
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2018/09/01 22:33:42 CLSRSC-343: Successfully started Oracle Clusterware stack

CRS-2672: Attempting to start 'ora.asm' on 'prodrac101'
CRS-2676: Start of 'ora.asm' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.DBWR_DATA.dg' on 'prodrac101'
CRS-2676: Start of 'ora.DBWR_DATA.dg' on 'prodrac101' succeeded
2018/09/01 22:35:24 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Node 2:-

$ sh /oradb/app/12.1.0.2/grid/root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oradb/app/12.1.0.2/grid

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 script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oradb/app/12.1.0.2/grid/crs/install/crsconfig_params
2018/09/01 22:45:47 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.

2018/09/01 22:46:16 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.

OLR initialization - successful
2018/09/01 22:47:40 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'prodrac102'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'prodrac102'
CRS-2677: Stop of 'ora.drivers.acfs' on 'prodrac102' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'prodrac102' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on 'prodrac102'
CRS-2672: Attempting to start 'ora.evmd' on 'prodrac102'
CRS-2676: Start of 'ora.evmd' on 'prodrac102' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'prodrac102'
CRS-2676: Start of 'ora.gpnpd' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'prodrac102'
CRS-2676: Start of 'ora.gipcd' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'prodrac102'
CRS-2676: Start of 'ora.cssdmonitor' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'prodrac102'
CRS-2672: Attempting to start 'ora.diskmon' on 'prodrac102'
CRS-2676: Start of 'ora.diskmon' on 'prodrac102' succeeded
CRS-2676: Start of 'ora.cssd' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'prodrac102'
CRS-2672: Attempting to start 'ora.ctssd' on 'prodrac102'
CRS-2676: Start of 'ora.ctssd' on 'prodrac102' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'prodrac102'
CRS-2676: Start of 'ora.asm' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'prodrac102'
CRS-2676: Start of 'ora.storage' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.crf' on 'prodrac102'
CRS-2676: Start of 'ora.crf' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'prodrac102'
CRS-2676: Start of 'ora.crsd' on 'prodrac102' succeeded
CRS-6017: Processing resource auto-start for servers: prodrac102
CRS-2672: Attempting to start 'ora.net1.network' on 'prodrac102'
CRS-2676: Start of 'ora.net1.network' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.ons' on 'prodrac102'
CRS-2676: Start of 'ora.ons' on 'prodrac102' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'prodrac101'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'prodrac101' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'prodrac101'
CRS-2677: Stop of 'ora.scan1.vip' on 'prodrac101' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on 'prodrac102'
CRS-2676: Start of 'ora.scan1.vip' on 'prodrac102' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'prodrac102'
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'prodrac102' succeeded
CRS-6016: Resource auto-start has completed for server prodrac102
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2018/09/01 22:52:27 CLSRSC-343: Successfully started Oracle Clusterware stack

2018/09/01 22:52:44 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

Database Binaries Installation:-

Let us start the Oracle 12cR1 database software installation.

In the below page, select the third option whereas we are configuring One Node RAC installation.

$ sh /oradb/app/oraInventory/orainstRoot.sh
Changing permissions of /oradb/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /oradb/app/oraInventory to oinstall.
The execution of the script is complete.

Node 1:-

$ sh /oradb/app/oracle/product/12.1.0.2/db_1/root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oradb/app/oracle/product/12.1.0.2/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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 script.
Now product-specific root actions will be performed.

Node 2:-

sh /oradb/app/oracle/product/12.1.0.2/db_1/root.sh
Performing root user operation.

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oradb/app/oracle/product/12.1.0.2/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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 script.
Now product-specific root actions will be performed.

One Node RAC Database Creation:-

After completing the Oracle 12cR1 database binaries installation, go to the bin directory and start dbca for database creation.

Once the one node RAC installation and database creation is complete, check the database configuration and in which node the database is running by the below commands.

$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /oradb/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DBWR_DATA/ORCL/PARAMETERFILE/spfile.278.985981865
Password file: +DBWR_DATA/ORCL/PASSWORD/pwdorcl.276.985981257
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCLPOOL
Disk Groups: DBWR_FRA,DBWR_DATA
Mount point paths: 
Services: ORCL.oracledbwr.com
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: ORCL
Candidate servers: 
OSDBA group: dba
OSOPER group: oper
Database instances: 
Database is policy managed
$ srvctl status database -d ORCL -v
Instance ORCL_1 is running on node prodrac102. Instance status: Open.
Online relocation: INACTIVE

From the above output we can see that the instance is running in the second node.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle SQL*Loader (sqlldr) Utility Tips And Tricks

 

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:

  • Load data across a network if your data files are on a different system than the database.
  • Load data from multiple data files during the same load session.
  • Load data into multiple tables during the same load session.
  • Specify the character set of the data.
  • Selectively load data (you can load records based on the records’ values).
  • Manipulate the data before loading it, using SQL functions.
  • Generate unique sequential key values in specified columns.
  • Use the operating system’s file system to access the data files.
  • Load data from disk, tape, or named pipe.
  • Generate sophisticated error reports, which greatly aid troubleshooting.
  • Load arbitrarily complex object-relational data.
  • Use secondary data files for loading LOBs and collections.
  • Use conventional, direct path, or external table loads.

  1. Input Datafile contains file containing the data to be loaded.The record format can be specified in the control file with the INFILE parameter.
    cat /home/oracle/employee.txt
    
    100,Hari,MCA,5000
    200,Karthi,Technology,5500
    300,Sunil,Technology,7000
  2. Control file contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

The above control file indicates the following:

  • infile – Indicates the location of the input data file
  • into table – Indicates the table name where this data should be inserted
  • fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
  • ( emp_id, emp_name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded

3. Discard file contains rejected rows those were discarded because they were filtered due to a statement in SQL*Loader control file. Data written to any database table is not written to the discard file.

4. Bad File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.It will have the same name as the data file, with a .bad extension.

5. Log File  contains a detailed summary of the load, including a description of any errors that occurred during the load.

Basics execution of sqlloader :-

Create the table structure:-

SQL> create table employee(emp_id integer,emp_name varchar2(10),dept varchar2(15),salary integer,Join_date date);

Table created.

Input Data :-(Datafile)

cat /home/oracle/employee.txt

100,Hari,MCA,5000
200,Karthi,Technology,5500
300,Sunil,Technology,7000

INSERT : Default value for loading data using SQL loader.

Sqlldr control file :-

[oracle@test]  cat > sqlldr-add-records.ctl
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Run the sqlloader utility:-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-add-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:40:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table EMPLOYEE:
3 Rows successfully loaded.

Check the log file:
sqlldr-add-records.log
for more information about the load.

Check the loaded data in table 

SQL> select * from hari.employee;

EMP_ID      EMP_NAME      DEPT        SALARY       JOIN_DATE
---------- ---------- --------------- -------      ---------
100           Hari        MCA          5000
200          Karthi       Technology   5500
300           Sunil       Technology   7000

APPEND :

Input data into existing table employee :-

cat /home/oracle/newemployee.txt

400,Ram,DBA,5500
500,Siva,Developer,7000
[oracle@test]  cat > sqlldr-append-records.ctl
load data
infile '/home/oracle/newemployee.txt'
append
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-append-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:47:18 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-append-records.log
for more information about the load.
SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY    JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari      MCA              5000
200        Karthi    Technology       5500
300        Sunil     Technology       7000
400        Ram       DBA              5500
500        Siva      Developer        7000

5 rows selected.

TRUNCATE :

Table Structure :

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari       MCA             5000
200        Karthi     Technology      5500
300        Sunil      Technology      7000
400        Ram        DBA             5500
500        Siva       Developer       7000

5 rows selected.

Input Data file :-

cat /home/oracle/newemployee.txt

400,Sam,DBA,5500
500,Scott,Developer,7000

Sqlloader control file :

cat > sqlldr-truncate-records.ctl
load data
infile '/home/oracle/newemployee.txt'
truncate
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-truncate-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 15:22:16 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-truncate-records.log
for more information about the load.

Check the table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
400        Ram        DBA             5500
500        Siva       Developer       7000

REPLACE : 

When REPLACE is specified, the entire table is replaced, not just individual rows.It uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.

Table Structure :-

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Input data :-

[oracle@orcl:~ orcldemo] cat /home/oracle/newemployee.txt
4000,Steven,ITlead,50000
5000,Brad,SystemAdmin,10000

Control file :-

[oracle@orcl:~ orcldemo] cat sqlldr-replace-records.ctl

load data
infile '/home/oracle/newemployee.txt'
replace
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-replace-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 17:08:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-replace-records.log
for more information about the load.

Check Table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Bad and Discard file Scenario :-

Table Structure :-

SQL> create table dept (deptno number,dname varchar(20),location varchar(20));

Table created.

Input data:-

cat sqlloader.dat

10,ACCOUNTING,PAK
30,SALES,RUSSIA
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
70,PRODUCTION,ENG
80,QUALITY,USA

Control file :

cat sqlload.ctl
load data
infile '/home/oracle/sqlloader.dat'
badfile '/home/oracle/badrecords.bad'
discardfile '/home/oracle/dicardload.dsc'
into table dept
WHEN LOCATION!='USA'
fields terminated by ","
(DEPTNO,DNAME,LOCATION)

Run the sqlloader utility :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlload.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 14:43:30 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 7

Table DEPT:
3 Rows successfully loaded.

Check the log file:
sqlload.log
for more information about the load.

Discard file record:-

[oracle@test:~ orcldemo] cat dicardload.dsc
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
80,QUALITY,USA

Bad file record:-

[oracle@test:~ orcldemo] cat badrecords.bad
10,ACCOUNTING,PAK
30,SALES,RUSSIA
70,PRODUCTION,ENG

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba