Installed Oracle RAC 12.2.0.1.0 on Linux 7.5

RAC Intro


Storage: openfiler
OS: Linux 7.5 
Database : 12.2.0.1
CPU: 2 core per node
RAM: 8 GB
Hypervisor: Virtual Box

Node1 : racnode1
Node2 : racnode2


[root@racnode1 bin]# ./olsnodes -n -i -s -t
racnode1        1       racnode1-vip.localdomain        Active  Unpinned
racnode2        2       racnode2-vip.localdomain        Active  Unpinned

[root@racnode1 bin]#

[oracle@racnode2 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

#public IP
192.168.43.222  racnode1.localdomain racnode1
192.168.43.87   racnode2.localdomain racnode2


#private ip
192.168.10.88  racnode1-priv.localdomain racnode1-priv
192.168.10.89  racnode2-priv.localdomain racnode2-priv

#virtul ip

192.168.43.224  racnode1-vip.localdomain racnode1-vip
192.168.43.225  racnode2-vip.localdomain racnode2-vip

# SCAN
192.168.43.228  racnode-scan.localdomain racnode1-scan
[oracle@racnode2 ~]$

[root@racnode2 bin]# ./crsctl check cluster

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@racnode2 bin]#

[root@racnode2 bin]# ./crsctl check cluster -all

**************************************************************

racnode1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

racnode2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

[root@racnode2 bin]# date

Sun May 31 12:23:15 IST 2020

[root@racnode2 bin]#

[oracle@racnode1 ~]$ . grid_env

[oracle@racnode1 ~]$

[oracle@racnode1 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.ASMNET1LSNR_ASM.lsnr

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.DATA.dg

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.LISTENER.lsnr

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.chad

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.net1.network

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.ons

               ONLINE  ONLINE       racnode1                 STABLE

               ONLINE  ONLINE       racnode2                 STABLE

ora.proxy_advm

               OFFLINE OFFLINE      racnode1                 STABLE

               OFFLINE OFFLINE      racnode2                 STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       racnode1                 STABLE

ora.MGMTLSNR

      1        ONLINE  ONLINE       racnode1                 169.254.30.165 192.1

                                                             68.10.88,STABLE

ora.asm

      1        ONLINE  ONLINE       racnode1                 Started,STABLE

      2        ONLINE  ONLINE       racnode2                 Started,STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.cvu

      1        ONLINE  ONLINE       racnode1                 STABLE

ora.guggu.db

      1        ONLINE  ONLINE       racnode1                 Open,HOME=/u01/app/o

                                                             racle/product/12.2.0

                                                             .1/db_home,STABLE

      2        ONLINE  ONLINE       racnode2                 Open,HOME=/u01/app/o

                                                             racle/product/12.2.0

                                                             .1/db_home,STABLE

ora.mgmtdb

      1        ONLINE  ONLINE       racnode1                 Open,STABLE

ora.qosmserver

      1        ONLINE  ONLINE       racnode1                 STABLE

ora.racnode1.vip

      1        ONLINE  ONLINE       racnode1                 STABLE

ora.racnode2.vip

      1        ONLINE  ONLINE       racnode2                 STABLE

ora.scan1.vip

      1        ONLINE  ONLINE       racnode1                 STABLE

--------------------------------------------------------------------------------

[oracle@racnode1 ~]$

SQL> select INST_ID,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance;

   INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T STATUS

---------- --------------- ---------------- ---------------------------------------------------------------- ----------------- --------- ------------

         1               1 guggu1           racnode1.localdomain                                             12.2.0.1.0        31-MAY-20 OPEN

         2               2 guggu2           racnode2.localdomain                                             12.2.0.1.0        31-MAY-20 OPEN

SQL>

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[oracle@racnode1 ~]$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
[oracle@racnode1 ~]$
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# systemctl status chronyd
● chronyd.service - NTP client/server
   Loaded: loaded (/usr/lib/systemd/system/chronyd.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:chronyd(8)
           man:chrony.conf(5)
[root@racnode1 ~]#
[root@racnode1 ~]# systemctl stop chronyd
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# systemctl disable chronyd
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# systemctl status chronyd
● chronyd.service - NTP client/server
   Loaded: loaded (/usr/lib/systemd/system/chronyd.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:chronyd(8)
           man:chrony.conf(5)
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# ls -lrt /etc/chrony.conf
-rw-r--r--. 1 root root 1092 Jan 29  2018 /etc/chrony.conf
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# mv /etc/chrony.conf  /etc/chrony.conf_old
[root@racnode1 ~]#
[root@racnode1 ~]# ls -lrt /etc/chrony.conf
ls: cannot access /etc/chrony.conf: No such file or directory
[root@racnode1 ~]#
[root@racnode1 ~]#
[root@racnode1 ~]# ls -lrt /etc/chrony.conf*
-rw-r--r--. 1 root root 1092 Jan 29  2018 /etc/chrony.conf_old
[root@racnode1 ~]#
[root@racnode1 ~]#

[root@racnode1 ~]# cd /u01/app/12.2.0.1/grid/bin/
[root@racnode1 bin]#
[root@racnode1 bin]# ./crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'racnode1'
CRS-2673: Attempting to stop 'ora.guggu.db' on 'racnode1'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'racnode1'
CRS-2673: Attempting to stop 'ora.chad' on 'racnode1'
CRS-2677: Stop of 'ora.chad' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.mgmtdb' on 'racnode1'
CRS-2677: Stop of 'ora.guggu.db' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.cvu' on 'racnode1'
CRS-2673: Attempting to stop 'ora.racnode1.vip' on 'racnode1'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'racnode1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.mgmtdb' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.racnode1.vip' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.MGMTLSNR' on 'racnode1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'racnode1'
CRS-2677: Stop of 'ora.DATA.dg' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1'
CRS-2677: Stop of 'ora.cvu' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.MGMTLSNR' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.racnode2.vip' on 'racnode1'
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.racnode2.vip' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'racnode1'
CRS-2677: Stop of 'ora.ons' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode1'
CRS-2677: Stop of 'ora.net1.network' on 'racnode1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode1' has completed
CRS-2677: Stop of 'ora.crsd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.storage' on 'racnode1'
CRS-2677: Stop of 'ora.storage' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'racnode1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1'
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
[root@racnode1 bin]#
[root@racnode1 bin]#
[root@racnode1 bin]#



[root@racnode1 bin]#
[root@racnode1 bin]# ./crsctl start cluster -all
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'racnode1'
CRS-2672: Attempting to start 'ora.evmd' on 'racnode1'
CRS-2676: Start of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'racnode1'
CRS-2672: Attempting to start 'ora.diskmon' on 'racnode1'
CRS-2676: Start of 'ora.diskmon' on 'racnode1' succeeded
CRS-2676: Start of 'ora.evmd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cssd' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'racnode1'
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'racnode1'
CRS-2676: Start of 'ora.ctssd' on 'racnode1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'racnode1'
CRS-2676: Start of 'ora.asm' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.storage' on 'racnode1'
CRS-2676: Start of 'ora.storage' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'racnode1'
CRS-2676: Start of 'ora.crsd' on 'racnode1' succeeded
[root@racnode1 bin]#
[root@racnode1 bin]#
[root@racnode1 bin]#


[root@racnode1 bin]# ./crsctl check ctss
CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0
[root@racnode1 bin]#

[oracle@racnode1 diag]$ cluvfy comp clocksync -verbose

Verifying Clock Synchronization ...
  Node Name                             Status
  ------------------------------------  ------------------------
  racnode1                              passed

  Node Name                             State
  ------------------------------------  ------------------------
  racnode1                              Active

  Node Name     Time Offset               Status
  ------------  ------------------------  ------------------------
  racnode1      0.0                       passed
Verifying Clock Synchronization ...PASSED

Verification of Clock Synchronization across the cluster nodes was successful.

CVU operation performed:      Clock Synchronization across the cluster nodes
Date:                         May 30, 2020 9:51:15 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle
[oracle@racnode1 diag]$

[oracle@racnode2 ~]$ cluvfy comp clocksync -verbose -n racnode1,racnode2

Verifying Clock Synchronization ...
  Node Name                             Status
  ------------------------------------  ------------------------
  racnode2                              passed
  racnode1                              passed

  Node Name                             State
  ------------------------------------  ------------------------
  racnode2                              Active
  racnode1                              Active

  Node Name     Time Offset               Status
  ------------  ------------------------  ------------------------
  racnode2      0.0                       passed
  racnode1      0.0                       passed
Verifying Clock Synchronization ...PASSED

Verification of Clock Synchronization across the cluster nodes was successful.

CVU operation performed:      Clock Synchronization across the cluster nodes
Date:                         May 31, 2020 12:24:56 PM
CVU home:                     /u01/app/12.2.0.1/grid/
User:                         oracle
[oracle@racnode2 ~]$

#./crsctl stop cluster –all

# systemctl stop ntpd

#mv /etc/ntp.conf /etc/ntp.conf.original

#systemctl disable ntpd

#./crsctl start cluster –all

Importing Data into Oracle on Amazon RDS using Oracle Data Pump and a Database Link

Import schema in AWS Oracle RDS
==============================================


Environment Details:
-------------------------------

Schema Name  : TEST

Source (Normal)
-----------------

Database Name : india
Database Version : 19c
Platform : oracle linux


Target (RDS)
--------------

Database Name : orcl
Dabase version  : 19c
Platform : AWS Oracle RDS




Step 1 : perform schema export 

expdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test


example 


[oracle@anurag ~]$ expdp  directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test

Export: Release 19.0.0.0.0 - Production on Thu May 28 17:21:14 2020
Version 19.3.0.0.0

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

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST"                               5.539 KB       2 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/oracle/admin/india/dpdump/test_schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 28 17:21:53 2020 elapsed 0 00:00:29

[oracle@anurag ~]$


we can perform export using dataump API as well 

login as sysdba


DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE(handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); 
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/


example :

SQL>
SQL> DECLARE
  2  hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE(handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);   3
  4    5    6  DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/  7    8    9

PL/SQL procedure successfully completed.

SQL>
SQL>



Step 2: ADD RDS database tns entry in TNSNAMES.ora file also validate using tnsping 


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
        (UR = A)
    )
  )
  


Step 3: Create public database link to using AWS rds database endpoint (create as per your requirement like public or private)


create public database link  to_rds19c  connect to "username" identified by "password"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';

SQL> create public database link  to_rds19c  connect to "username" identified by "password"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';
  2
Database link created.

Step 4: validate created database link is working or not 

SQL>
SQL>
SQL> select * from dual@to_rds19c;

D
-
X

SQL>
SQL>

Step 4: Transfer dumpfile to AWS RDS orcl database using database link using below API

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'sample.dmp',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name         => 'sample_copied.dmp', 
destination_database          => 'to_rds19c' 
);
END;
/ 



SQL>
SQL> BEGIN
  2  DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object       => 'DATA_PUMP_DIR',
source_file_name              => 'sample.dmp',
destination_directory_object  => 'DATA_PUMP_DIR',
destination_file_name         => 'sample_copied.dmp',
destination_database          3    => 'to_rds19c'
);
END;
  4    5    6    7    8    9   10  /

PL/SQL procedure successfully completed.

SQL>
SQL>



Step  5: On AWS RDS database validate dumpfile which you transfer  using below command


select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;


Step 6: create schema in target AWS database (orcl) with required privilege  after that Perform import usig datapump API 


DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample_copied.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample_imp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/



Step 7: To monitor import log using below command 

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','sample_imp.log'))





* You have to enable the port for access in AWS security group 


https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-ug.pdf

Simulating ORA-XXXX errors manually in database alert log

Generate ORA-XXX error manually in DB alert log 

login as sysdba


SQL> exec dbms_system.ksdwrt(2,'ORA-00600: This is a test error message for monitoring and can be ignored.');

PL/SQL procedure successfully completed.

SQL>

In Alert_india.log

*************************************************************************
2020-05-26 13:45:21.335000 +00:00
ORA-00600: This is a test error message for monitoring and can be ignored.
2020-05-26 13:45:25.125000 +00:00
ORA-00600: This is a test error message for monitoring and can be ignored.