pre eod backup and copy to new server

[oracle@garuda-2-17 ~]$ cat /u01/app/script/FCCHOST_FCCLIVE_PREEOD_expdp.sh
#!/bin/bash
. /home/oracle/.bash_profile
export script_dir=/u01/app/script
export DATA_PUMP_DIR=/backup/data_pump_dir
export DEST_DIR=/datastore/FCCLIVE

cd /u01/app/script
datevar=`date +%Y%m%d%H%M`

sed 's/<DATE>/'$datevar'/g' <FCCHOST_FCCLIVE_template.par >FCCHOST_FCCLIVE.par

export ORACLE_SID=FCCLIVE

sqlplus /nolog <<EOF
conn /as sysdba
set feedback off
set head off
set line 200
set pagesize 100
col object_type format a18
col status format a15
select object_type,status,count(*)
from dba_objects
where owner = 'FCCHOST'
group by object_type,status
/
exit;
EOF

expdp \' \/as sysdba \' parfile=FCCHOST_FCCLIVE.par


# Sending mail #
sqlplus /nolog <<EOF
conn /as sysdba
      BEGIN
         UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => 'dba@ababank.com;FlexcubeSupport@ababank.com',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Backup PREEOD '|| $(echo $datevar)||' Completed',
                    message => '<PRE>"$(tail -40 ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}.log)"</PRE>',
                    mime_type => 'text/html;charset=us-ascii');
     END;
/
--create restore point BEFORE_RUN_EOD guarantee flashback database;
exit;
EOF

# Copy to Backup 10.152.1.52 #
scp ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}* oracle@10.152.1.52:${DEST_DIR}

# deleting part #
grep "FCCHOST_FCCLIVE" ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_"$datevar".log|awk -F'/' '{print $4}' > filelist.txt

for filename in $(cat filelist.txt); do
a=`ssh oracle@10.152.1.52 ${DEST_DIR}"/FCCLIVE_dump_exist_check.sh "$filename`
if [ "$a" = "File is existing" ]
then
  rm ${DATA_PUMP_DIR}/$filename
fi
done

touch $script_dir/running_preeod_expdp_done.flg
if [ -f $script_dir/running_preeod_expdp.flg ]
then
  rm $script_dir/running_preeod_expdp.flg
fi

exit 1

Database size

The size of the database is the space the files physically consume on disk. You can find this with: 

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

But not all this space is necessarily allocated. There could be sections of these files that are not used. 

You can find the total space that is used with: 

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

You can break this down by user by running: 

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group  by owner;

Check used and free space:

select

"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(MB)","Free_Space(GB)"

from(

select

(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",

(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"

from dual

);

Configuring Clients for Environments with Both Shared and Dedicated Servers

If a shared server is configured on the server side, and a client connection request arrives when no dispatchers are registered, then the request is processed by a dedicated server process. If you want a particular client always to use a dispatcher, then configure (SERVER=shared) in the CONNECT_DATA section of the connect descriptor. For example:

sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (SERVER=shared)))

If the (SERVER=shared) attribute is configured and a dispatcher is not available, then the client connection request is rejected, and a message is sent to the client.

If the database is configured for a shared server and a particular client requires a dedicated server, then you can configure the client to use a dedicated server in one of the following ways:

• You can configure a network service name with a connect descriptor that contains (SERVER=dedicated) in the CONNECT_DATA section. For example:
  sales=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
    (CONNECT_DATA=
       (SERVICE_NAME=sales.us.example.com)
       (SERVER=dedicated)))
• You can configure the client profile file, sqlnet.ora, with USE_DEDICATED_SERVER=on. This adds (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses.
  Note:
  If USE_DEDICATED_SERVER is set to ON, then existing (SERVER=value) entries in connect descriptors are overwritten with (SERVER=dedicated).

Select object tablespace

select * from dba_segments

where owner = 'FCCHOST'

and lower(segment_name) like '%fc_%'

Privilege

select text from USER_SOURCE

where name = 'REMOVE_EMP'

order by line; 

-Check privilege granted to users

SELECT * FROM DBA_SYS_PRIVS A

JOIN DBA_ROLE_PRIVS B

ON (A.GRANTEE=B.GRANTEE

and A.GRANTEE='SAK')

If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries:

System privileges for a user:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = 'SAK'
UNION
SELECT PRIVILEGE
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = 'SAK'
 ORDER BY 1;

Direct grants to tables/views:

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv
  FROM table_privileges
 WHERE grantee = <theUser>
 ORDER BY owner, table_name;

Indirect grants to tables/views:

SELECT DISTINCT owner, table_name, PRIVILEGE
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY owner, table_name;

Pasted from <http://stackoverflow.com/questions/1298473/how-can-i-list-all-grants-a-user-received>

24down vote

Assuming you want to list grants on all objects a particular user has received: select * from all_tab_privs_recd where grantee = 'SAK'

Pasted from <http://stackoverflow.com/questions/1298473/how-can-i-list-all-grants-a-user-received>

select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||RP.GRANTEE
from DBA_ROLE_PRIVS RP join ROLE_TAB_PRIVS RTP
on (RP.GRANTED_ROLE = RTP.role) 
where (OWNER in ('SAK') --Change User Name
   OR RP.GRANTEE in ('SAK')) --Change User Name
and RP.GRANTEE not in ('SYS', 'SYSTEM')
;

Kill rman

Kill rman

Friday, August 10, 2018

4:30 AM

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';

SQL> alter system kill session '592,12' immediate;

Virtual Private Database (VPD) Demo.

Security is very important in today technology world. for example in the company, the CEO doesn't want the employees working in the Department 20 see the salary of the employees working in Department 10. especially End Of Year..hehe..you know that. So in this demo I will show you how to implement the Virtual Private Database(VPD) in Oracle Database. using this method we can restrict the condition and archive this goal. I will create a policy to let the employees to see only his/her own Department Data(Deptno 20).

1. Before apply the policy

2. Creating the policy function.

3. Creating the security policy. (on user sys)

4. Verifying the policy

We can see that user can see only data of Department num 20.

5. To drop the policy

EXECUTE dbms_rls.drop_policy('HR','EMPLOYEES','EMP_DEPT_20');