Monday 17 December 2018

How to recover database from rman command with example


Hi DBA-Mates,
We would like give a simple demo for how to recover database from rman backup with example.

It is very simple steps. As we all know these steps but for Quick check we can check it.

Please find the below details.
Backup details:
[oracle@localhost backup]$ ls -ltr
total 816752
-rwxrwxrwx 1 oracle oracle 794894336 Dec 12 05:09 ORCL_20181212_5_1_FULL
-rwxrwxrwx 1 oracle oracle   1114112 Dec 12 05:09 ORCL_20181212_6_1_FULL
-rwxrwxrwx 1 oracle oracle   9797632 Dec 12 05:31 ORCL_20181212_7_1_CONTROL
-rwxrwxrwx 1 oracle oracle  29705728 Dec 12 05:46 ORCL_20181212_8_1_ARCHIVE

 [oracle@localhost backup]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 12 05:58:31 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (not mounted)

1. RMAN> startup nomount;
database is already started

2. RMAN> restore controlfile from '/home/oracle/backup/ORCL_20181212_7_1_CONTROL';
Starting restore at 12-DEC-18
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 12-DEC-18

3. RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

4. RMAN> restore database;
Starting restore at 12-DEC-18
Starting implicit crosscheck backup at 12-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 12-DEC-18
Starting implicit crosscheck copy at 12-DEC-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-18
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ORCL_20181212_5_1_FULL
channel ORA_DISK_1: piece handle=/home/oracle/backup/ORCL_20181212_5_1_FULL tag=DATABASE_BKP121218
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:37
Finished restore at 12-DEC-18

5. RMAN> recover database;
Starting recover at 12-DEC-18
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 565 is already on disk as file /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc
archived log for thread 1 with sequence 566 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo02.log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2018_12_12/o1_mf_1_565_g1249wpd_.arc thread=1 sequence=565
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo02.log thread=1 sequence=566
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-DEC-18

6. RMAN> alter database open resetlogs;
database opened
RMAN>

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE
SQL>

Some more useful links:
Regards,

Wednesday 12 December 2018

Real Time Oracle Apps DBA Interview Questions

Dear DBA-Mates,
Hope you all are doing Good!!!

We understand that finding answers of these Questions is not easy for freshers but sorry due to lack of time, we are not able to prepare the Answers for all the below Questions.But these all are the asked question's in interview.

But please do some research, you will get it all. And doing research is a DBA's main task. So, please keep reading and sharing the Answers also...

1)Let me the flow of application when user login?
2)What is the purpose of authentication to connect to database?
3)Why u need GUEST/ORACLE To connect to database?
4)What is the purpose of JSERV?
5)How to increase the performance of Apache?
6)Tell me some issues with Apache?
7)Why u need httpd.pid?why u need to clear that?

10)If One dba can launch the forms and another unable to launch
the forms?what will be the issue?
11)How to set trace at forms level?
12)If u have Concurrent request with pending -standby
how do your troubleshoot?
13)If you have long pending requests and system become slow.what you do?
14)If you lost http.conf file, without running autoconfig to create all files,I want to generate only http.conf individually?
15)How autoconfig works?

16)what Concurrent Tables that will cleanup when u run cmclean.sql?
17)PCP configuration ?What is the principal behind it? how to u
configured?
18)What is diff between distributed AD and Shared Appl_top? what happens when u apply patch and distributed AD and Shared Appl_top?
19)What happen if you Miss Pre-Req patch and apply main patch
and how to do continue?
20)How to do u skip a worker

21)How to u recover if u lost a single file.
22)Explain how Dataguard works?what is the principal behind that?
23)what are different Session waits?
24)How do you check Blocking sessions?
25)Have u common across ora-600 errors?what is the cause of errors?
26)What are the different Events?
27)What are systemstat event Stand for?
28)What is the main purpose of maintenance Mode?
29)What if Apache is up when Maintenance Mode is up?
30)What is heap size?

31)Issues u faced with concurrent Managers?
32)What is the script u used to clean the FND_NODES of the
production Entries on non-Prod Entries.
33)How you mount the production Snap to target without putting the prod in begin backup mode?
34)How to check port Availability?
35)How do you Save time while doing patching?

36)What is the purpose of Defaults File?
37)Where do your increase processes for a concurrent Manager?
38)Wait Events? Different Wait events u have come across and what all the solution u have given to them?
39)How to check Port Availability?
40)How do you save time while doing patching?
41)Have u worked on workflow issues?
42)In Rac How do you do patching?
43)How do you check for conflicts with database patching?
44)What is the purpose of APPS,APPLSYS,APPLSYSPUB,At the login?
45)What happens when u put in database in begin backup mode?
46)why more redo's are generated?

47)Is the Datafile Will update when headers are freeze?
48)what actions can perform on datafiles when headers are freeze?
49)If you have 10 archive logs, How to find how many archives are required to complete the recovery?
50)Have u come across any ora-600 errors?

Hope this may useful and helpful.
Regards,

Friday 7 December 2018

Real time Oracle DBA Interview Questions and Answers

Dear DBA-Mates,
Hope you all are doing Good!!! As per time constraint, not able to provide all the Answers but these are the some important Questions which are asked in Ci... MNC company... We will try to close all the answers slowly-slowly...
But for this time please go through the Questions and test yourself...

1) Rac Complete Configuration with prereq steps?
2)What is Voting disk and OCR File?
3) How u will take ocr Backup if u lost?
4)How to check the node connectivity? what is the script used for?
5)What are the advantages of ASM?
6)Why we use raw devices in ASM? can we use any other storage?
7)How to add redo logs in ASM?
8) How many Undo required in RAC?
9)Benefits of RMAN Backups
10)Have u clone RAC to Non RAC?

11)Clone of RAC to RAC..Tell me the difference?
12)Patching on Distributed AD?
13) Tell me some workflow background process?
14)How to generate DBC file?
15)For Discoverer What is required to set the profile?
16)SSO,OID ..u know?
17)U know Weblogic?
18)U know SOA, Application server?
19)what all the ad utilities u used ?
20)What are the different Status of Worker?

21)U know about events?
22)Let me different Ad options?
23)Flow of apps when u login ?
24)What is the use of DBC File and Tnsnames.ora
what is the difference here...both used for connectivity?
25)PCP Concepts ..how to implement?
26)where do you increase the process for a concurrent Manager
for standard manager?
27)Let the Major difference for 11i and R12?
28)Upgrade process 11.5.9 to R12?
29)How to you licence a product? what is the purpose of adsplice? what is the difference of the both?
30)Have u applied HRMS or Legislative Patches?

31)RAC node ,How do you patch?
32)RAC to Non RAC Clone? Explain and what are the major difference?
33)What exactly happens when any user connect to database?
34)How many undo's required in RAC?
35)What is $TWO_TASK
36)You do Rman Clones?
37)How to do save customizations, What u run autoconfig?
38)How to configure JVM?
39)What is JVM?
40)Have u faced any performance issues After upgrade?

41)what are the services running in R12(1st node - CM and DB
and 2nd node - web and forms
42)Custom_code Creation Steps
43) What are the things that can be done on OAM?
44) Mention some Workflow Tables?
45)why u rebuild workflow tables ?and How?
46)10g Discoverer can be configured on 11?
Hope this may useful...
Regards,

Monday 3 December 2018

How to Restore DR database from RMAN

Dear DBA-Mates,
We would like to share some rough work for "How to Restore DR database" which can be helpful here for quick reference.

First we need to set the value for log_archive_dest_state_2 paramater as shown below in Primary Node.

In Primary Seerver:
SQL>alter system set log_archive_dest_state_2=DEFER sid='*';

Now, we should Physically check all the archive, once the full backup completed, as is available either in primary or in standby.

Then disable the archive purge cronjob in standby.
Also, Stop if any 3rd party snapsync jobs is running. In our case we don't have any.
Now, we will login to Standby and will start restore:

1. We will restore controlfile from production backup controlfile.
Login rman as:
$rman target /

rman>
restore standby controlfile from  '/rmanbk01/ora-data/backup/PRD_20180621_1919_1_CONTROL';
rman>

2. Start database in mount stage:
rman>alter database mount;

3. Start recovering from catalogs.
rman>catalog start with '/rmanbk01/PRD/backup/';

4. Restore the database.
rman>RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
ALLOCATE CHANNEL c5 DEVICE TYPE disk;
ALLOCATE CHANNEL c6 DEVICE TYPE disk;
ALLOCATE CHANNEL c7 DEVICE TYPE disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}

5. Once restore completed, we can check archive and start the catalog.
rman>catalog start with  'path/archivelog';

6. Now, we can start the MRP process as shown below:
rman>alter database recover managed standby database disconnect;

7. From below Query, we can monitor the restore process.

select SID,OPEN_TIME, (sysdate-open_time)*24*60 ela_time,close_time,filename,
(TOTAL_BYTES/1024/1024/1024) GBTOBEREAD,(bytes)/1024/1024/1024 GB_sofar  from V$BACKUP_ASYNC_IO where filename like '/path/datafile%';

Some more useful links:

Wednesday 28 November 2018

How to take Apps Tier backup using gtar command in R12

Hi DBA-Mates, Topic: Backup Apps Tier using gtar command in R12
We would like to share this command because sometimes we got confused while taking backup for Apps Tier in real time scenario.
The confusion is that current APPS directory should not get zipped, really many times such situation came with our junior.

So, I thought lets prepare a document and share with them as well as you all here.
So, hope this may useful and helpful for future references.

One more thing should be noted that, we can take backups for apps tier when the services are up & running.
So, please don't get confused with it, especially for juniors...

Backup can taken in two ways either screen or nohup command. Both are used for backend running process.

How to use screen command:
1. screen -ls  ## it will give output for list of screen which earlier people has used.

2. screen -l   ## this command is used to start new screen, once you type the this command it means you are in New screen where you can perform your activities.

3. Ctrl+a+d    ##  Always use this command to exit from current screen i.e. Press together Ctrl,a,d otherwise your screen will terminated/closed.

4. screen -r screen_name ## this command is used for re-attached old screen, screen_name you can get from screen -ls command.

Note: Before using old screen_name , make sure there should not be running old command or scripts.

If you want to use nohup command then:
nohup < your command for backup > &

Now, let's see the command:
Syntax:  $gtar -czvf </path/backup_filename_date.tar.gz> <directory which you want to be zipped>

Example: $gtar -czvf /u01/backup/apps_12June2018.tar.gz apps

Explanation:
/u01/backup/apps_12June2018.tar.gz -- file backup for cloning.
apps -- we want backup for this directory.

applmgr@ora-data:/u01/ebs->ps -ef|grep gtar
 applmgr  7366  6943   1 23:56:42 pts/15      0:37 gtar -czvf /u01/backup/apps_12June2018.tar.gz apps
 applmgr  7996  9516   1 00:00:43 pts/6       0:03 gtar -czvf /u01/backup/inst_12June2018.tar.gz inst
 applmgr  8007 21175   0 00:00:59 pts/1       0:00 grep gtar
applmgr@ora-data:/u01/ebs->

SCP command syntax with example:
Syntax:
scp user@host:/<source file path> <destination path>  ## running on destination host

Example:
scp oracle@host:/u01/PROD/backup/PROD_20170719_1105702_1_FULL ./

Note: It will ask for password, please provide username's password like oracle user.

Some more important Points:
Oracle Deadlocks
Regards,

Sunday 18 November 2018

Performance Issue and Solution

Dear DBA-Mates,
Recently, we received an email from users that they are facing performance issues.
They are not able to run SELECT query on a particulat schema's table.
When they run the Query as shown below:
select * from abc where rownum=5;

This query got hanged, neither error nor any output. Users waited for long time but neither output nor error. Even we also tried but same issue.

Note: Actually, I suggested them for GSS in starting only but they want some investigation before GSS that. How to run GSS for table in Oracle we can see below.
Then we Checked the below General Points and thought may helpful but didn’t work…

1. Is there any high CPU or Memory usage by using "top" command?
2. Is there any Blocking session?
3. Is there any Lock session?
4. Checked alert log

After checking all above steps we didn't get any things, everything was fine. There was no High CPU or memory usage, no lock and no blocking sessions.
Then we decided to investigate more... We checked alert logs we found some archive was failed but that was not related to it.

Then we checked the Tablespace and mount point file system spaces. It was also fine.
Now we checked the last analyzed date/time for that schema's table. Then we were surprised.
Then we suggested them that we need to run GSS for that particular Schema's table.
We ran the GSS and issues got resolved.

Before:
SQL>select substr(OWNER,1,8) OWNER
, TABLE_NAME
, to_char(LAST_ANALYZED,'DD-MON-RR HH24:MI:SS') LAST_ANALYZED
from dba_tables
where OWNER = 'ABC' and table_name='XYZ';  2    3    4    5

OWNER                            TABLE_NAME
-------------------------------- ------------------------------
LAST_ANALYZED
---------------------------
ABC                           XYZ

SQL> exec dbms_stats.gather_table_stats('ABC','XYZ',estimate_percent => dbms_stats.auto_sample_size);
PL/SQL procedure successfully completed.
SQL>

After:
SQL> set pages 200
SQL> set lines 200
SQL> /

OWNER                            TABLE_NAME                     LAST_ANALYZED
-------------------------------- ------------------------------ ---------------------------
ABC                                            XYZ               19-NOV-17 18:20:58

Some more useful links:
Regards,

Sunday 11 November 2018

How to increase Java heap size in Hyperion

Hi DBA-Mates, Good news that we got one more new error, hurrayyyy...
Recently, we got an error Exception in thread "main" java.lang.OutOfMemoryError: Java heap space … in Hyperion which we would like share with you all DBA’s…

Actually, one of our colleagues was thinking as increasing Java Heap Size is same in Hyperion and in Oracle EBS, but it completely different which we have shown below.
Login to correct Hyperion server where the Node manager, WebLogic and java are running as per our environment. But you can look accordingly as per your environment.

Here path may vary but the main file name is setHPenv.sh where we need to make changes for java heap size.
[oracle@host ~]$ hostname; id
ora-data.blogspot.com
uid=225(oracle) gid=2259(oinstall) groups=209(dba),2259(oinstall)

[oracle@host ~]$ cd /p02/apps/oracle/product/fin/ora-data/fmw/user_projects/orasystem1/planning/planning1
[oracle@host ~]$ pwd
/p02/apps/oracle/product/fin/ora-data/fmw/user_projects/orasystem1/planning/planning1
[oracle@host ~]$ ls -ltr setHPenv.sh*
-rwxr-x---+ 1 oracle oinstall 3776 Jun 23  2017 setHPenv.sh.512
-rwxr-x---+ 1 oracle oinstall 3777 Jun 23  2017 setHPenv.sh.10192015
-rwxr-x---+ 1 oracle oinstall 3983 Nov  8 02:45 setHPenv.sh.210818_bkp
-rwxr-x---+ 1 oracle oinstall 3984 Nov  8 02:59 setHPenv.sh
[oracle@host ~]$
As below we can see that Java heap size value is 512 MB in backup file and now we can increased to 1024 MB (1GB).

Note: Always take backup of file before editing or making any changes in file either on PROD or Test, otherwise you are good to know what can be happen hhhhhh...
Also, always check for "diff" value after made any changes in file and backup file as shown below

[oracle@esu2e005 planning1]$ diff setHPenv.sh.210818_bkp setHPenv.sh
< HP_JAVA_OPTIONS="${HP_JAVA_OPTIONS} -Xmx512M"
---
> HP_JAVA_OPTIONS="${HP_JAVA_OPTIONS} -Xmx1024M"
[oracle@esu2e005 planning1]$
Error Log details:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
                at java.lang.reflect.Array.newArray(Native Method)
                at java.lang.reflect.Array.newInstance(Array.java:52)
                at oracle.jdbc.driver.BufferCache.get(BufferCache.java:229)
                at oracle.jdbc.driver.PhysicalConnection.getByteBuffer(PhysicalConnection.java:12319)
                at oracle.jdbc.driver.OracleStatement.prepareAccessors(OracleStatement.java:1099)
                at oracle.jdbc.driver.OracleStatement.check_row_prefetch_changed(OracleStatement.java:3989)
                at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:599)
                at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:520)
                at com.hyperion.planning.sql.HspSQLImpl.executeQuery(HspSQLImpl.java:228)
                at com.hyperion.planning.sql.HspSQLImpl.executeQuery(HspSQLImpl.java:108)
                at com.hyperion.planning.sql.JDBCCacheLoader.loadObjects(JDBCCacheLoader.java:62)
                at com.hyperion.planning.sql.JDBCCacheLoader.loadObjects(JDBCCacheLoader.java:44)
                at com.hyperion.planning.sql.GenericCache.loadCache(GenericCache.java:383)
                at com.hyperion.planning.sql.GenericCache.getCache(GenericCache.java:150)
                at com.hyperion.planning.sql.GenericCache.getObject(GenericCache.java:294)
Some more useful link:
Regards,

Friday 9 November 2018

How to start MRP process on Dataguard -- standby database

Hi DBA-Mates, Hope you all are doing Great!!!
Usually, we have faced this issue Archive logs not applied on physical standby and this is most important error/issue in our DBA task.

Also, 99% people ask this question in the interviews.
We would like to share our real time scenario here which may useful and helpful to everyone.
Whenever we faced this type of issue, we need to check some important below points:

1. We should check the status of database, which should be mounted status.
SQL> select name, open_mode from v$database;

It may also show in Open (Read-only) mode because it may open for any backup or reporting purpose.

2. Then we need to check the MRP process is running or not as shown below command:
1. Check the MRP process is running or not?
$ ps -ef|grep mrp
$
2. In our case MRP is down. If the MRP process is not running then we need to START the MRP to sync from below command.

As per DBA guidance, we should always cross verify the correct instance and database.

$hostname; id
$echo $ORACLE_SID
PRD02
$sqlplus '/as sysdba'
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL>

Once you start the MRP then archive log will start to sync.
Alert log error details: Providing below alert details , so we can see how the above command is working...

$ cd /p01/app/oracle/diag/rdbms/ora-data/ORA-DATA/trace
$ view alert_ORA-DATA.log
Fri Nov 02 08:41:14 2018
Archived Log entry 1014432 added for thread 1 sequence 608497 rlc 810827692 ID 0x8c1e6f64 dest 2:
RFS[2]: No standby redo logfiles available for thread 1
RFS[2]: Opened log for thread 1 sequence 608499 dbid -1944187801 branch 810827692
Fri Nov 02 12:42:47 2018
alter database recover managed standby database disconnect
Attempt to start background Managed Standby Recovery process (ORA-DATA)
Fri Nov 02 08:42:47 2018
MRP0 started with pid=54, OS id=7948
MRP0: Background Managed Standby Recovery process started (ORA-DATA)
 started logmerger process
Fri Nov 02 08:42:53 2018
Managed Standby Recovery not using Real Time Apply
Fri Nov 02 08:42:53 2018
Reconfiguration started (old inc 4, new inc 6)
List of instances:
 1 2 (myinst: 1)
 Global Resource Directory frozen
 Communication channels reestablished
 Master broadcasted resource hash value bitmaps
 Non-local Process blocks cleaned out
Fri Nov 02 08:42:53 2018
 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Fri Nov 02 08:42:53 2018
 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Fri Nov 02 08:42:53 2018
 LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
 Set master node info
 Submitted all remote-enqueue requests
 Dwn-cvts replayed, VALBLKs dubious
 All grantable enqueues granted
 Submitted all GCS remote-cache requests
 Fix write in gcs resources
Reconfiguration complete
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: alter database recover managed standby database disconnect
Fri Nov 02 08:42:57 2018
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608453.131634.991106577
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423209.63201.991105597
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423210.119301.991106405
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608454.137049.991106667
Fri Nov 02 08:43:12 2018
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608455.141399.991107219
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_1_seq_608456.122183.991107343
Media Recovery Log +RECO_SCL1/ebsprd02/archivelog/2018_11_02/thread_2_seq_423211.111046.991107213
Fri Nov 02 08:43:24 2018

To Stop the MRP process:
$sqlplus '/as sysdba'
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>

Next – How to check Archive Gap and details…(soon)
Some more useful links:
Regards,