Search This Blog

Wednesday, 5 December 2018

python_exapmle_bank_login_check

'''
Date: 05-Dec-2018
Name: Raja
Des: 1. Validate the user name and pass
        2. Test sys.argv for the bank login with Dictionary concept
'''

import sys

if (len(sys.argv) !=2):
    sys.exit(1)
bank_name=sys.argv[1]

print ("welcome to ",bank_name)
user_dt={'ganesh':'Password','vinayaka':'Password'}
user_name=input("enter the username : \n")
pwd=input("enter the pass : \n")

user_lst=user_dt.keys()

if user_name in user_lst:
    if (pwd ==user_dt[user_name]):
        print("login sucess")
    else:
        print("pass invalid")
        sys.exit(1)
else:
    print("invalid user")
    sys.exit(1)
print("hello Mr. ",user_name)

Sunday, 21 October 2018

AWR baseline:

SYS@TESH996P SQL> select min(snap_id), min(sample_time), max(snap_id),max(sample_time) from dba_hist_active_sess_history where sample_time < to_date('20-JUL-18','DD-MON-YY');

MIN(SNAP_ID) MIN(SAMPLE_TIME)
------------ ---------------------------------------------------------------------------
MAX(SNAP_ID) MAX(SAMPLE_TIME)
------------ ---------------------------------------------------------------------------
       72277 18-JUL-18 11.00.39.789 PM
       72326 19-JUL-18 11.59.48.043 PM


1 row selected.

To know the retenetion:
------------------------------------
SYS@TESH996P SQL> select DBID,RETENTION,SNAP_INTERVAL from dba_hist_wr_control;

      DBID RETENTION                                SNAP_INTERVAL
---------- ---------------------------------------- --------------------------------------------------
1857388495 +00090 00:00:00.0                        +00000 00:30:00.0

1 row selected.

Cretae baseline:
---------------------

exec DBMS_WORKLOAD_REPOSITORY.create_baseline(72277,72326,'Test Baseline');

PL/SQL procedure successfully completed.


To view the baseline:
-------------------------


SYS@TESH996P SQL> SELECT baseline_id,baseline_name,baseline_type,start_snap_id,end_snap_id FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                BASELINE_TYPE                           START_SNAP_ID END_SNAP_ID
----------- ------------------------------------------------------------ --------------------------------------- ------------- -----------
          8 Test Baseline                                                STATIC                                          76691       76738
          1 EOD BASELINE 13/AUG/2018 MON                                 STATIC                                          73479       73492


Delete baseline:
-------------------

SYS@TESH996P SQL> exec DBMS_WORKLOAD_REPOSITORY.drop_baseline(BASELINE_NAME => 'Test Baseline');

PL/SQL procedure successfully completed.

SYS@TESH996P SQL> SELECT baseline_id,baseline_name,baseline_type,start_snap_id,end_snap_id FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME                                                BASELINE_TYPE                           START_SNAP_ID END_SNAP_ID
----------- ------------------------------------------------------------ --------------------------------------- ------------- -----------
          1 EOD BASELINE 13/AUG/2018 MON                                 STATIC                                          73479       73492

Friday, 19 October 2018

tablespace details

pct_free.sql
==================================
set echo off
set lines 999 pages 999;
col tablespace for a22;
col "Used % " for a10;

select a.tablespace_name "Tablespace",
        a.total_size_mb   "Total_Size_MB",
        (a.total_size_mb - b.free_size_mb) Used_Size_MB,
         b.free_size_mb "Free_Size_MB",
         round(((a.total_size_mb - b.free_size_mb)*100/a.total_size_mb), 2)        "Pct_Used",
      round(free_size_mb*100/total_size_mb, 2) "Pct_Free",
          round(b.max_free, 2) "Max_Free",
      round(c.max_next, 2) "Max_Next",
        d.status,
      TO_CHAR(NVL((a.bytes - NVL(b.bytes, 0)) / a.maxbytes * 100, 0), '990.00')  "Used % "
      from   ( select tablespace_name, round(sum(bytes)/1024/1024, 2) total_size_mb,
    decode(sum(decode(autoextensible,'YES',1,0)),0,'NO','YES') auto_extension,sum(bytes) bytes,sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
                 from   dba_data_files group  by tablespace_name ) a, ( select tablespace_name, round(sum(bytes)/1024/1024, 2) free_size_mb,
                                                     round (max( bytes) / ( 1024 * 1024 ), 2 )max_free,sum(bytes) bytes from   dba_free_space
                group  by tablespace_name ) b, ( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next from   dba_segments
                                                 group  by tablespace_name ) c, dba_tablespaces d
where  d.tablespace_name = a.tablespace_name(+)
   and    d.tablespace_name = b.tablespace_name(+)
and    d.tablespace_name = c.tablespace_name(+)
   and    a.tablespace_name is not null
order by 5;

==============================================================
tbs.sql
------------------------------------
set echo off
set line 200
col file_name for a100
select file_id, file_name, bytes/1024/1024 "mb" from dba_data_files where tablespace_name='&tbs';


prompt >>>>> Please check mount point size before resize datafile
prompt
prompt >>>>> HINTS for resize datafile command
prompt =======================================================================
prompt alter database datafile <file_id> resize ...m;
prompt alter tablespace tablespace_name add datafile '...' size ...m;
prompt

Monday, 1 October 2018

Database SID based on OS Process ID


Database SID based on OS Process ID


col SPID for a20;
col OSUSER for a30;
col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

=============================================
col SPID for a20;
col OSUSER for a30;
col sid format 999999
col username format a20
col CLIENTPROGRAM for a20;
col MODULE for a30;
col MACHINE for a30;

select b.spid,a.sid, a.serial#,a.username, a.osuser,
a.PROGRAM clientprogram, a.module, a.machine
from v$session a, v$process b
where a.paddr= b.addr
and b.spid in ='&spid'
order by b.spid;

Friday, 20 July 2018

UNNAMED datafiles alerts at Standby:


Error on standby due to lack of space datafile has been created logically on standby with "UNNAMED",but physically file has not created:
---------------------------------------------------------------------------------------------------------------------

Errors in file /oracle/base/diag/rdbms/owsh120a/OWSH120A1/trace/OWSH120A1_pr00_29534.trc:
ORA-01111: name for data file 50 is unknown - rename to correct file
ORA-01110: data file 50: '/oracle/product/11204/db_8/dbs/UNNAMED00050'

STANDBY:
==========
SQL> select FILE#,NAME from v$datafile  where FILE#=50;
     FILE# NAME
---------- ------------------------------------------------------------
        50 /oracle/product/11204/db_8/dbs/UNNAMED00050

SQL> show parameter standby_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO


SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database create datafile '/oracle/product/11204/db_8/dbs/UNNAMED00050' as '/oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> select FILE#,NAME from v$datafile  where FILE#=50;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
        50 /oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf


=> Then start MRP process and check DG sync issue:


SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1     120985        345    1048576

6 rows selected.

Standby archive seq:

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                 120984                120984          0
         2                 111745                111745          0


=> Then resize the datafile on primary to fit datafile in oradata.And then rename the datafile to orginal location in standby:

1.Cancel the mrp before rename:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


2.convert standby file management to Manual:


SQL> alter system set standby_file_management=manual;


System altered.


3.Move datafile to orginal location from archivepool:


orcldb@ah4hi044:~> mv /oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf
orcldb@ah4hi044:~> ll /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf
-rw-r----- 1 orcldb oinstall 3221258240 Jul 20 03:41 /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf

4.Then rename the datafile in standby database :

SQL> alter database rename file  '/oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf' to '/oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> select FILE#,NAME from v$datafile  where FILE#=50;

     FILE# NAME
---------- ---------------------------------------------------------------------
        50 /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf














Monday, 16 July 2018

Recover block from standby if block corrupted on primary:

With the help:

https://shivanandarao-oracle.com/2014/05/03/recovering-a-corruptedlost-datafile-on-primary-database-from-the-standby-database/


To find corrupted block:
 ================================================================
SYS@TWSH722P SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
         6          2        126                  0 ALL ZERO
         6        128         18                  0 ALL ZERO
         6        147        109                  0 ALL ZERO
         6      20736        128                  0 ALL ZERO

4 rows selected.

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

Standby:
=======
backup as copy datafile 6 format '/oracle/TWSH722P/archive/temp_TWSH722P/o_TWSH722P_USERS_1.dbf';

scp (to archive pool)

Primary:
======
 alter database datafile 6 offline;

 rman target /

 catalog datafilecopy '/oracle/TWSH722P/archive/o_TWSH722P_USERS_1.dbf';
 run {
 SET NEWNAME FOR DATAFILE '/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf' TO '/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf';
 }
 restore datafile 6;
 recover datafile 6;

  alter database datafile 6 online;

Note: Here i have used set newname bcz in db level we are not maintaing db_create_file_dest parameter .



========================================================================
[orcldb@dcplnx22981091 oradata01]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 16 12:35:04 2018

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

connected to target database: OWSH722P (DBID=1734500440)

RMAN> catalog start with '/oracle/TWSH722P/archive/backup_satndby';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/TWSH722P/archive/backup_satndby

List of Files Unknown to the Database
=====================================
File Name: /oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

RMAN>  run {
 SET NEWNAME FOR DATAFILE '/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf' TO '/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf';
 }2> 3>

executing command: SET NEWNAME

RMAN> restore datafile 6;

Starting restore at 16-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=428 device type=DISK

channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=96458 STAMP=981635722 file name=/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf
destination for restore of datafile 00006: /oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf RECID=0 STAMP=0
Finished restore at 16-JUL-18

RMAN> recover datafile 6;

Starting recover at 16-JUL-18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 16-JUL-18

RMAN>



RMAN> VALIDATE DATAFILE 6 BLOCK 2,128,147,20736;

Starting validate at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              0            4               13087849105005
  File Name: /oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2
  Other      0              2

Finished validate at 16-JUL-18