Auto shutdown database and copy datafiles to desired location

sqlplus sys/oracle_4me@db11gdev as sysdba @G:\alert\shut.sql


1. This batch file will call the 2nd script (shut.sql).

Below script withh shutdown the database and copy the all datafiles to desired destination.

shutdown immediate

host copy G:\DB11GDEV\DB11GDEV\*.dbf   H:\target

How to Check which tables are STALE

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where owner='&owner' and STALE_STATS='YES';

SOLUTION: If you fount stale tables gather the stats as below.

=== gather stat TABLE====

exec dbms_stats.gather_table_stats (ownname=>'ASHWANI',tabname=>'EMP',estimate_percent=>100);

----gather stat TABLE WITH INDEXES

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ASHWANI' , tabname => 'EMP',cascade => true, estimate_percent => 10,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

--Schema. The Gather Schema Statistics process should be run on a regular basis (weekly at a minimum) and anytime large changes to the data or database take place. 

Example:

exec dbms_stats.gather_schema_stats(ownname => 'ASHWANI', estimate_percent => 25)

How to move older trace files to another location in Window Server.

Step 1) Make a directory called "moved"

D:\>mkdir D:\app\oracle\admin\orcl\Adump\moved

Step 2) Create a batch file mv_trc.bat and copy the below content to this batch file.


D:


cd \app\oracle\admin\orcl\Adump


forfiles /S /D -365 /C "cmd /c move @file D:\app\oracle\admin\orcl\Adump\moved"

----Above forfiles command will move the all the trace files created 365 days before to the said location---


Step 3) Scheduled the task from window scheduler with below details.

Create a Basic Task and should be run as high privelege.

PARAMETERS:

ACTION: C:\Users\ashwani\Desktop\mv_trc.bat

How To Find Users Who Tried To Login To Database.

############################################################################
Below Script Used To Fetched The Report Of Users Who Tried But Failed To Login To Database in CSV Format 
############################################################################

-- -----------------------------------------------------------------------------------
-- Author         : Ashwani Kumar
-- Description  : Failed Login Notification.
-- Requirements : Access to the v$views.
-- Last Modified: 09/Oct/2017
-- -----------------------------------------------------------------------------------

set markup html on
set echo on
set feedback off
set serverout off
set feedback off

define file_time=date
column tm new_value file_time
select 'Notification_DBNAME_'||SYSDATE||'.xls' tm from dual;

spool C:\&file_time

Select name, open_mode, database_role from v$database;

set echo off
prompt ***********************FAILED LOGIN ATTEMPT*************************************

variable KEY1 NUMBER;
exec :KEY1 := '&NUMBER_OF_DAYS';
col action_name format a10
SELECT
  TO_CHAR(TIMESTAMP,'DD-MON-YYYY hh24:mi') LOGIN_DATE,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  DECODE(RETURNCODE,0,'SUCCESS',1017,'FAILED LOGIN',28000,'ACCOUNTLOCKED AFTER FAILED LOGIN') AS Message
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  TIMESTAMP BETWEEN SYSDATE-&days AND SYSDATE and returncode <> 0
ORDER BY TIMESTAMP ASC ;

spool off

set markup html off