Resize Redolog files in Data guard environment

Primary

1. Check the current details of redolog and standby redolog files

SQL> select group#,bytes/1024/1024 size_,status from v$log;

    GROUP#      SIZE_   STATUS
---------- ------------  ----------
         5       512    INACTIVE
         6       512    INACTIVE
         7       512    CURRENT

SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
         1            512
         2            512
         3            512

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------------------
         1 /data01/oradata/smsprod/stby_redo04a.log
         2 /data01/oradata/smsprod/stby_redo05a.log
         3 /data01/oradata/smsprod/stby_redo06a.log
         5 /data01/oradata/smsprod/redo05a.log
         6 /data01/oradata/smsprod/redo06a.log
         7 /data01/oradata/smsprod/redo07a.log

2. Drop existing redolog files which is not in 'CURRENT' state

SQL> alter database drop logfile group 1;              
Database altered.

SQL> alter database drop logfile group 2;
Database altered.  

3. Create redologs

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

4. Switch logfiles manually until Group 3 becomes INACTIVE state

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint global;
System altered.

5. Makesure group 3 is INACTIVE

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

6. Drop group 3 and re-create

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

7. Create standby logfile. Standby logfile should be same size of redolog files and count should be redolog +1 (Here need to create 4 standby logfiles as we have 3 redolog files

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

Standby

1. Cancel Redo Apply

SQL> alter database recover managed standby database cancel;
Database altered.

2. Change standby management to manual

SQL> show parameter standby_file_management

NAME                           TYPE        VALUE
--------------------------- ----------- ------------
standby_file_management                  string            AUTO

SQL> alter system set standby_file_management=manual;
Database altered.

3. Check existing files

SQL> show parameter standby_file_management

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         7 ACTIVE
         1 UNUSED

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         5 UNASSIGNED
         6 UNASSIGNED

4. Clear logfiles which is 'ACTIVE' state

SQL> alter database clear logfile group 7;
Database altered.

5. Drop All redolog and standby log files

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 7;
Database altered.

SQL> alter database drop logfile group 5;
Database altered.

SQL> alter database drop logfile group 6;
Database altered.

6. Re-create same as primary

SQL> alter database add logfile group 1 ('/data01/oradata/smsprod/onlinelog/redo01.log','/data02/oradata/smsprod/onlinelog/redo01.log') size 1024M;
Database altered.

SQL> alter database add logfile group 2 ('/data01/oradata/smsprod/onlinelog/redo02.log','/data02/oradata/smsprod/onlinelog/redo02.log') size 1024M;
Database altered.

SQL> alter database add logfile group 3 ('/data01/oradata/smsprod/onlinelog/redo03.log','/data02/oradata/smsprod/onlinelog/redo03.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 5 ('/data01/oradata/smsprod/onlinelog/stby_redo05.log','/data02/oradata/smsprod/onlinelog/stby_redo05.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 6 ('/data01/oradata/smsprod/onlinelog/stby_redo06.log','/data02/oradata/smsprod/onlinelog/stby_redo06.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 7 ('/data01/oradata/smsprod/onlinelog/stby_redo07.log','/data02/oradata/smsprod/onlinelog/stby_redo07.log') size 1024M;
Database altered.

SQL> alter database add standby logfile group 8 ('/data01/oradata/smsprod/onlinelog/stby_redo08.log','/data02/oradata/smsprod/onlinelog/stby_redo08.log') size 1024M;
Database altered.

7. Change standby file management parameter

SQL> alter system set standby_file_management=auto;
System altered.

8. Start redo apply

SQL> alter database recover managed standby database disconnect from session;
Database altered.

Install Oracle Client In Silent Mode on Linux 8

1. Install preinstall package for Oracle 19c

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

2. If you using RHEL8, then retreive RPM's from repository as follows

curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

yum -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm

3. Download oracle 19c database software

https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html

4. Copy downloaded file to the server and unzip under oracle_home

mkdir /u01/app/oracle/product/19.3.0 -p
cd /u01/app/oracle/product/19.3.0 
unzip ~/LINUX.X64_193000_client.zip

5. Modify Response file as below

/usr1/app/oracle/product/client/response/client_install.rsp

UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/usr1/app/oracle/product/oraInventory
ORACLE_HOME=/usr1/app/oracle/product/19.3.0/
ORACLE_BASE=/usr1/app/oracle/product
oracle.install.client.installType=Administrator

6. To avoid bug '[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'' in Oracle 19c on RHEL8

export CV_ASSUME_DISTID=OEL7.8

7. Install DB software as silent mode

./runInstaller -showProgress -silent  -responseFile /usr1/app/oracle/product/19.3.0/client/response/client_install.rsp
Prepare in progress.
..................................................   7% Done.

Prepare successful.

Copy files in progress.
..................................................   12% Done.
..................................................   17% Done.
..................................................   22% Done.
..................................................   27% Done.
..................................................   32% Done.
..................................................   37% Done.
..................................................   42% Done.
..................................................   47% Done.
..................................................   52% Done.
........................................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.

Setup files in progress.
..................................................   57% Done.
....................
Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup in progress.
..........
Finish Setup successful.
The installation of Oracle Client 19c was successful.
Please check '/usr1/app/oracle/oraInventory/logs/silentInstall2023-01-20_11-07-59AM.log' for                                                                                                 more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   67% Done.

Prepare for configuration steps in progress.

Prepare for configuration steps successful.
..................................................   82% Done.

Oracle Client Configuration in progress.

Oracle Client Configuration successful.
..................................................   96% Done.

As a root user, execute the following script(s):
        1. /usr1/app/oracle/oraInventory/orainstRoot.sh



Successfully Setup Software with warning(s).
..................................................   100% Done.

8. Execute root.sh from root user

/usr1/app/oracle/oraInventory/orainstRoot.sh

HugePages Configuration for Oracle on Linux (x86-64)

For large SGA sizes, HugePages can give substantial benefits in virtual memory management. Without HugePages, the memory of the SGA is divided into 4K pages, which have to be managed by the Linux kernel. Using HugePages, the page size is increased to 2MB (configurable to 1G if supported by the hardware), thereby reducing the total number of pages to be managed by the kernel and therefore reducing the amount of memory required to hold the page table in memory. In addition to these changes, the memory associated with HugePages can not be swapped out, which forces the SGA to stay memory resident. The savings in memory and the effort of page management make HugePages pretty much mandatory for Oracle systems running on x86-64 architectures. However their is a limitation by Oracle, because Automatic Memory Management (AMM) does not support HugePages. If you already use AMM and MEMORY_TARGET is set you have to disable it and switch back to Automatic Shared Memory Management (ASMM). That means set SGA_TARGET and PGA_AGGREGATE_TARGET.

1. To determine how much memory currently using to support the page table

[root@bo3udsmsxodb01 ~]# grep PageTables /proc/meminfo
PageTables:        9764 kB

2. Check current HugePage usages

[root@bo3udsmsxodb01 ~]$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

3. This script is intended to compute values for the recommended HugePages configuration

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating the overall size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.18') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '5.4') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac

# End

4. Create a text file named hugepages_settings.sh and Copy above contents

5. Make hugepages_settings.sh as executable file

[root@bo3udsmsxodb01 ~]# chmod +x hugepages_settings.sh

6. Verify all the below requiremnet has been met to execute the hugepages_settings.sh

• Oracle Database instance(s) are up and running 
• Automatic Memory Management (AMM) is not setup 
• The shared memory segments can be listed by command "ipcs -m" 
• Oracle Linux Package 'bc' installed

7. Run hugepages_settings.sh

[root@bo3udsmsxodb01 ~]# ./hugepages_settings.sh

8. Script return the computed value of HugePages.

[root@bo3udsmsxodb01 ~]# Recommended setting: vm.nr_hugepages = 16386

9. Modify /etc/sysctl.conf, Add 'vm.nr_hugepages = 16386' to end of the file and save

10. Run 'sysctl -p' effect the changes. Make sure the parameter and values are same as recommended

[root@bo3udsmsxodb01 ~]# sysctl -p
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
vm.nr_hugepages = 16386

11. You can verify whether HugePages are created now

[root@bo3udsmsxodb01 ~]# grep Huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:   16386
HugePages_Free:    16386
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        33558528 kB

Reference

Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)

ORA-65140: invalid common profile name

Problem

Following Error occur while create new profile

SQL> Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited;
Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited
               *
ERROR at line 1:
ORA-65140: invalid common profile name;

Cause

Try to create profile by loging to the CDB when you should be logged into a PDB.

Solution

You can create profile when login to Pluggable database (PDB) instead of login to Container Database (CDB). If you want to continue from CDB, Please follow the step below

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> Create profile ords_profile limit PASSWORD_LIFE_TIME unlimited;

Profile created.

SQL> alter user ORDS_PUBLIC_USER profile ords_profile;

User altered.