KTEXPERTS – Page 19 – KNOWLEDGE SHARING PLATFORM

Silent Installation of Oracle 19c Database on AWS

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Silent Installation of Oracle 19c Database on AWS. Hardware Requirements: Yum Packages to install in server:

yum install -y oracle-epel-release-el7
yum install -y oracle-database-preinstall-19c
yum install -y bc
yum install -y binutils
yum install -y compat-libcap1
yum install -y compat-libstdc++-33
yum install -y dtrace-modules
yum install -y dtrace-modules-headers
yum install -y dtrace-modules-provider-headers
yum install -y dtrace-utils
yum install -y elfutils-libelf
yum install -y elfutils-libelf-devel
yum install -y fontconfig-devel
yum install -y glibc
yum install -y glibc-devel
yum install -y ksh
yum install -y libaio
yum install -y libaio-devel
yum install -y libdtrace-ctf-devel
yum install -y libXrender
yum install -y libXrender-devel
yum install -y libX11
yum install -y libXau
yum install -y libXi
yum install -y libXtst
yum install -y libgcc
yum install -y librdmacm-devel
yum install -y libstdc++
yum install -y libstdc++-devel
yum install -y libxcb
yum install -y make
yum install -y net-tools # Clusterware
yum install -y nfs-utils # ACFS
yum install -y python # ACFS
yum install -y python-configshell # ACFS
yum install -y python-rtslib # ACFS
yum install -y python-six # ACFS
yum install -y targetcli # ACFS
yum install -y smartmontools
yum install -y sysstat
yum install -y unixODBC
yum install -y chrony

yum install -y oracle-epel-release-el7

yum install -y oracle-database-preinstall-19c

yum install -y bc

yum install -y binutils

yum install -y compat-libcap1

yum install -y compat-libstdc++-33

yum install -y dtrace-modules

yum install -y dtrace-modules-headers

yum install -y dtrace-modules-provider-headers

yum install -y dtrace-utils

yum install -y elfutils-libelf

yum install -y elfutils-libelf-devel

yum install -y fontconfig-devel

yum install -y glibc

yum install -y glibc-devel

yum install -y ksh

yum install -y libaio

yum install -y libaio-devel

yum install -y libdtrace-ctf-devel

yum install -y libXrender

yum install -y libXrender-devel

yum install -y libX11

yum install -y libXau

yum install -y libXi

yum install -y libXtst

yum install -y libgcc

yum install -y librdmacm-devel

yum install -y libstdc++

yum install -y libstdc++-devel

yum install -y libxcb

yum install -y make

yum install -y net-tools # Clusterware

yum install -y nfs-utils # ACFS

yum install -y python # ACFS

yum install -y python-configshell # ACFS

yum install -y python-rtslib # ACFS

yum install -y python-six # ACFS

yum install -y targetcli # ACFS

yum install -y smartmontools

yum install -y sysstat

yum install -y unixODBC

yum install -y chrony

User/group Creation:

groupadd -g 650 oinstall
groupadd -g 651 dbaoper
groupadd -g 652 dba
groupadd -g 653 asmadmin
groupadd -g 654 asmoper
groupadd -g 655 asmdba
groupadd -g  656 backupdba
groupadd -g 54325 dgdba
groupadd -g 658 kmdba
groupadd -g 659 racdba
useradd oracle -u 1330 -d /home/oracle -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmdba,backupdba,dgdba,kmdba,racdba,ssh_in
useradd grid  -u 1331 -d /home/grid   -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmadmin,asmoper,asmdba,ssh_in

groupadd -g 650 oinstall

groupadd -g 651 dbaoper

groupadd -g 652 dba

groupadd -g 653 asmadmin

groupadd -g 654 asmoper

groupadd -g 655 asmdba

groupadd -g 656 backupdba

groupadd -g 54325 dgdba

groupadd -g 658 kmdba

groupadd -g 659 racdba

useradd oracle -u 1330 -d /home/oracle -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmdba,backupdba,dgdba,kmdba,racdba,ssh_in

useradd grid -u 1331 -d /home/grid -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmadmin,asmoper,asmdba,ssh_in

Setup Filesystem:

mkdir /u01/app/stage
mkdir -p /u01/app/oraInventory
mkdir -p /arch/oradata
mkdir -p /data/control
mkdir -p /arch/control
mkdir -p /data/oradata
mkdir -p /arch/archive
mkdir -p /u01/app/oracle/tde/testpdb1
mkdir -p /arch/redolog
mkdir -p /arch/flash
mkdir -p /arch/archive
chown -R oracle:oinstall /data /arch
cd /u01/app/
chown -R oracle:oinstall  *

mkdir /u01/app/stage

mkdir -p /u01/app/oraInventory

mkdir -p /arch/oradata

mkdir -p /data/control

mkdir -p /arch/control

mkdir -p /data/oradata

mkdir -p /arch/archive

mkdir -p /u01/app/oracle/tde/testpdb1

mkdir -p /arch/redolog

mkdir -p /arch/flash

mkdir -p /arch/archive

chown -R oracle:oinstall /data /arch

cd /u01/app/

chown -R oracle:oinstall *

Download Software from Oracle Site: Software installation by using attached response file: a) Unzip… Read More

Backup Details of SQL

5 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the following Backup Details of SQL. SQL Server offers many options for creating backups. As we discussed what types of backups can be performed based on the recovery model of the database. Here different types of backups… Read More

How to Install Ubuntu 20.04.1 LTS On Windows Using VirtualBox

5 years ago by Ramesh ReddyAWS

Hi Dears, In this article, we will see Install Ubuntu 20.04.1 LTS On Windows.. Steps to Follow Download and Install Oracle VM VirtualBox Download Ubuntu 20.04.1 LTS Create New Virtual Machine Install Ubuntu 20.04.1 LTS in Virtual Machine Install Guest Additions 1. Download and Install… Read More

How to add Container database to Existing RMAN Recovery catalog

5 years ago by Binnary Ajay KumarOracle DB Admin

Hello Readers, In this article ,we will see how to a container database to the existing RMAN recovery catalog. Please check below article before adding container database to RMAN Catalog. How to Create an RMAN Recovery Catalog Target Database : … Read More

How to Get Instance Details By Using Boto3

5 years ago by Ramesh ReddyDevOps

Dear Readers, In this article, we will see the following How to Get Instance Details By Using Boto3. Steps to Follow : Logging to AWS Account Launch Linux Server Connect to Linux EC2 Instance by Using Putty Install Python Package Install Boto3 Package Connect to… Read More

Rebuild Stand By database when PDB restored from restore point/Issue with the PDB

5 years ago by Venkata Sai PavanOracle DB Admin

Hello Readers, In this article, we will see Recovery models in SQL Server. 1. Take backup of the primary database

run {
allocate channel ch1 device type disk  maxpiecesize 2G;
allocate channel ch2 device type disk  maxpiecesize 2G;
backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';
release channel ch1;
release channel ch2;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

run {

allocate channel ch1 device type disk maxpiecesize 2G;

allocate channel ch2 device type disk maxpiecesize 2G;

backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';

release channel ch1;

release channel ch2;

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

2. Create standby control file from primary database.

RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

1	RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

3. Transfer backup pieces along with control file from primary server to standby server.… Read More

Recovery models in SQL Server

5 years ago by KTEXPERTS TEAMSQL Server DBA

Hello Readers, In this article, we will see Recovery models in SQL Server. SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database… Read More

How to Create an RMAN Recovery Catalog

5 years ago by Binnary Ajay KumarOracle DB Admin

Hello Readers, In this article, we will see Creation of RMAN Recovery Catalog. Target Database : Catalog Database: Name : AVPROD … Read More

Oracle : Important Commands for Dataguard (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info

set linesize 300 
col machine for a60 
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; 

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

set linesize 300

col machine for a60

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance;

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE

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

ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

Tab Name : DG_GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  44791                 44791          0
         2                  42354                 42354          0
         3                  40223                 40223          0

         

SQL> 
   THREAD# APPLIED   MAX(SEQUENCE#)
---------- --------- --------------
         1 IN-MEMORY          44791
         1 NO                 39800
         1 YES                44790
         2 NO                 38782
         2 YES                42354
         3 NO                 38673
         3 YES                40223

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 44791 44791 0

2 42354 42354 0

3 40223 40223 0

SQL>

THREAD# APPLIED MAX(SEQUENCE#)

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

1 IN-MEMORY 44791

1 NO 39800

1 YES 44790

2 NO 38782

2 YES 42354

3 NO 38673

3 YES 40223

Tab Name : ERR_DEST

set linesize 300 
col dest_name for a30 
col error for a70 
select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); 
select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; 

   DEST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID
         2 LOG_ARCHIVE_DEST_2             INACTIVE
         3 LOG_ARCHIVE_DEST_3             INACTIVE
         4 LOG_ARCHIVE_DEST_4             DEFERRED

SQL> 
   INST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID

set linesize 300

col dest_name for a30

col error for a70

select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4);

select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1;

DEST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 INACTIVE

3 LOG_ARCHIVE_DEST_3 INACTIVE

4 LOG_ARCHIVE_DEST_4 DEFERRED

SQL>

INST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

Tab Name : Date Format

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

1	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Tab Name :… Read More

Autonomous Health Framework

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the Autonomous Health Framework. AHF includes number of diagnostic tools in a single bundle and helps us to gather various types og information like upgrading info. , oracheck….. AHF combines Trace file analyzer(TFA), orachk etc.., which oracle… Read More