How To Enable Flash Recovery Area In Oracle Database

How To Enable Flash Recovery Area In Oracle Database

The flash recovery area(FRA) is an Oracle-managed destination( either FILE SYSTEM or ASM ) for centralized backup and recovery files. It simplifies the backup management.

The following recovery-related files are stored in the flash recovery area:
— Current control file
— Online redo logs
— Archived redo logs
— Flashback logs
— Control file auto backups
— Datafile and control file copies
— Backup pieces
— Foreign archived redo log

Below are the steps for enabling flash recovery area.

DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST initial parameters are required for enabling FRA.

DB_RECOVERY_FILE_DEST_SIZE -> It is the disk quota size for the flash recovery area.
DB_RECOVERY_FILE_DEST – > This initialization parameter is a valid destination for the Flash Recovery Area. It can be a directory, file system, or ASM disk group.

NOTE : DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

1. Check whether FRA is enabled or not.

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL> select * from V$RECOVERY_FILE_DEST;

no rows selected

2. Enable FRA.

SQL> alter system set db_recovery_file_dest_size=20G scope=both;
SQL> alter system set db_recovery_file_dest='/u01/oradata/FRA' scope=both;

SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oradata/FRA
db_recovery_file_dest_size           big integer 20G


select * from V$RECOVERY_FILE_DEST;
NAME                                   SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
-------------------------------------- ----------- ---------- ----------------- --------------- ----------
/u01/oradata/FRA               2.1475E+10          0                 0               0          0

Now FRA has been enabled.

How to make archivelog destination same as flash recovery area:

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     816
Next log sequence to archive   818
Current log sequence           818
SQL> alter system switch logfile;

select * from v$flash_recovery_area_usage

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                           .01                         0               1          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                          .49                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

FOR RAC:

For RAC database, DB_RECOVERY_FILE_DEST_SIZE and DB_RECOVERY_FILE_DEST parameter must be same across all the instances. So it is recommended to put FRA on ASM DISKS.

SQL> alter system set db_recovery_file_dest_size=20G scope=both sid='*';
SQL> alter system set db_recovery_file_dest='+FRADG' scope=both sid='*';

   

Oracle RAC Upgrade From 11.2.0.4 to 12.1.0.2

Oracle RAC Upgrade From 11.2.0.4 to 12.1.0.2

Main steps:

GRID_HOME:

1.check all the services are up and running of GRIDHOME (11gr2)
crsctl check crs
crsctl status resource -t
2.check the GRID infrastructure software version
crsctl query crs activeversion
3.check database status and configuration
srvctl status database -d PROD
srvctl config database -d PROD
4.perform the local backup of OCR, GRID and DATABASE.
./ $GRID_HOME/bin/ocrconfig -export /u01/ocrmanulbkp
use tar command for GRID and DATABASE backup
5.Run the cluvfy utility from 12cr1 to pre-check any errors
./runclufy.sh stage -pre crsinst -upgrade -rolling -scr_crshome <11g loc> -dest_crshome <12c loc> -dest_version 12.1.0.2 -
versbose (-fixup) optionally
6.stop the running 11g database
srvctl stop database -d PROD
6.Unzip the 12c software, change the ownership and permissions
7.start the 12cr1 upgrade by executing ./runInstaller
-select upgrade GRID infrastructure with ASM
-check the existing grid homes
-provide the location of 12c grid home
8.execute the rootupgrade.sh script in both nodes from root user
9.check the clusterware upgrade version
crsctl query crs activeversion
10.start the 11g database
srvctl start database -d PROD
srvctl status database -d PROD

RDBMS_HOME:

1.backup the database before upgrade(important)
take level zero backup or cold backup
2.database pre-checks
-run cluvfy.sh stage -pre dbinst -upgrade -src_dbhome <11g> -dest_dbhome <12c> dest_version 12.1.0.2.0
-unzip the database software
-execute runInstaller
-select the RAC cluster database
-provide the 12c binaries home location
-run the root.sh script on both the nodes with root user

DATABASE:

1.perform the pre-checks
-check the invalid objects
select count(*) from dba_objects where status='INVALID';
-check the duplicate objects owned by sys and system
other than 4 we need to cleanup
2.run the preupgrade tool
copy the prequprd.sql and utluppkg.sql from 12c and run on 11g home
preupgrd.sql will generate 3 files, prequpgrade.log, preupgrade_fixups.sql and postupgrade_fixups.sql
3.execute the preupgrade_fixups.sq
it will set few parameters like processess, job_queue_processes, execute gather status and purge recyclebin
4.check the timezone version, for 12c database timezone is 18 and 11g is 14
select version from v$timezone_file;
5.make sure no files in begin backup mode and no files in media recovery
select * from v$recover_file;
6.disable cronjob and dbms_scheduler jobs
execute dbms_scheduler.disable;
7.remove EM repository because it is superseded in 12c
emctl stop dbcontrol
@?/rdbms/admin/emremove.sql
8.enable flashback by setting 2 parameters
db_recovery_file_dest_size and db_recovery_file_dest
alter database flashback on;
9.create restore point
create restore point bef_upgrade guarantee flashback database;
10.stop the 11g listener and database.
lsnrctl stop PROD
shut immediate;
11.update theORACLE_HOME paths pointing to 12c home
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD
12.move spfile and password file from 11g home dbs directory to 12c home dbs directory
13.start the 12c database with startup upgrade option
startup upgrade;
14.run catupgrade script from os level with parallel=6
goto cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -1 $ORACLE_HOME/diagnostics catupgrd.sql
check the logfiles for diagnosis the upgrde process
15.run the post upgrade status tool
startup
@utlu121s.sql
16.run the catuppst.sql script
$ORACLE_HOME/rdbms/admin/catuppst.sql
16.upgrade DST timezone
downlod the dst upgrade script from metalink
17.run the postupgrade_fixups.sql script
18.change the ORACLE_HOME to 12c in listener.ora file
19.uncomment the crontab and enable the dba_scheduler_jobs
18.run utlrp.sql to compile to invalid objects
19.update the compatible parameter and bounce the db
alter system set compatibe='12.1.0.2' scope=spfile;
Once compatible is set to higher version, downgrade is not possible

IF UPGRADE FAILS:

If you have done the prechecks properly, then chances are very less that, upgrade willl fail. but enven if it is fails for any other reasons like server crash during upgrade, then follow below steps to revert back to 11g version.

1.shutdown immediate;
2.set ORACLE_HOME to 11g
3.startup mount (with the 11g spfile)
4.select * from v$restore_point;
5.flashback database to restore point bef_upgrade (this restore point was created before upgrade)
6.alter database open resetlogs;

Oracle RAC Patching Steps In 11gr2

Oracle RAC Patching Steps In 11gr2

Patch is a software code.

-To fix the bugs
-Defects in software
-It delivers new features/tools

Disadvantages:

-Downtime

Classifications of patches:

-Security patche (CPU/SPU)
-PSU

Pre-requisite before patch (PPA):

-check the invalid objets
-patch conflicts
-patch dependencies
-patch status (applied or not)
-check opatch version (6880880)

GRID patching:

While applying patches on GRID home, bydefault the GRID home is locked

cd $GRID_HOME/crs/install

#./rootcrs.pl -unlock

-it will stop the grid services.
-unlock the GRID home.

$Opatch apply -local
after applying the patch

cd $GRID_HOME/crs/install
#rootcrs.pl -patch

-it will start the GRID services
-it will lock the GRID home.

Apply the GRID patch on node2 with above steps

Patching Steps:

GRID_HOME patching:

-export PATH=$ORACLE_HOME/OPatch:$PATH:.
-check the OPatch version
-check the patch conflicts
-check the invalid objects
-Run rootcrs.pl -unlock script which is located in $GRID_HOME/crs/install with root privileges
-finally apply with OPatch
-OPatch apply -local

RDBMS_HOME patching:

-set the paths
-srvctl stop instance -d PROD -i PROD1
-OPatch apply -local
-srvctl start instance -d PROD -i PROD1
-Run the catbundle.sql script

Note:
catbundle.sql script will update the dba_registry_history table
root.sh will be located in $GRID_HOME/crs/install

Oracle Enterprise Manager (OEM) 13c

Oracle Enterprise Manager (OEM) 13c

Oracle Enterprise Manager is Oracle’s solution to manage/monitor various database/applications which may be running on Cloud infrastructure or on-premise.

1) AGENTS
Agent or management agent is a piece of software that runs on every host that you want to monitor. Management agent work in conjunction with plug-ins to monitor the target server. All hosts where you install the agents are then termed as “Managed Hosts”.

Agents can be of two types:

CENTRAL AGENT : When you first install Oracle Management Service (OMS), by default you receive a management agent called the Central Agent. It is used for monitoring the OMS host, the OMS, and the other targets running on this OMS host.

STANDALONE TARGET AGENT : To monitor other hosts and the targets running on those hosts, you must install a separate Standalone Management Agent on each of those hosts.

2) ORACLE MANAGEMENT SERVICE

OMS is the actual brain of OEM. OMS is deployed over Weblogic 13c. It is a web-based application and perform below broad level activities:

-Works with the Management Agents and the plug-ins to discover targets.
-Monitor and manage the agents
-Store the collected information in a repository for future reference and analysis
-Renders the user interface for Enterprise Manager Cloud Control.

3) ORACLE MANAGEMENT REPOSITORY
Oracle Management Repository is simply a database/database schema where all the information collected by the Management Agent gets stored. The Management Repository then organizes the data so that it can be retrieved by the OMS and displayed in the Enterprise Manager Cloud Control console.

4) PLUG-INS
Plug-ins as name suggest are pluggable entities that offer special management capabilities customized to suite specific target types. Example if your target type is Oracle EBS, you will need specifc plug-ins to monitor the Oracle EBS. Plug-ins are deployed to the OMS as well as the Management Agent in OEM 13c.

Good thing about Plug-ins is that they have independent release cycles, so every time you have a new version of an Oracle product released, you will have a new version of the plug-in released to support monitoring of that new product version in Enterprise Manager Cloud Control.

Default Plugins that OEM 13c will install are:

-Oracle Database
-Oracle Fusion Middleware
-Oracle Exadata
-Oracle Cloud Framework
-Oracle System Infrastructure

In addition to these plug-ins, you can optionally install other plug-ins available in the software kit.

5) BI PUBLISHER
Oracle Business Intelligence (BI) Publisher is Oracle’s primary reporting tool for authoring, managing, and delivering all your highly formatted documents. Starting with Oracle Enterprise Manager 13c, Oracle BI Publisher is installed and configured by default on the OMS.

6) CONSOLE
Console is the GUI Front end of the OEM application. With the help of the console, you can monitor and administer your entire computing environment from one location. All the systems and services including enterprise application systems, databases, hosts, middleware application servers, listeners etc will be visible through Console

7) EM CLI
The Enterprise Manager Command Line Interface (EMCLI) is a command-line too that is accessible through classic programming language constructs, enabling tasks t be created and run either from the command-line or programatically.

8) TARGETS
A target instance, can be defined as any entity that can be monitored within an enterprise. Managed targets are the entities that Enterprise Manager can monitor and manage. Examples of targets include hosts, databases, application servers, applications, and listeners. As your environment changes, you can add and remove targets from Enterprise Manager as required.

9) CONNECTORS
Connector is a very specialized piece of software whose work is to be act like an intermediary between OEM and third party application like BMC Remedy Ticket generation system. Connectors make your life easier in the sense that they give you ready-made solution to connect your OEM system to other famous third party applications.

10) JVMD ENGINE
Java Virtual Machine Diagnostics (JVMD) Engine enables you to diagnose performance problems in Java applications. Starting with Oracle Enterprise Manager 13c , as part of the Oracle Fusion Middleware Plug-in deployment, one JVMD Engine is installed and con􀃝gured by default on the OMS. You will also need JVMD Agents to be manually deployed on the targeted JVMs.