Set FRA in Oracle RAC Database
Setting Fast Recovery Area (FRA) and Enabling archive log in Oracle RAC Database
aSS
1- Check the database status: srvctl status database -d ORCL
Instance ORCL1 is running on node rac-node1
Instance ORCL2 is running on node rac-node2
2- Create arch directory in ASM disk location (+DATA)
$ asmcmd
asmcmd> lsdg
asmcmd> cd +DATA
asmcmd> mkdir arch
3- If database is up then connect to any instance of database and run the following query to set the parameter:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+Data/arch' SCOPE=BOTH SID='*';
4- Stop the database : srvctl stop database -d ORCL
5- Start database in mount state : srvctl start database -d ORCL -o mount
6- Connect to datbase and enable archive log:
sqlplus / as sysdba
alter database archivelog;
7- Stop the database :srvctl stop database -d ORCL
8- Start database and check archive log is enabled or not
$ srvctl start database -d ORCL
$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 25
Current log sequence 25
9- Check archive is generating or not
SQL> alter system switch logfile;
Go to the location '+Data/arch' in asmcmd and type lsdg
aSS
1- Check the database status: srvctl status database -d ORCL
Instance ORCL1 is running on node rac-node1
Instance ORCL2 is running on node rac-node2
2- Create arch directory in ASM disk location (+DATA)
$ asmcmd
asmcmd> lsdg
asmcmd> cd +DATA
asmcmd> mkdir arch
3- If database is up then connect to any instance of database and run the following query to set the parameter:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+Data/arch' SCOPE=BOTH SID='*';
4- Stop the database : srvctl stop database -d ORCL
5- Start database in mount state : srvctl start database -d ORCL -o mount
6- Connect to datbase and enable archive log:
sqlplus / as sysdba
alter database archivelog;
7- Stop the database :srvctl stop database -d ORCL
8- Start database and check archive log is enabled or not
$ srvctl start database -d ORCL
$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 24
Next log sequence to archive 25
Current log sequence 25
9- Check archive is generating or not
SQL> alter system switch logfile;
Go to the location '+Data/arch' in asmcmd and type lsdg
Setting Up FRA in Oracle Database
To enable the flash recovery area(FRA), you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database) and
DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
Note:
DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
Note:
- DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST in parameter file.
- In a RAC database, all instances must have the same values for these parameters
SELECT dest_name, status, destination FROM v$archive_dest;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oracle/fra';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';
If FRA is ASM Disk Group
--------------------------
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';
Startup mount
alter database close;
Alter database archivelog;
alter database open;
archive log list;
10.10.10.73
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/DCLUATarch' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731122arch' SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731211arch' SCOPE=BOTH;
dg731211
10.10.10.89
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/install/EBSUAT/EBSarch' SCOPE=BOTH;
41-RAC
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+OCRVFDG' SCOPE=BOTH SID='*';
srvctl stop database -d ORCL
srvctl start database -d ORCL -o mount
alter database archivelog;
srvctl stop database -d ORCL
srvctl start database -d ORCL
10.10.10.74
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731124' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731212' SCOPE=BOTH;
----- 10.10.10.73
Instance FRA_LOCATION SIZE ArchiveLog
--------------------------------------------------------------
DCLUAT /u06/FRA/DCLUAT 3G ENABLED
dg731122 /u06/FRA/dg731122arch 3G ENABLED
dg731211 /u06/FRA/dg731211arch 3G ENABLED
----- 10.10.10.41
Instance FRA_LOCATION SIZE ArchiveLog
-----------------------------------------------------------------
ORCL +OCRVFDG 3G ENABLED
----- 10.10.10.74
Instance FRA_LOCATION SIZE ArchiveLog
--------------------------------------------------------------
DG731124 /u04/ARCH/DG731124 3G ENABLED
DG731212 /u04/ARCH/DG731212 3G ENABLED
Dataguard -1
10.10.10.73 primary db:
10.10.10.74 standby
parallel from 41 have to take backup of EBSUAT in /mnt
shift db in archive log and take rman backup in mount state in location of /mnt
dataguard -2
primary 10.10.10.41
standby 10.10.10.89 db has been dropped.
EBSUAT database env file location : . /u01/app/oracle/product/11.2.0/EBSUAT_rac-node1.env
FILE_NAME
--------------------------------------------------------------------------------
+OCRVFDG/ebsuat/data/system17.dbf.
Logfile Maintenance in PostgreSQL
Logfile Maintenance in PostgreSQL
- Save the database server's log output somewhere,
- Need to "rotate" the log files so that new log files are started and old ones removed after a reasonable period of time.
- Only way to truncate the log file is to stop and restart the server.
- Better approach is to send the server's stderr output to some type of log rotation program. There is a built-in log rotation program, which you can use by setting the configuration parameter logging_collector to true in postgresql.conf
- To managing log output is to send it all to syslog and let syslog deal with file rotation. To do this, set the configuration parameter log_destination to syslog (to log to syslog only) in postgresql.conf
Patch in Oracle Database
Patch is a piece of software or code designed to fix the problems of the existing software. These fixes can be security vulnerability fixes or bug fixes. In certain cases the patches increases or enhances the functionality of the software as well
Interim patch
Interim patches are also known as “one of patches”. Interim patches are released in between the release of CPU or PSU patch to fix a specific issue. These patches generally address specific bug’s fixes for a specific customer and should not be applied unless specified by Oracle support services.
Diagnostic patch
It’s a type of interim patch used for the diagnosis of a specific issue. As the name indicates this patches doesn’t fix an issue but helps us to understand an issue it is mostly used by Oracle support services.
Bundle patch
It’s a cumulative patch issued between patch set bundles. patches normally include only fixes but sometimes they include minor enhancements as well. Example Windows bundle patch
Critical patch update (CPU)
These are the cumulative patches consisting of security fixes. The new name for the critical patch updates is security patch update. CPU or SPU provide the security fixes for a single product eg: Oracle Database 11.2.0.1. These patches are cumulative for the same product or a component For example Oracle database 11.2.0.1
Critical patch updates or CPUs are released on quarterly basis.
Mostly they are released on the third week of January, April, July and October.
Patching in Oracle Databases using Opatch Utility
- OPatch is an Oracle-supplied utility that assists you with the process of applying interim patches to Oracle's software and rolling back interim patches from Oracle's software.
- Patches are a small collection of files copied over to an existing installation and associated with particular versions of Oracle products.
- Interim patches are bug fixes available to customers in response to specific bugs and They require a particular base release or patchset to be installed before you can apply them, generally address specific bugs for a particular customer.
- OPatch also supports silent operation. This mode allows you to run the software without any user interaction.
- OPatch has various levels of logging and tracing mechanisms. It also has a debug option that helps to easily diagnose software problems.
OPatch supports the following tasks
1- Applying an interim patch.
2- Rolling back the application of an interim patch.
3- Detecting a conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
4- Reporting on installed products and interim patches.
OPatch uses the following environment variables
ORACLE_HOME — Oracle home location.
export ORACLE_HOME=/u04/oracle/product/database/12102
PATH — Path information.
export PATH=$ORACLE_HOME/OPatch:$PATH
Required:
Oracle Home must to set to valid location
Java commands for Windows, and ar, cp, fuser, and make commands for UNIX must be made available
Prerequisite Checks for OPatch
1- Check ORACLE_HOME and Environment Variable
2- Check for JRE
3- Check for Oracle Universal Installer and OPatch Version Compatibility
4- Check for OPatch Lsinventory : OPatch lsinventory -detail
Note: It is highly recommended before applying the patch , database must be down and back up the ORACLE_HOME before any patch operation
[oracle@dgprim2 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.13
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u04/oracle/product/database/12102
Central Inventory : /u03/app/oracle/oraInventory
from : /u04/oracle/product/database/12102/oraInst.loc
OPatch version : 12.2.0.1.13
OUI version : 12.1.0.2.0
Log file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/opatch2019-04-02_11-47-29AM_1.log
Lsinventory Output file location : /u04/oracle/product/database/12102/cfgtoollogs/opatch/lsinv/lsinventory2019-04-02_11-47-29AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: dgprim2.transappdata.com
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
Location of Opatch Utility: $ORACLE_HOME/OPatch
syntax: opatch [-help] [command] [-option]
help — Displays the help message for the command.
$ opatch -help
Apply Command for OUI-based Oracle Homes
opatch napply Command for OUI-based Oracle Homes
opatch napply <patch_location>
opatch napply <patch_location> -id 1,2,3
opatch napply <patch_location> -skip_subset -skip_duplicate
opatch napply <patch_location> -id 1,2,3 -skip_subset -skip_duplicate
u
Subscribe to:
Posts
(
Atom
)
