expdp backup script [ tested ] Linux

expdp  userid='system/REP2012@REPDB' full=Y  direct=Y

###############################################################
##    Connect to database and create directory object
###############################################################


SQL> CREATE OR REPLACE DIRECTORY DMP_FILES AS '/backup/dmp';

SQL> GRANT read, write ON DIRECTORY DMP_FILES TO system;

SQL> col DIRECTORY_PATH for a40

SQL> col OWNER for a12

SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER    DIRECTORY_NAME DIRECTORY_PATH
-------- --------------- ---------------------
SYS     DMP_FILES    /backup/dmp

###############################################################
##    From Linux command prompt, create backup script
###############################################################


[oracle@oradb1 ~]# cat /backup/dmp/dmp_backup.sh

### Export Data Pump Backup

expdp  userid='system/REP2012@REPDB' full=Y  direct=Y directory=DMP_FILES 
dumpfile=REPDB_full_$(date +"%a").dmp 
logfile=REPDB_full_$(date +"%a").log


### Send status Email
cat /backup/dmp/REPDB_full_$(date +"%a").log | grep -i 'successfully completed' | mailx -s "EXPDP Backup Status" itgroup@hsbc.com

### Compressing Backup + Log

tar -zcvf /backup/dmp/REPDB_full_$(date +"%a").dmp.tar.gz /backup/dmp/REPDB_full_$(date +"%a").dmp /backup/dmp/REPDB_full_$(date +"%a").log


### Deleting Backup

rm -f /backup/dmp/REPDB_full_$(date +"%a").dmp

How to find number of pysical hard drives in windows

Windows Command for finding number of Physical Hard drive:
C:\> wmic diskdrive list brief 

Output: (two physical drive)

C:\Users\administrator> wmic diskdrive list brief
Caption                             DeviceID            Model                               Partitions  Size
HP LOGICAL VOLUME SCSI Disk Device  \\.\PHYSICALDRIVE0  HP LOGICAL VOLUME SCSI Disk Device  1           146778685440
HP LOGICAL VOLUME SCSI Disk Device  \\.\PHYSICALDRIVE1  HP LOGICAL VOLUME SCSI Disk Device  1           299966300160

alter system kill session [dynamic]

select 'ALTER SYSTEM KILL SESSION ' ||chr(39)||sid||chr(44)||serial#||chr(39)||';' QUERY 

from v$session 
where username='ADMIN';

QUERY
-----------------------------------------
ALTER SYSTEM KILL SESSION '197,4989';



----------------------------------------------------------


SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';
--AND    s.sid=136;

INST_ID  SID    SERIAL# SPID  USERNAME   PROGRAM
------- ---- ---------- ----- ---------- ------------------
      1   63       9889 6800             ORACLE.EXE (J000)
      1  132      12187 7828             ORACLE.EXE (J001)
      1  197       4989 4836  SYS        sqlplus.exe


C:\> set ORACLE_SID
ORACLE_SID=orcl

C:\> orakill <oracle_sid> <spid>

C:\> orakill orcl 7828

Warning: Oracle recommends killing session at database level.

----------------------------------------------------------


DECLARE
A CHAR(2);
BEGIN
FOR I IN 20..50
LOOP
--SELECT CHR(I) INTO A FROM DUAL;
DBMS_OUTPUT.PUT_LINE(I||CHR(32)|| CHR(I));
END LOOP;
END;
/

ORA-19815, ORA-16038, ORA-19809, ORA-00312

Found these above errors in alert log..?

ORA-19815, ORA-16038, ORA-19809, ORA-00312

Database hung..?

Database crashes immediately after startup..?




### Check your alert log for this parameter:


log_archive_dest_1         = "LOCATION=USE_DB_RECOVERY_FILE_DEST"
db_recovery_file_dest      = "D:\ARCHIVELOG"

db_recovery_file_dest_size = 16201108K


### pfile : Increase the value by atleast 1GB.

db_recovery_file_dest_size = 17201108K


SQL> startup

Done.

==========================================================

Further like to delete old archivelogs, proceed below

==========================================================

C:\Users\userIT>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jan 13 12:58:17 2013

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

connected to target database: ROSE (DBID=575541292)

RMAN> delete archivelog all completed before "sysdate-1"; 

### decide upto which archivelog to delete.

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
List of Archived Log Copies for database with db_unique_name ROSE
==============================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
307     1    657     A 11-JAN-13
Name: D:\ARCHIVELOG\2013_01_11\O1_MF_1_657_8H0R9QG0_.ARC

308     1    658     A 11-JAN-13
Name: D:\ARCHIVELOG\2013_01_12\O1_MF_1_658_8H0ZCK66_.ARC

309     1    659     A 12-JAN-13
Name: D:\ARCHIVELOG\2013_01_12\O1_MF_1_659_8H1F745J_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_11\O1_MF_1_657_8H0R9QG0_.ARC RECID=307 STAMP=804463209
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_12\O1_MF_1_658_8H0ZCK66_.ARC RECID=308 STAMP=804470434
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_12\O1_MF_1_659_8H1F745J_.ARC RECID=309 STAMP=804484629
Deleted 3 objects


#### If you already deleted archivelogs manually using OS command, proceed with crosscheck...


RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
validation failed for archived log
archived log file name=D:\ARCHIVELOG\2013_01_12\O1_MF_1_660_8H32D135_.ARC RECID=310 STAMP=804539058
validation failed for archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_661_8H50RVPF_.ARC RECID=311 STAMP=804602956
validation failed for archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_662_8H50S035_.ARC RECID=312 STAMP=804602961
validation failed for archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_663_8H50S2B9_.ARC RECID=313 STAMP=804602964
Crosschecked 4 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
List of Archived Log Copies for database with db_unique_name ROSE
==============================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
310     1    660     X 12-JAN-13
Name: D:\ARCHIVELOG\2013_01_12\O1_MF_1_660_8H32D135_.ARC

311     1    661     X 12-JAN-13
Name: D:\ARCHIVELOG\2013_01_13\O1_MF_1_661_8H50RVPF_.ARC

312     1    662     X 12-JAN-13
Name: D:\ARCHIVELOG\2013_01_13\O1_MF_1_662_8H50S035_.ARC

313     1    663     X 12-JAN-13
Name: D:\ARCHIVELOG\2013_01_13\O1_MF_1_663_8H50S2B9_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_12\O1_MF_1_660_8H32D135_.ARC RECID=310 STAMP=804539058
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_661_8H50RVPF_.ARC RECID=311 STAMP=804602956
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_662_8H50S035_.ARC RECID=312 STAMP=804602961
deleted archived log
archived log file name=D:\ARCHIVELOG\2013_01_13\O1_MF_1_663_8H50S2B9_.ARC RECID=313 STAMP=804602964
Deleted 4 EXPIRED objects



Done.

Find database name. (non-privileged users)

SQL> show user

USER is "SCOTT"

SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
-------------------
NSEDB



SQL> select '&_CONNECT_IDENTIFIER' NAME from dual;
old   1: select '&_CONNECT_IDENTIFIER' NAME from dual
new   1: select 'NSEDB' NAME from dual

NAME
-----
NSEDB

how to change data guard real time apply to delayed apply

########## ON STANDBY: Real time Apply to Delayed apply ##########


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;




########## ON STANDBY: Revoking delayed apply to No delay apply #######

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

--