Fix RMAN-06059 error in single instance RAC environment

Have you ever wondered if there is an error that can compromise your job with your employer, this is none other than RMAN-06059 the archive log missing, compromises recover ability error
Lets consider the case of a normal single instance environment. When we try to perform backup plus archived redo log, perform backup of archived redo log alone issuing backup archivelog all, if you encounter the error RMAN-06059 expected archived log not found, lost of archived log compromises recover-ability that typically causes backup failure it is something to be noted.
At this point I always think about the way oracle corporation safeguard our job. Typically this error causes failure of backup that needs to be taken into consideration.
Now, let us see what causes this issue and how to fix it?
Archived redo logs form the heart and soul of performing safe, complete recovery of database. Note that missing archived redo logs will compromise data loss. The major reason behind this issue self-explanatory. RMAN is looking for an archived redo log file for backup, the file is missing
In real-time scenarios this can be result of initiating an RMAN backup on a user-managed backup environment. As part of user-managed backup, OS copy would have happened, file is removed physically. RMAN strategy would have been implemented. This entry is not updated in controlfile, causing the error
To fix it, run RMAN command : crosscheck archivelog all; Thsi updates the information in controlfile following which backup will be success
There are cases wherein the file is not part of recovery catalog, is not part of controlfile. To update this issue the command:
catalog start with ‘archivedloglocation’; Typically change in archival location that is not taken care of properly causes catalog to be out of sync. catalog command is an RMAN command that causes proper synchronization
Lets consider a RAC environment. It is typical in case of two node RAC to allocate two channels, perform backup using two channels. It is to be noted that parallelism should be set equivalent to or more than number of channels in RAC environment. IF not this error is caused
Log into RMAN, get details using command:
show all;
Change parallelism as needed. Say in case of three node rac with three channels, set this as follows:
show all;
configure device type disk parallelism 3;
Following this perform backup and this will be success

Free Oracle Database Articles, Tips, Jobs:

Delivered by FeedBurner

Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact fix

I got a call from one of my clients we support remotely. This is a customer hosting oracle database server as separate entity. Client applications do connect to the server in windows environment utilizing OLEDB. All websites have been working fine. All of a sudden the customer reported the following issue:
Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
The error is self-explanatory as it says that the oracle client to server connectivity is lost. As a first step I tested to see if this issue has been happening on a continuous basis. It happened that this is an intermittent issue. Also, if the webpage is accessed from server there is no issue
Next step is to run fiddler capture while webpage is opened and I could not find real useful information from there
I checked sqlnet file setting expire_Time and this is not a big deal. Also, same setting was working fine all these days. Hence , this is not the cause of issue
I ran the trace from database server issuing
alter session set events ‘3135 trace name errorstack level 3’;
The trace files didn’t show something obvious. Also, no error that is of much significance is recorded in alert.log
Hence, it happened to me that this error is totally different and I did check the firewall settings. Now, comes the thief. Firewall was enabled by network admin by mistake causing issue with client server connectivity. They did fix the settings and this issue disappeared

Free Oracle Database Articles, Tips, Jobs:

Delivered by FeedBurner

Write Test Behaviour Using Gherkin Language Introduction

We have seen details on Behaviour Driven Development aka BDD
So, how do we implement this? What tools do we have to implement BDD?
It is ultimate cool cucumber that makes it possible
What is cucumber?
1) Cucumber is a tool that supports behavior driven development(BDD)
2) It is written in ruby and often installed as a gem
3) Supports testing in many different languages including Python, Java, C#, Ruby
Now comes the question on gherkin language.What is a Gherkin Language?
1) It is the native language used to write tests that cucumber understands hence supports behavior driven development (BDD)
2) It is business readable domain specific language
3) It can be written by anyone who undersands domain – Note that if you know domain you can write scenarios using gherkin. If you are an expert it is an icing on the cake
4) Gherkin code comes as a part of cucumber codebase
Gherkin language is used to write behaviors in the following languages:
Cucumber (Ruby)
Lettuce (Python)
Spock (groovy)
Specflow (dotnet)
behat (php)

Get QA Tutorials, Training For Free:

Delivered by FeedBurner

Oracle RAC DBA Interview Questions

1) What is Oracle RAC?
Oracle RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.
2) Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database. Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
There is a single storage that is accessed by two are more of these instances
3) What is single point of failure?
SPOF stands for Single Point Of Failure.Every component in the system must have redundancy incorporated in it.Failure of a single component may lead to failure of entire system.
Components include – power supplies, hardware, storage disks, OS, database,applications.
Oracle Clustering provides solution to avoid SPOF (single Point Of Failure).
4) What is need of clustering solution?
In todays market data is considered to be the most precious asset of an organization. Database is the data repository. Customers need data on a 24×7 basis which demands the servers hosting database to be up and running all the time. As a protection against server failure due to media failure(Failure in server hardware) or instance failure(database failure) clustering solutions have been developed.
5) What is GRD? How does this help with cache fusion?
GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cached block using global resource directory.This process is referred to as cache fusion and helps in data integrity.
6) Give Details on Cache Fusion:-
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion.
7) Give Details on ACMS:-
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
8) What is clustering?
Clustering is a High availability solution.Clustering makes physically separate servers appear as a single server to the end user.Clustering provides scalability at all level – OS, Storage, database, Applications, hardware.Clustering makes the application available 24x7x365.
9) Give details on GTX0-j :-
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.GLOBAL_TXN_PROCESSES setting specifies the initial number of GTXn background processes per instance. This process is seen only in RAC environments. The range of value for GLOBAL_TXN_PROCESSES can be from 1 to 20 and there is no definite need to set this parameter. The number of processes needed is decided by oracle database automatically and is tuned on demand
10) Give details on LMON:-
This process monitors instance membership in a RAC encironment, detects isntance transitions, reconfigures GES and GCS resources as needed. This is called Global Enqueue Service Monitor Process primarily used for managing global resources
11) Give details on LMD:-
As LMON is for monitoring global enqueue services, this is global enqueue services daemon process. This process manages incoming remote resource requests within each instance. LMD0 particularly processes incoming enqueue request messages. IT controls access to global enqueues
12) Give details on LMS:-
This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
13) Give details on LCK0:-
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as library and row cache requests.
14) Give details on RMSn:-
This process is called as Oracle RAC management process.These processes perform manageability tasks for Oracle RAC. Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
15) Give details on RSMN:-
This process is called as Remote Slave Monitor.This process manages background slave process creation and communication on remote http://learnersreference.com/ instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.
16) What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
17) What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE’s, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
18) Give few examples for solutions that support cluster storage:-
ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).
19) Give details on oracle rac lkdebug utility:-
LKDEBUG is an oracle supplied utility.LKDEBUG is integrated with ORADEBUG utility.To use LKDEBUG we must login with SYSDBA system privilege.
LKDEBUG is used to obtain information about the current state GCS and GES structures in the instance.
To obtain information on LKDEBUG options issue the following command
SQL> ORADEBUG LKDEBUG HELP
20) What is an interconnect network?
an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
21) How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware. Windows clusters use the TCP protocol.
22) Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware interconnects.
23) What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
24) How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.
25) What is the use of a service in Oracle RAC environment?
Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.
26) What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing and failover options,and high availability characteristics.
27) Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database
28) What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
29) What is the use of VIP?
If a node fails, then the node’s VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
30) Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
31) What is the significance of VIP address failover?
When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don’t have to wait for TCP connection timeout messages.
32) What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETC
33) How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column, host_:instancename under INST_NAME column.
34) What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAC uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.
35) Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.
36) Why should we have separate homes for ASM instance?
It is a good practice to have ASM home separate from the database home (ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.
37) What is the advantage of using ASM?
Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the/storage configuration across the disks. ASM does this by distributing the database file across all of the available storage within our cluster database environment.
38) What is rolling upgrade?
It is a new ASM feature from Database 11g. ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions
39) Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1) and upwards
40) State the initialization parameters that must have same value for every instance in an Oracle RAC database:-
Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT
41) Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.
42) What two parameters must be set at the time of starting up an ASM instance in a RAC environment?
The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.
43) Mention the components of Oracle clusterware:-
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
44) What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.
45) What is the use of OCR?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
46) How does a Oracle Clusterware manage CRS resources?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
47) Name some Oracle clusterware tools and their uses?
OIFCFG – allocating and deallocating network interfaces
OCRCONFIG – Command-line tool for managing Oracle Cluster Registry
OCRDUMP – Identify the interconnect being used
CVU – Cluster verification utility to get status of CRS resources
48) What are the modes of deleting instances from ORacle Real Application cluster Databases?
We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).
49) How do we remove ASM from a Oracle RAC environment?
We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name
50) How do we verify that an instance has been removed from OCR after deleting an instance?
Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
./crs_stat
51) How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
52) What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type. GV$ views obtain information from individual V$ views.
53) What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.
54) What is the difference between server-side and client-side connection load balancing?
Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
55) Give details on srvm_trace:-
Oracle RAC (Real Application Cluster) SRVM_TRACE environment variable is an oracle RAC (Real Application Cluster) environment variable from Oracle
It is used in the debugging on Oracle RAC (Real Application Cluster) utility srvctl.
56) Give the usage of srvctl:-
srvctl start instance -d db_name -i “inst_name_list” [-o start_options]srvctl stop instance -d name -i “inst_name_list” [-o stop_options]srvctl stop instance -d orcl -i “orcl3,orcl4” -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount
57) What is an OCRCHECK utility?
An ocrcheck utility is a diagnostic tool used for diagnosing OC(Oracle Cluster Registry) Problems.This is used to verify the Oracle Cluster Registry(OCR) integrity.
58) What does an ocrcheck display?
The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.
59) How does ocrcheck perform integrity check?
OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.
60) Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
61) Where does an ocrcheck utility create a log file?
OCRCHECK creates a log file in the directory CRS_home/log/hostname/client.
How can we change the amount of logging?
To change amount of logging, edit the file CRS_home/srvm/admin/ocrlog.ini.

Free Oracle Database Articles, Tips, Jobs:

Delivered by FeedBurner

RMAN interview question answer preparation

We are offering rman interview questions for beginners, rman interview questions for middle level DBA, rman interview questions for experienced. Clear your interview, grab next job
1) What are all the new rman features supported in oracle 12c?
Oracle database 12c supports RMAN the Recovery manager tool to perform backup,restore and recovery. In Oracle database 12c RMAN is enhanced to support many new features that are really interesting
1) Fine-grained recovery is supported – This involves recovery of individual tables from backup. This feature is available in addition to flashback database features
recover table – Command to recover tables/partitions from RMAN backup
This is a great improvement as this provides enhanced point-in-time recovery of tables as opposed to tablespaces in earlier versions. This is primarily useful over FLASHBACK TABLE in cases wherein there is insufficient UNDO space
2) Migration of databases across different platforms is simplified
3) Cloning has been improved which enhances faster duplication of databases using fast ACTIVE duplicate mechanism
4) Multitenant database feature starting 12c can be backed up and restored using RMAN – This is a brand new implementation in 12c. Backup , restore and recovery of both pluggbale databases and container databases is fully supported
5) Faster synchronization of standby databases is supported
2) What is RMAN?
RMAN the oracle recovery manager is a tool from Oracle Corporation
RMAN helps us backup the database files including datafiles, archived redo log files, control files,spfile from within the database with the help of database server itself. This is a utility that comes for FREE as part of oracle database software
3) What files can be backed up using RMAN?
RMAN can make backup of the following files – backups of datafiles, data file image copies, control files and control file image copies, archived redo logs, the SPFILE, RMAN backup pieces
4) What is Proxy copy in RMAN?
RMAN can perform a special kind of backup called as proxy copy
Media manager is given control of copying process
Proxy copies can’t be used with disks and can be used only with tape backups
RMAN>BACKUP DEVICE TYPE sbt PROXY DATAFILE 10(datafile_number);
It is to be noted that media manager will have to determine if a PROXY COPY can be performed as data transfer control is given to media manager
5) What happens if media manager can not perform PROXY COPY?
If a PROXY is specified as part of BACKUP command and media manager determines that data transfer can’t be performed, RMAN will query the media manager. If there is no provision, RMAN will perform backup same as the case without PROXY option being specified
6) What happens when PROXY ONLY option is used with RMAN?
RMAN will query the media manager to determine is proxy copy can be performed. IF media manager fails to do so, RMAN backup fails
7) Why is rman preferred tool for performing oracle recovery process?
RMAN is the preferred tool for performing Oracle recovery process. RMAN selects and applies the necessary data and log files during recovery
RMAN can perform recovery at the data block level with the block media recovery feature, which dramatically reduces recovery time
RMAN provides restore optimization – a great timesaving feature that enables us to bypass data files that are ok during the recovery process
RMAN provides DUPLICATE command which lets us create copies of production database easily
8) What is the use of recovery_catalog_owner role?
Oracle RMAN(Recovery Manager) RECOVERY_CATALOG_OWNER is a predefined system role which includes the following system privileges:
1) ALTER SESSION
2) CREATE CLUSTER
3) CREATE DATABASE LINK
4) CREATE PROCEDURE
5) CREATE SEQUENCE
6) CREATE SESSION
7) CREATE SYNONYM
8) CREATE TABLE
9) CREATE TRIGGER
10) CREATE TYPE
11) CREATE VIEW
RECOVERY_CATALOG_OWNER is the role granted to rman user. rman user is the default user used for creating and accessing the recovery catalog of an oracle database.
9) Give details on backup tag format in RMAN?
RMAN lets us specify tag for every backup so we can easily identify the backup.We can specify the tags when we perform are store or recovery.
RMAN>BACKUP TAG ‘tag_name’ DATABASE;
Using backup tag when we perform a restore or recovery operation , we can speify the tag to identify the backup to use.Tags are very useful in identifying various backups,especially those created using incremental backup strategies.A simple example showing how we can tag a full database backup is given below:
RMAN> BACKUP TAG ‘weekly_full_database_backup’ DATABASE;
We can use the format option with backup commands to specify a location and name for backup pieces and copies. We use substitution variables to generate unique filenames. A example showing how to specify a file format , as well as location,using the FORMAT option is given below:
RMAN> BACKUP FORMAT=’AL_%d%t%s%p’ ARCHIVELOG LIKE ‘%arc_dest%’;
To make the RMAN backup job a non-interfering one we can issue DURATION option with RMAN BACKUP command. This specifies the time oracle should take to perform a backup.
We can use the FORMAT option with backup commands to specify a location and name for backup pieces and copies.We can use substitution variables – RMAN> BACKUP FORMAT ‘NAME_substitution_variables’ ARCHIVELOG LIKE ‘name’;
10) Give details on restore validate command in RMAN:-
RESTORE VALIDATE – check whether a certain object of interest is among the RMAN’s backup set.
RMAN> RESTORE TABLESPACE tablespace-name VALIDATE; – this command asks RMAN to confirm whether it can restore the tablespace from its backup sets
Success Message – “Finished Restore”
Give details on restore preview command in RMAN:-
RESTORE PREVIEW – lets us identify all the backup files necessary for a specific restore operation
RMAN>RESTORE DATABASE/TABLESPACE/DATAFILE tsname/datafilename PREVIEW;
The command provides detailed report of all the backups needed for the RESTORE command to succeed
11) How to PRINT script in RMAN?
RMAN scripts can be printed using PRINT SCRIPT Command
RMAN>PRINT SCRIPT script_name;
To print a global RMAN Script issue:
RMAN>PRINT GLOBAL SCRIPT script_name;
12) What is oracle backup aka Oracle Secure Backup?
Oracle Backup is a media manager from Oracle Corporation used for tape backups.It simplifies and automates backup and recovery operations. In Oracle 10.2 this tool has been named as Oracle Secure Backup
13) How to create recovery catalog?
To create a recovery catalog we must connect to the database in which we want to create the recovery catalog. Create a new recovery catalog owner schema usually named rman (create user rman).
Grant necessary privileges to it, then create recovery catalog-
SQL> grant connect, resource to rman;
SQL> grant recovery_catalog_owner to rman;
Connect to catalog as shown previously
RMAN>CREATE CATALOG ;- creates RMAN recovery catalog in the tablespace assigned as default tablespace for user rman
DROP catalog; – drop catalog
14) How does user-managed recovery work?
User-managed techniques can be used to restore and recover a database
Decide if we can allow users to access the database during the time of recovery
Use commands RECOVER DATABASE/TABLESPACE/DATAFILE
If database is not open it will be in mount state. Now open with ALTER DATABASE OPEN command
RMAN has provision to automate all of the above mentioned user-managed restore and recovery procedures
15) How to Backup Flash Recovery Area in oracle?
RMAN>show all;
………………….
CONFIGURE BACKUP OPTIMIZATION OFF;#default
………………………………
RMAN>configure backup optimization on;
RMAN>backup recovery area;
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
Ensure that tape drive is properly connected.
The steps mentioned above are used to backup flash recovery area.
16) How to backup flashback recovery area using RMAN?
We can backup flash recovery area with RMAN backup commands. We have to run CONFIGURE BACKUP OPTIMIZATION ON. We can backup the flash recovery area to the tape device using the backup commands
RMAN>BACKUP RECOVERY AREA – Allows us to backup every file in the flash recovery area. This will only backup the files that haven’t been backed up to the tape before
RMAN>BACKUP RECOVERY FILES – Backup every file, including files on the file system
RMAN> BACKUP RECOVERY FILE DESTINATION – Move disk backup to tape
17) What are all the tools needed for oracle database backup?
RMAN – Recovery Manager. It is a tool from Oracle corporation. Can be used in command-line mode and as GUI from OEM
Operating system utilities – cp and dd. Used to perform user-managed backups. We use a combination of OS backup commands and SQL*PLUS commands to backup the database files
18) How to connect to rman?
Simply type $rman at OS prompt. This will lead us to RMAN> prompt
RMAN commands can also be used in batch mode
We need SYSDBA privilege to connect to target databases
For connecting to recovery catalog we need special RMAN username/password. SYSDBA privilege is not needed
$rman target / – OS authentication
Recovery catalog – rman target target-database-name catalog username/password@recoverycatalog_SID
19) How to check the syntax in RMAN?
RMAN commands can be issued as individual commands in RMAN> prompt. It is also possbile to script the sequence of commands in a RMAN script.We can check the syntax of a RMAN script and a RMAN command using the parameter CHECKSYNTAX
#rman CHECKSYNTAX @/abc/rmancheck
The above command :
1) Shows message on Recovery Manager version
2) Lists the RMAN commands in the rmancheck file
3) Displays the message – ” The cmdfile has no syntax errors Recovery Manager complete” – If there are no errors
4) Displays an error message if there is an error
20) Is there a way it skip previously backed up files in RMAN?
By default backup optimization is off
We can configure backup optimization to on and skip the previously backed up files
Configure backup optimization on
21) Is there a way to override backup optimization?
Yes. We can make use of FORCE option as part of backup commands – backup database force, backup archivelog all force overrides backup optimization setting , creates backup of database files even though it happens to be identical to previous version of the file
22) What is the main advantage of fast recovery area?
IT is to be noted that flash recovery area introduced in 10g has been renamed to fast recovery area starting 11g. To keep it simple this is a location configured using certain initialization parameters like db_recovery_file_Dest, db_recovery_file_Dest_size instead of log_Archive_DEst, log_Archive_DEst_n, log_Archive_duplex_Dest etc that the RMAN is aware of. This is a disk based location created as part of disk group, ASM etc. Disk based backup and recovery is considered fastest form of recovery and this mechanism comes handy
23) Who can configure fast recovery area?
USer who is owner of oracle software, users logged in as sysdba have the privilege to configure fast recovery area
24) What are all the steps needed to configure fast recovery area?
Here are the simple steps that must be executed in sequence
1) Disable archived redo log destinations. All the destinations must be disabled
alter system set log_Archive_Dest=”;
alter system set log_Archive_Dest_!=”;
2) Log into system as sysdba
sqlplus / as sysdba
sqlplus sys/password as sysdba
3) Configure fast recovery area as follows:
alter system set db_recovery_file_Dest=’location’;
alter system set db_recovery_file_DEst_size=’value’;
The location used for fast recovery area is typically a directory in file system, ASM disk group location etc
25) How is space managed in fast recovery area?
RMAN does utilize the redundancy and retention policies, deletes unwanted obsolete backup files, image copies, archived redo logs etc from fast recovery area. Again delete obsolete command is used for this purpose
26) What is common oracle error while making use of fast recovery area?
It is to be noted that fast recovery area is totally disk based. Henceforth, there is a emergency situation associated with running out of disk space. Here is a common oracle error ORA-38760: This database instance failed to turn on flashback database
To fix this issue do one of the following:
Increase the size of fast recovery area
alter system set db_Recovery_file_dest_Size=’newvalue’;
Manually run rman obsolete, remove unnecessary files
delete obsolete;
Above command does remove the unnecessary files. Files needed for recovery are retained and not touched. In that case we need to increase the fast recovery area size
Get details on restore point, remove some permanent restore points to conserve space
select * from v$restore_point;
drop restore point restore_point_name;
Above steps will help us startup database without error
27) How to delete archived logs based on log sequence number?
delete archivelog from logseq=starting_log until logseq=ending_log
28) How does rman treat user-made backups?
RMAN generally treats user made backups as image copies

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner


ORA-09291: sksachk: invalid device specified for archive destination fix

I tried to help my customer perform restore and recovery of their test server. In a typical scenario restore involves restoring spfile, controlfile, datafiles to start with. Surprisingly the rman restore failed in this site and I got the following error in recovery log file
connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/08/2016 05:27:45
RMAN-04014: startup failed: ORA-16032: parameter LOG_ARCHIVE_DEST_4 destination string cannot be translated
ORA-09291: sksachk: invalid device specified for archive destination
OSD-04018: Unable to access the specified directory or device.
O/S-Error: (OS 2) The system cannot find the file specified.
As a first step in investigation, I created a pfile from spfile.ora
create pfile=’pfile_new.ora’ from spfile;
Then I edited the pfile_new.ora to see the location details associated with initialization parameter LOG_ARCHIVE_DEST_4. This is a folder found missing in destination.
I created this folder manually and proceeded with restore, this did go fine as expected.
Learning: In some sites archives are stored in more than one destination for safety purpose. This is not a common practice. Check with sites to know if they have additional configuration before starting restore process.
Document the learning and retain a copy of this as part of backup folder for future reference

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner


ORA-01688: unable to extend ASH tables in sysaux tablespace fix

I got a call from client stating that they have ORA-1688 messages recorded in alert.log file. Upon investigation I found that there has been storage problems in SYSAUX tablespace causing storage problems
As a side effect, active session history tables that get created, did throw ORA-1688 error owing to unable to extend tablespace.
As a fix, we need to look for free space in sysaux tablespace and address it in one of the following two ways:
1) Add more space to existing tablespace using alter database command
2) Add additional datafile to SYSAUX tablespace to address the space issue
As a side effect indexes belonging to SYS tables became unuable. To fix this issue we need to rebuild index online
select * from dba_indexes where status=’UNUSABLE’;
alter index index_name rebuild online;
It is to be taken care of from capacity planning perspective and more space need to be added to SYSAUX tablespace

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner


Scope of devops for DBA

DBA the database administrator is an essentially important personnel in infrastructure team. In larger enterprises like IBM, Oracle, Google DBA is a separate group in itself.
Devops has been the talk of the town this year. Devops is a culture that did start gaining momentum ever since 2014. In this post lets see what is the scope of Devops for a DBA. As such what does a DBA do in Devops team
Organizations create a brand new Devops engineer profile and much of them talk about knowledge of linux, ability to work comfortably in linux systems etc. System administrators find easy transition onto Devops space. That doesn’t stop there. Every member in infrastructure has some role major or minor when become part of Devops. So, what is expected out of a DBA who is to join Devops team in real-time?
1) Cloud infrastructure knowledge – Datacentre has started moving out of your office space onto cloud ever since amazon launched AWS service in 2006. Though it was thought out to be a service to cater internet based companies like google, facebook etc organizations started realizing the pay as you go model value and are migrating their infrastructure onto cloud.

Say, you are a DBA for a brand new project and you have been asked to come up with capacity planning – Once you come up with proposal on disk space, anticipated growth in next 5 years now firms ask you to migrate your database onto cloud services like AWS, Rackspace etc.

DBA’s will have to work with AWS engineers in case you want to go with Amazon service, migrate your database onto AWS using their GUI tools, make sure performance is perfect. If you find some issues, have them add more CPU’s, disk, memory etc resources on the go. Biggest advantage is that you only pay as you use the resources. You don’t have to lock the money in servers costing 1000’s of dollars as you did before

Take away : DBA’s should learn basics of AWS to support databases in cloud. You will be hired as part of Devops team if you know cloud computing infrastructure like AWS, rackspace, openstack etc.

Recently Oracle has come up with Oracle Cloud that offers database as a service DaaS. Once migrated DBA’s should evangelize this to Devops team

2) Database Install/Upgrades/Migration scripts update – When the culture does get transformed from waterfall onto Devops adopting Lean agile methodologies, DBA’s who work in development will need to checkin the code changes, add new scripts that come as part of product install, upgrade, migration scripts onto source code system, will need to check automated email notification from continuous integration and delivery frameworks like jenkins, make changes, support until code in deployed in pre-production environment

3) Knowledge of Devops infrastructure, tools – When you adapt to a particular culture you start learning tools, techniques, processes that does support it. This is no different. DBA’s should know something about the following tools that are integral part of Devops framework

Linux shell scripting – It can be bash,ksh,csh,bash etc

Source control tools – git, svn, clearcase and can be any choice of your organization. This is wherein you checkin code

Build & release engineering tool – ant, apache maven

Continuous integration and continuous delivery – CI&CD tools can be jenkins, hudson etc

Jira, docker, chef, puppet – some understanding on this will come handy

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner


RMAN performance issue reported from site steps taken

Recently I got a call from customer related to backups. They say that backup is taking longer time and wanted us to investigate more details on what is going on.
Here are some steps we have taken to come up with a solution
1) Get details from v$rman_backup_job_Details view to get details on start_time, end_time, backup size
spool rman_backup_job_Details.html
set markup html on
select * from v$rman_backup_job_Details;
set markup html off
spool off
2) Once we suspect an issue we can generate trace details on the rman command. This is a oracle 12c environment and we can execute sql statements directly from RMAN prompt. IF you are in oracle 11g or earlier version, still run the commands with sql clause as prefix
rman target /
RMAN> alter session set events ‘10046 trace name context forever, level 12’;
RMAN> select ‘RMAN SQL TRACE FILE’ from dual;
RMAN> backup database;
RMAN> exit;
Based on the trace file, we use tkprof to generate output. It turned out that the issue is related to their new network card in backup server

Free Oracle Database Articles, Tips, Jobs :

Delivered by FeedBurner