Example: On Resuming Replication on Skipped Table in logical standby DB

Example : On Resuming Replication on Skipped Table in logical standby DB
Check if table is listed in unsupported list.
SYS (ARS1T3) SQL>select owner,table_name from DBA_LOGSTDBY_UNSUPPORTED where table_name like 'FIELD_ENUM_VALUES';

no rows selected

-- Connect to Primary database and reset and unlock system user if required,If you already knew the password Ignore it as we are going to use DB Link to recreate and populate rows.


All the below steps are in In Logical standby DB
create public database link PROD_DB_SYSTEM connect to SYSTEM identified by <Password> using 'PROD_DB';


-- Make sure its applying
SELECT STATE  FROM  V$LOGSTDBY_STATE;


--Check the LAG time, if any first resolve the LAG.

col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;


-- Check if this table is included in skip list., Here both , DML & DDL is skipped.

SYS (PROD_DB3) SQL>SET LINES 200
select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';SYS (PROD_DB3) SQL>

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y


N                                                                 SCHEMA_DDL                     LOGICAL_USER                        FIELD_ENUM_VALUES              Y





-- If their is no LAG then, Stop the logical standby Process

SYS (PROD_DB3) SQL>ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

-- Unskip the DDL for this table
SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('SCHEMA_DDL','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

ERROR                                                             STATEMENT_OPT                  OWNER                          NAME                           U E
----------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ - -
PROC
--------------------------------------------------------------------------------------------------
N                                                                 DML                            LOGICAL_USER                        FIELD_ENUM_VALUES              Y

-- Below procedure will , drop recreate the table and import data

SYS (PROD_DB3) SQL> EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE('LOGICAL_USER','FIELD_ENUM_VALUES','PROD_DB_SYSTEM');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL>EXECUTE DBMS_LOGSTDBY.UNSKIP('DML','LOGICAL_USER','FIELD_ENUM_VALUES');

PL/SQL procedure successfully completed.

SYS (PROD_DB3) SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SYS (PROD_DB3) SQL>select * from DBA_LOGSTDBY_SKIP WHERE NAME LIKE 'FIELD_ENUM_VALUES';

no rows selected

SELECT STATE  FROM  V$LOGSTDBY_STATE;

STATE
----------------------------------------------------------------
IDLE

press enter to continue


Primary                        Standby                        Lag in Minutes
------------------------------ ------------------------------ --------------
2014/12/10 18:52:10            2014/12/10 18:5:23                         2


After 2 mins

SYS (PROD_DB3) SQL>
SELECT STATE  FROM  V$LOGSTDBY_STATE;SYS (PROD_DB3) SQL>

STATE
----------------------------------------------------------------
APPLYING

SYS (PROD_DB3) SQL>col Primary format a30
col Standby format a30
SELECT TO_CHAR(latest_time,'yyyy/mm/dd hh24:mi:ss') "Primary",
TO_CHAR(applied_time,'yyyy/mm/dd hh24:mi:ss') "Standby",
round ((latest_time-applied_time)*24*60) "Lag in Minutes"
FROM V$LOGSTDBY_PROGRESS;
SYS (PROD_DB3) SQL>SYS (PROD_DB3) SQL>  2    3    4
Primary                        Standby
------------------------------ ------------------------------
                                    Lag in Minutes
--------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10
                                                 0


SYS (PROD_DB3) SQL>
SYS (PROD_DB3) SQL>set lines 200
SYS (PROD_DB3) SQL>/

Primary                        Standby                                                            Lag in Minutes
------------------------------ ------------------------------ --------------------------------------------------
2014/12/10 18:52:10            2014/12/10 18:52:10                                                             0



Check the number of rows on the source and dest table, It must be matching,..

Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

Solved : ORA-01927: cannot REVOKE privileges you did not grant ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled. An execute privilege on DBMS_RLS package has been granted by DVSYS user (that's DB vault sys user I assume)

Solved : ORA-01927: cannot REVOKE privileges you did not grant ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled.
An execute privilege on DBMS_RLS package has been granted by DVSYS user (that's DB vault sys user I assume)
I have been trying to revoke as SYS user and getting ORA-01927: cannot REVOKE privileges you did not grant
Also tried with DVADMIN user to revoke Got ORA-01749: you may not GRANT/REVOKE privileges to/from yourself DB Vault Enabled.


Objective is to Remove GRANT option to DVADMIN user

SYS (DEV_DB) SQL>revoke execute on DBMS_RLS from DVADMIN;
revoke execute on DBMS_RLS from DVADMIN
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

So tried to Grant and Revoke once again.,,,. Entries got added twice Revoke didn't succeed with option

SYS (DEV_DB) SQL>grant execute on dbms_rls to DVADMIN;

Grant succeeded.


GRANTEE  OWNER  TABLE_NAME   GRANTOR  PRIVILEGE  GRA HIE
--------------- ---------- --------- ----------- - ---
DVADMIN  SYS    DBMS_RLS     SYS      EXECUTE    NO  NO
DVADMIN  SYS    DBMS_RLS     DVSYS    EXECUTE    YES NO


SYS (DEV_DB) SQL>revoke execute on dbms_rls from DVADMIN;

Revoke succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE  GRANTEE LIKE 'DVADMIN';

GRANTEE  OWNER   TABLE_NAME   GRANTOR  PRIVILEGE   GRA HIE
-------- ----- - -----------  -------  ----------  --- ---
DVADMIN  SYS     DBMS_RLS     DVSYS    EXECUTE     YES NO
                           
SYS (DEV_DB) SQL>revoke execute on dbms_rls from DVADMIN;
revoke execute on dbms_rls from DVADMIN
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant


Solutions is :Revoked EXECUTE on DBMS_RLS from DVSYS (SYS is the grantor for DVSYS), Which eventually revoked
from DVADMIN as well DVSYS with grant option, Later I granted only Executed permission without grant
Revoked


-- Note I removed some columns for reading ease.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';

GRANTEE                 TABLE_NAME   GRANTOR        GRA HIE
----------------------- ------------ -------------- --- ---
EXECUTE_CATALOG_ROLE    DBMS_RLS     SYS            NO  NO
USER1                     DBMS_RLS     SYS            NO  NO
DVSYS                   DBMS_RLS     SYS            YES NO
DV_OWNER                DBMS_RLS     SYS            NO  NO
DVADMIN                 DBMS_RLS    DVSYS          YES NO

SYS (DEV_DB) SQL>REVOKE EXECUTE ON DBMS_RLS FROM DVSYS;

Revoke succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';

GRANTEE               TABLE_NAME  GRANTOR   GRA HIE
--------------------- ----------- ------------- ---
EXECUTE_CATALOG_ROLE  DBMS_RLS    SYS       NO  NO
USER1                 DBMS_RLS    SYS       NO  NO
DV_OWNER              DBMS_RLS    SYS       NO  NO

SYS (DEV_DB) SQL>
SYS (DEV_DB) SQL>GRANT  EXECUTE ON DBMS_RLS TO DVSYS;

Grant succeeded.

SYS (DEV_DB) SQL>SELECT * FROM DBA_TAB_PRIVS WHERE table_name like 'DBMS_RLS';
                                                                        
GRANTEE               OWNER   TABLE_NAME  GRANTOR    GRA HIE
--------------------- ------- ----------- ---------  --- ---
EXECUTE_CATALOG_ROLE  SYS     DBMS_RLS    SYS        NO  NO
USER1                 SYS     DBMS_RLS    SYS        NO  NO
DV_OWNER              SYS     DBMS_RLS    SYS        NO  NO
DVSYS                 SYS     DBMS_RLS    SYS        NO  NO

SYS (DEV_DB) SQL>
SYS (DEV_DB) SQL>GRANT  EXECUTE ON DBMS_RLS TO DVADMIN;

Grant succeeded.

Successfully Revoked and granted only without GRANT OPTION to user.

Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

Solved: ORA-01918,ORA-00911 Error User Does not exist I was trying to drop a user , which was created with junk character

Solved: ORA-01918,ORA-00911 Error User Does not exist
I was trying to drop a user , which was created with junk character

**********Recommended solution is to drop the user via OEM,*******

Below is NOT recommended by oracle, hence it may be risk
============================

Junk character would have been added by additional space/backspace keystroke by user while creating,.
example
========
username "king" while creating a space or backspace key stroke was hit by user in creation user script as junk character




SQL> select username from dba_users where username like 'king';

no rows selected


SQL> -- Notice the junk character below
SQL> select username from dba_users where username like '%king%';

USERNAME
------------------------------
king?

SQL>drop user king
ERROR at line 1:
ORA-01918: user 'king' does not exist

SQL> drop user king?;
drop user king?
              *
ERROR at line 1:
ORA-00911: invalid character

SQL> drop user "king?";
drop user "king?"
          *
ERROR at line 1:
ORA-01918: user 'king?' does not exist


SQL> -- Note : Make sure their are no segments owned by user
SQL> -- any way user would have not logged in so for,... but just saying
SQL>      
SQL> select user#,name from sys.user$ where name like 'king%';

     USER# NAME
---------- ------------------------------
        96 king?
       
SQL>-- Will update the new name in sys.user$ table, and drop the user and recreate correctly. --- This is unreommeded as we are not suppose to touch the base,.. it may create logical corruption.
SQL>-- Please make sure you DB is backed up if you dont have any other option...

SQL> update sys.user$ set name='king_new' where user#=96 and name like 'king%';

1 row updated.

SQL> commit;

Commit complete.

SQL> select username from dba_users where username like 'king_new';

USERNAME
------------------------------
king_new

SQL> select username from dba_users where username like 'king%'
  2  ;

USERNAME
------------------------------
king_new
SQL> -- Note the double quote
SQL> drop user "king_new";

User dropped.

New create the user again without junk character.


Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

SQL Query to check how much RMAN backup completed so for .

1st Query
==========
--Query to check the size how much MB RMAN May Take

select sum(Bytes/1024/1024) AS SIZE_MB from dba_segments;


2nd Query
=============

-- Query to check the how much RMAN has taken backup so far

select to_char(completion_time,'dd/mm/yyyy') "DATE",sum(blocks)*8/1024 "Backup IN MB" 
from v$backup_datafile
group by to_char(completion_time,'dd/mm/yyyy')
having to_char(completion_time,'dd/mm/yyyy')= to_char(sysdate,'dd/mm/yyyy');



Note : 1. Will only show completed size after first channel completion.... else shows 0
       2. Will only show for the current sysdate ,. 
            which means how much ever completed at 23:59. 
            again if you trigger the query after 00:00 it will calculate for current date and shows 0 until it completes the next channel
           
Note :
====
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
====
Implementing or trying any of the advice from this blog is at your own risk.

[SOLVED] RMAN Restore failed with error ORA-19870,ORA-19504, ORA-27086 Permission denied Restore to different host

[SOLVED] RMAN Restore failed with error ORA-19870,ORA-19504,  

Note :
please use the commands carefully before executing in your server 
its advised to test in test server.

Note : 
Implementing or trying any of the advice from this blog is at your own risk.  



ORA-27086 Permission denied 

Backup Taken in Lab Server 1

and Restoration tested in different Server.

Below is the error noticed and restoration completed for other peice but not completed for one peice

ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use
Linux : 13: Permission denied

ORA-19870: error reading backup piece bk_uikpekrcs_s97876_p1_t854224284
ORA-19507: failed to retrieve sequential file, handle="bk_uikpeks_s97876_p1_t854224284", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   Backup file <bk_uikpekrcs_s97876_p1_t854224284> not found in DISK
failover to previous backup

But backup peice is available in backed up location


====

After analysing  below error
ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use --------------------- <<<<<<< Note this error, can lead you to solution.
Linux : 13: Permission denied

Understood the error

One source Server backup was taken the datafile ORCL_APPTSB_2 name was repeated but if different mount point. like below

File # 5 belongs to /u01/ORCL_APPT_2.dbf  ---- >>>> belongs to /u01
File # 6 belongs to /u02/ORCL_APPT_2.dbf  ---- >>>> belongs to /u02 ----<<<<<

When restoring the file it restores to same destination in /u01 and throws below error
ORA-19870: error reading backup piece bk_ug4p912_s98820_p1_t855090210
ORA-19504: failed to create file "/u01/ORCL_APPT_2.dbf"
ORA-27086: unable to lock file - already in use
Linux : 13: Permission denied



Solution 
=====

Set newname to datafile 6 to '/u01/ORCL_APPTSB_2_2.dbf'; solved the issue.

For datafile 5 let it be same



 

[Solved] opatch apply failed with error " Following executables are active under Oracle Home libclntsh.so.11.1 : OPatch failed with error code 73"

=======================================
Note :
please use the commands carefully before executing in your server its advised to test in test server.

Note : 
Implementing or trying any of the advice from this blog is at your own risk.
=======================================

Entered commands are highlighted in Blue Colour
To be noticed from the output is highlighted in  Red Colour

Solution was to find if any session connected thru cronjob, if so kill it and try agian.

Following executables are active :
/u01/OH/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u01/OH/cfgtoollogs/opatch/opatch2014-01-05_06-43-04AM_1.log

OPatch failed with error code 73


sol_test1 $ ps -ef|grep oracle
  oracle 11506 11500   0 06:43:02 ?           0:00 sh -c ORACLE_HOME=/u01/OH; export ORACLE_HOME; echo
  oracle 11508 11507   0        - ?           0:00 <defunct>
  oracle 11507 11506   0 06:43:02 ?           0:00 /u01/OH/bin/sqlplus -s monitor_usr/*******@TEST1
  oracle 11975 28738   0 06:43:20 pts/2       0:00 grep oracle
  oracle 11974 28738   0 06:43:20 pts/2       0:00 ps -ef
    root 10018  9973   0 22:28:34 pts/1       0:00 sudo su - oracle
  oracle 28738 28737   0 06:33:35 pts/2       0:00 -ksh
    root 28737  6953   0 06:33:35 pts/2       0:00 sudo su - oracle
  oracle 10019 10018   0 22:28:34 pts/1       0:00 -ksh
sol_test1 $ kill -9 11507 11506

After killing the session which was trying to connect the DB from crontab to monitor
Chained session which is starting from 1150* was killed
patch installed successful

Space not recovered after house keeping.... !!!! the open file alert log from oracle database in solaris How to recover space from open files like alert log,. ...from the file system DO NOT MOVE. or Delete.. recovering the space without loosing any contents which is getting updated as well...follow the step

OS : Solaris
Oracle Database
Recover space after housekeeping
===============================

How to recover space from open files like alert log,. 

Have you ever experienced after delete or move a file in Solaris, 

the mount point space was not recovered....unless you stop the process(here we have to bounce the DB)

In case if you ever experienced such issue you may consider following step which will recover the space.

This happens because due open files. ( you may google about this)

consider a scenario where we can do only do alert log file move/delete 

( Alert log is configured with logical standby hence alertlog grows 1gb in 3 days) and have 5 database running

Below Mount point is filled with different files, but will consider housekeeping on alertlog file 

=======================================
Note :
please use the commands carefully before executing in your server its advised to test in test server.

Note : 
Implementing or trying any of the advice from this blog is at your own risk.
=======================================

Open 2 putty session for this
1st Putty session Original Putty to execute all the commands
2nd Putty Session Duplicate to tail the alert log ( Make sure you enable huge number of scroll in putty to have the entire history output just visible)
OS : Solaris

Step 1  : Check the space available in Oracle Home Mount point
========

solaris_test1:testdb1:/ora/admin/testdb1/bdump>df -h .
Filesystem             size   used  avail capacity  Mounted on
/dev/md/dsk/d100       133G   128G   3.2G    98%    /ora
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

Step 2  : Size of the alert log
========

solaris_test1::/ora/admin/testdb1/bdump>du -sh alert_testdb1.log
 3.3G   alert_testdb1.log
solaris_test1::/ora/admin/testdb1/bdump>

Step 3  : other available mount point in case if space is not available in same mount point.
========

solaris_test1::/ora/admin/testdb1/bdump>df -h /ora/db052/temp
Filesystem             size   used  avail capacity  Mounted on
10.97.74.113:/vol/adeis_ps_vol10/ora_db052_temp
                       561G   151G   410G    27%    /ora/db052/temp
solaris_test1::/ora/admin/testdb1/bdump>

Step 4  : Tail the alert log file and make sure you are able to see the db events getting updated.
========

Open another putty session( duplicate of same server) , do below to trap all the alert log events in your putty window.( Make sure huge scroll in putty is enabled so that enire history is getting saved)

solaris_test1::/ora/admin/testdb1/bdump>
solaris_test1::/ora/admin/testdb1/bdump>tail -10f alert_testdb1.log


Step 5  : copy the alert log to another location where you have enough space. !!!! .....DO NOT MOVE. if you move the space will not recover.....!!!!!!

( Run in it background with the help of "&"
========

solaris_test1:testdb1:/ora/admin/testdb1/bdump>cp alert_testdb1.log /ora/db052/temp/alert_testdb1.log_june_5_2014 &
[1] 6078
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

Step 6  : Monitor the background job by command "jobs"
========

solaris_test1:testdb1:/ora/admin/testdb1/bdump>jobs
[1]+  Running                 cp alert_testdb1.log /ora/db052/temp/alert_testdb1.log_june_5_2014 &
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

Step 7  : To see how much copied compare source and destination file size
========

solaris_test1:testdb1:/ora/admin/testdb1/bdump>du -sh alert_testdb1.log
 3.3G   alert_testdb1.log
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

solaris_test1:testdb1:/ora/admin/testdb1/bdump>du -sh /ora/db052/temp/alert_testdb1.log_june_5_2014
 717M   /ora/db052/temp/alert_testdb1.log_june_5_2014


Step 8 : Once the entire files copied then jobs output will not come. and also the size of source and copied will be same(Approximately)
========
solaris_test1:testdb1:/ora/admin/testdb1/bdump>jobs
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

solaris_test1:testdb1:/ora/admin/testdb1/bdump>du -sh *
 3.3G   alert_testdb1.log
  57M   alert_testdb1.log_april_29_2014.gz
   1K   testdb1_arc0_3593.trc
  21K   testdb1_lgwr_1688.trc
   0K   nohup.out
   0K   v1
solaris_test1:testdb1:/ora/admin/testdb1/bdump>du -sh /ora/db052/temp/alert_testdb1.log_june_5_2014
 3.3G   /ora/db052/temp/alert_testdb1.log_june_5_2014

Step 9  : Purge the original file with appending with 0 byte file
========


solaris_test1:testdb1:/ora/admin/testdb1/bdump>touch test1
solaris_test1:testdb1:/ora/admin/testdb1/bdump>cat test1 > alert_testdb1.log
solaris_test1:testdb1:/ora/admin/testdb1/bdump>rm test1

solaris_test1::/ora/product/software>df -h /ora
Filesystem             size   used  avail capacity  Mounted on
/dev/md/dsk/d100       133G   126G   5.5G    96%    /ora

solaris_test1:testdb1:/ora/admin/testdb1/bdump>sync
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

Step 10  : from Duplicatate or another putty window which you capture in Step 4 .. Copy and paste in the alert log file to fill the gap( some events may repeat but we are almost not losing any date
========


copy and paste the alert log output which you capturing from tail -10f output

Step 11  : zip the file
========

solaris_test1:testdb1:/ora/admin/testdb1/bdump>gzip /ora/db052/temp/alert_testdb1.log_june_5_2014 &
[1] 3341
solaris_test1:testdb1:/ora/admin/testdb1/bdump>
solaris_test1:testdb1:/ora/admin/testdb1/bdump>jobs
[1]+  Running                 gzip /ora/db052/temp/alert_testdb1.log_june_5_2014 &
solaris_test1:testdb1:/ora/admin/testdb1/bdump>


Step 12  : move it back to original location or create a soft link. 
========


solaris_test1:testdb1:/ora/admin/testdb1/bdump>du -sh /ora/db052/temp/alert_testdb1.log_june_5_2014.gz
  15M   /ora/db052/temp/alert_testdb1.log_june_5_2014.gz
solaris_test1:testdb1:/ora/admin/testdb1/bdump>mv /ora/db052/temp/alert_testdb1.log_june_5_2014.gz /ora/admin/testdb1/bdump/
solaris_test1:testdb1:/ora/admin/testdb1/bdump>

Crontab is lost !!!!,....... consider taking a backup before and after adding an entry or modifying the crontab ......... You may write a custom script which is automatically take the backup of crontab....... ( You may use the script below)

Note :
please use the script carefully before deploying in your server its advised to implement in test server 

Note : 
Implementing or trying any of the advice from this blog is at your own risk.



# Scripts Start
# This script will generate a crontab backup file and will email to mentioned ids
# Script Created Karthikeyan.K
# karthik-oracledba.blogspot.in
#================================================================================
#Below syntax will create a new file with present date with name specified.
START_DT=`date '+%d_%m_%Y'`
file_name=$HOME/cron_backup_$START_DT.txt

crontab -l > $file_name

/usr/bin/uuencode $file_name $file_name | mailx -s "The crontab backup of HOST(IP)" ____@__.com

# Below will delete 30 Days old crontab
find $HOME/cron_backup_*.txt -mtime +30 -exec rm {} \;

#### IMPORTANT THE ABOVE SYNTAX WILL DELETE ANY FILE IN HOME DIRECTORY WHICH HAS THE NAMING CONVENTION cron_backup_*.txt !!!!!!!!! 

#=================================================================================
# Scripts End








Tip : 
===
place all your cron job shell scripts in one directory this will help in taking a backup of script directory as well if possible direct this parent folder to tape backup

Lost oratab file ? OR Started the db with wrong ORACLE_HOME ? how to find the right ORACLE_HOME which was used to start up the oracle DB

1. A DB was started by mistake with setting different oracle home their was mutiple homes installed in test environment with 30 database running
2. Used below method
3. find the pmon process id  with ps -ef|grep pmon
4. Navigate to cd /proc folder
5. find a folder under /proc with process id  ls -lrt |grep <pmon_processid>
6. Navigate to process id folder and do ls -lrt you will find the path which was set for that process to spawn.



Example - 1
======
testhost1:orcl1:/opt/app>ps -ef|grep pmon
oracle    6237  6222  0 21:30 pts/1    00:00:00 grep pmon
oracle   28245     1  0 Feb16 ?        00:02:48 ora_pmon_orcl1

testhost1:orcl1:/opt/app>cd /proc


testhost1:orcl1:/proc>ls -lrt |grep 28245
dr-xr-xr-x  5 oracle     oinstall              0 2014-02-16 23:11 28245


testhost1:orcl1:/proc>cd 28245
testhost1:orcl1:/proc/28245>ls -lrt
total 0
-r--r--r-- 1 oracle oinstall 0 2014-02-16 23:11 stat
-r--r--r-- 1 oracle oinstall 0 2014-02-16 23:11 status
-r--r--r-- 1 oracle oinstall 0 2014-02-16 23:11 maps
dr-x------ 2 oracle oinstall 0 2014-02-16 23:11 fd
-r--r--r-- 1 oracle oinstall 0 2014-02-16 23:11 statm
-r-------- 1 oracle oinstall 0 2014-02-16 23:11 limits
-r--r--r-- 1 oracle oinstall 0 2014-02-16 23:11 cmdline
lrwxrwxrwx 1 oracle oinstall 0 2014-02-16 23:30 exe -> /opt/app/oracle/product/11.2.0.4/db_4/bin/oracle
dr-xr-xr-x 3 oracle oinstall 0 2014-02-17 22:11 task
dr-xr-xr-x 2 oracle oinstall 0 2014-02-17 22:11 attr
-rw------- 1 oracle oinstall 0 2014-02-22 01:25 seccomp
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 numa_maps
-rw------- 1 oracle oinstall 0 2014-02-22 01:25 mem
-r-------- 1 oracle oinstall 0 2014-02-22 01:25 environ
lrwxrwxrwx 1 oracle oinstall 0 2014-02-22 01:25 cwd -> /opt/app/oracle/product/11.2.0.4/db_4/dbs
-r-------- 1 oracle oinstall 0 2014-02-22 01:25 auxv
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 wchan
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 smaps
lrwxrwxrwx 1 oracle oinstall 0 2014-02-22 01:25 root -> /
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 oom_score
-rw-r--r-- 1 oracle oinstall 0 2014-02-22 01:25 oom_adj
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 mounts
-rw------- 1 oracle oinstall 0 2014-02-22 01:25 mapped_base
-rw-r--r-- 1 oracle oinstall 0 2014-02-22 01:25 loginuid
-r--r--r-- 1 oracle oinstall 0 2014-02-22 01:25 cpuset
testhost1:orcl1:/proc/28245>

Here the ORACLE HOME is /opt/app/oracle/product/11.2.0.4/db_4

Remember process id folder will exist for current running process.

Spool file in RMAN prompt

This will be very useful when you quickly need a huge output from RMAN.

Check 2 examples

Below is the example  1
=====================

testhost1 $
testhost1 $ ls -lrt list_bkp_controlfile_sysdate
list_bkp_controlfile_sysdate: No such file or directory
testhost1 $ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue May 27 01:44:12 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL1 (DBID=394612220)

RMAN> spool log to 'list_bkp_controlfile_sysdate';
RMAN> list backup of controlfile  completed after 'sysdate-1';
RMAN> exit
testhost1 $ ls -lrt list_bkp_controlfile_sysdate
-rw-------   1 oracle   dba         5788 May 27 01:45 list_bkp_controlfile_sysdate
testhost1 $ head list_bkp_controlfile_sysdate

Spooling started in log file: list_bkp_controlfile_sysdate

Recovery Manager11.2.0.2.0

RMAN>
using target database control file instead of recovery catalog

List of Backup Sets
===================
testhost1 $ tail list_bkp_controlfile_sysdate
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56974   Full    68.75M     SBT_TAPE    00:00:46     26-MAY-14
        BP Key: 56974   Status: AVAILABLE  Compressed: NO  Tag: TAG20140526T221945
        Handle: ctrl_u78p99lc1_s60648_p1_t848622785   Media: U00880
  Control File Included: Ckp SCN: 11076235186143   Ckp time: 26-MAY-14

RMAN>

Recovery Manager complete.
testhost1 $ cat list_bkp_controlfile_sysdate |wc -l
     106
     










Below is the example  2
=====================
     
Remember this spool syntax will overright the file if already exists in present working directory 

you may use APPEND(From 10g onwards) to append on the existing file. use below syntax


testhost1 $ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue May 27 01:46:00 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL1 (DBID=394612220)

RMAN> spool log to 'list_bkp_controlfile_sysdate' append;
RMAN>  list backup of controlfile  completed after 'sysdate-2';
RMAN> exit
testhost1 $ ls -lrt list_bkp_controlfile_sysdate
-rw-------   1 oracle   dba        17127 May 27 01:46 list_bkp_controlfile_sysdate
testhost1 $ head list_bkp_controlfile_sysdate

Spooling started in log file: list_bkp_controlfile_sysdate

Recovery Manager11.2.0.2.0

RMAN>
using target database control file instead of recovery catalog

List of Backup Sets
===================
testhost1 $ tail list_bkp_controlfile_sysdate
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56974   Full    68.75M     SBT_TAPE    00:00:46     26-MAY-14
        BP Key: 56974   Status: AVAILABLE  Compressed: NO  Tag: TAG20140526T221945
        Handle: ctrl_u78p99lc1_s60648_p1_t848622785   Media: U00880
  Control File Included: Ckp SCN: 11076235186143   Ckp time: 26-MAY-14

RMAN>

Recovery Manager complete.
testhost1 $
testhost1 $ cat list_bkp_controlfile_sysdate |wc -l
     303
testhost1 $