useful

Oracle Database Upgrade Path Reference List [ID 730365.1]
---------------------------------------------------------

set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10

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

=/mnt/dbbackup/bemsvrmbdb02/billsvr11_1_BILLSVR1_S_527_P_1_T_855345927 tag=WEEKLY_BACKUP
channel ch1: restore complete, elapsed time: 01:25:37
Finished restore at 11-OCT-14
recover database;

Starting recover at 11-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=538 devtype=DISK

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA1/billsvr1/datafile/system.285.860647991'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/11/2014 09:40:28
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 283705 lowscn 5983102243733 found to restore
RMAN-06025: no backup of log thread 2 seq 283704 lowscn 5983102129256 found to restore
RMAN-06025: no backup of log thread 1 seq 440755 lowscn 5983102293248 found to restore
RMAN-06025: no backup of log thread 1 seq 440754 lowscn 5983102264068 found to restore

SELECT codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');

---------------------------------------------------------------------------
APEX_VMV:
------------
Apex
http://192.168.0.141:8080/apex

Apex
http://192.168.0.141:8080/apex/f?p=100:LOGIN_DESKTOP:5252404193772

Internal
admin
India12*

http://192.168.0.75:8080/apex/f?p=4550:1:2504728357192:::::
crm
admin

>CRM admin pwd ?
Vmv12*
-------------------------------------------------------------------------------------------------------------
impdp fintemp/fintemp dumpfile=EXPDP_FINANCE1_CRC_080614.DMP logfile=impdp_crc_fintemp_080615.log directory=expdump remap_schema=finance:fintemp TRANSFORM=oid:n:type


impdp boltest/boltest dumpfile=EXPDP_BOLFINANCE_250715.DMP logfile=impdp_boltest_finance_270515.log directory=expdump remap_schema=finance:boltest TRANSFORM=oid:n:type

SELECT username, PASSWORD, 'alter user ' || username || ' identified by values '|| '''' || PASSWORD || '''' ||';' alter_user_sql
FROM dba_users WHERE username = 'SYSMAN'


select 'alter table ' ||table_name|| ' add ||column_name||,table_name,data_type from user_tab_columns where data_type like 'NVARCHAR2';

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

http://www.dba-oracle.com/t_save_reset_oracle_user_password.htm

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

http://oh-bee-eye.blogspot.in/2012/06/oracle-database-arabic-support.html

----------------------------------------------------------------------------------------
Spool charfix.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;

-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
spool off


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


select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='BOLPROD';


get SID



select
a.sid,
a.serial#,
b.sql_text
from
v$session a,
v$sqlarea b
where
a.sql_address=b.address
and
sid=<>;

--------------------------
SELECT MACHINE, PROGRAM, COUNT(*) FROM V$SESSION GROUP BY MACHINE, PROGRAM;

------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
------------------------------
LOCK:

set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column ctime heading "Seconds"
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request,
l.ctime
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/



-----------------
Total Size

select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ;



select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session where username='ABILLITY_PROD';

DELETE FROM table_name;

or

DELETE * FROM table_name;


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

alter system set audit_trail = db|NONE scope=spfile;

alter system set audit_trail='DB','EXTENDED' scope=spfile;


select value from v$parameter where name = 'audit_file_dest';



SQL> audit all by finance by access;

Audit succeeded.

SQL> audit select table,update table,delect table,insert table by finance by access;
audit select table,update table,delect table,insert table by finance by access
*
ERROR at line 1:
ORA-00956: missing or invalid auditing option


SQL> audit select table,update table,delete table,insert table by finance by access;

Audit succeeded.

SQL> audit execute procedure by finance by access;

Audit succeeded.



SQL> select count(*) from aud$;
COUNT(*)
----------
344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
COUNT(*)
----------
53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.
----------------------------------

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'VMVERP');
END;



SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

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

find . -mtime +0 # find files modified greater than 24 hours ago
find . -mtime 0 # find files modified between now and 1 day ago
# (i.e., in the past 24 hours only)
find . -mtime -1 # find files modified less than 1 day ago (SAME AS -mtime 0)
find . -mtime 1 # find files modified between 24 and 48 hours ago
find . -mtime +1 # find files modified more than 48 hours ago

The following may only work on GNU?

find . -mmin +5 -mmin -10 # find files modified between
# 6 and 9 minutes ago
find / -mmin -10 # modified less than 10 minutes ago


find /path of directory -name "*.log" -mtime +90 -exec ls -ltr {} \; it will list the files older than 3 months

find /path of directory -name "*.log" -mtime +90 -exec rm -f {} \; it will remove the files older than 3 months
---------------------------------------------------------------------------------------------
Database link

CREATE [PUBLIC] DATABASE LINK

CONNECT TO

IDENTIFIED BY

USING '';


create public database link CRCATTENDANCE connect to finlink identified by finlink using 'VMVERP';

create public database link CRCINTERFACE connect to crcinterface identified by crcinterface using 'VMVERP';

create public database link CRCINTERFACE connect to sierra identified by sierra using 'VMVERP';

select * from @

CRC-finuat:-

-- Create database link
create database link CRCATTENDANCE
connect to HRMS identified by hrms
using 'CRCATTENDANCE';


---------------------------------------------------------------------------------------------------
crctest/******** directory=expdump dumpfile=EXPDP_TEST1.DMP logfile=impdp_tables_test1.log remap_schema=crctest:finlink

308195--old
328742--new

TMP_DAILY_TNS


--------------------------------------------------
St courier

9176989263-->

------------------------
------------------------
97910
73561

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

-------------------------------------------------------------
https://www.udacity.com/course/viewer#!/c-ud837/l-4027328704/m-4329550789
https://www.udacity.com/course/developing-android-apps--ud853
https://www.udacity.com/course/advanced-android-app-development--ud855
https://www.udacity.com/course/ux-design-for-mobile-developers--ud849

https://www.udacity.com/course/html5-game-development--cs255

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

Row Locks:


select
sid,
serial#,machine
from
v$session
where
sid in (
select
session_id
from
dba_dml_locks )


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

select
session_id
from
dba_dml_locks
where
name = '';

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

SELECT OBJECT_ID, SESSION_ID, inst_id FROM GV$LOCKED_OBJECT
WHERE OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD') ;


select 'alter system kill session '||''''||a.SESSION_ID||','||b.serial#||',@'||b.inst_id||''';' FROM GV$LOCKED_OBJECT a, gv$session b
where a.session_id=b.sid AND OBJECT_ID=(select object_id FROM dba_objects where object_name='TT_TICKETITEM' and object_type='TABLE' and owner='ABILLITY_PROD')
-----------------------------------------------
-- Utilisation (MB) du FRA
set lines 100
col name format a60

select
name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';


-----------------------------------------------------------------------------
set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

-------------------------------------------------
SQL> select TABLE_NAME from dict where table_name like 'DBA_S%';


DBA_SCHEDULER_PROGRAMS
DBA_SCHEDULER_JOBS
DBA_SCHEDULER_JOB_CLASSES
DBA_SCHEDULER_WINDOWS
DBA_SCHEDULER_PROGRAM_ARGS
DBA_SCHEDULER_JOB_ARGS
DBA_SCHEDULER_JOB_LOG
DBA_SCHEDULER_JOB_RUN_DETAIL
DBA_JOBS


--scheduled job run details--------------------
SQL> set linesize 800
SQL> select LOG_ID,LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,RUN_DURATION,SESSION_ID,CPU_USED,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS;
--------------------------------------------------

--scheduled job logs------

SQL> select LOG_ID,LOG_DATE,OWNER, JOB_NAME,STATUS,USER_NAME,CLIENT_ID,GLOBAL_UID,ADDITIONAL_INFO from DBA_SCHEDULER_JOB_LOG;

-------------------------DBA JOBS---------------


SQL> SELECT LOG_USER,SCHEMA_USER,LAST_DATE, LAST_SEC,THIS_DATE,THIS_SEC, NEXT_DATE, NEXT_SEC,TOTAL_TIME,BROKEN,INTERVAL,FAILURES,WHAT,INSTANCE FROM DBA_JOBS;

----------------------------------------------------SCHEDULED JOBS--------------


SQL> SELECT OWNER,CLIENT_ID, PROGRAM_OWNER, PROGRAM_NAME,SCHEDULE_OWNER,START_DATE END_DATE,ENABLED,STATE,RUN_COUNT,LAST_START_DATE,LAST_RUN_DURATION,
NEXT_RUN_DATE, MAX_RUN_DURATION,COMMENTS FROM DBA_SCHEDULER_JOBS;


SELECT job_name,
destination,
TO_CHAR(actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS actual_start_date,
run_duration,
status,
error#
FROM user_scheduler_job_run_details

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

http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_4.shtml


--------------------------------------------------------------------------
TABLESPACE_NAME,Autoextend Size
***********************************

select
file_name "Filename",
tablespace_name "Tablespace",
round(bytes/1024/1024 ,2) "Current Size (MB)",
autoextensible "Autoextend",
round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where TABLESPACE_NAME='ABILLITY_TABLES';


---------------------------------------------
/bin/find /opt/DB_ARCH/ -type f -mtime +3 -name "arch*.arc" -print -exec rm {} \;' 2>&1 >>/opt/DB_ARCH/archives.deleted.log


-----------------------------------------------------------------------------------------------
To find LAsT DLL:

select object_name,object_type,to_char(created,'DD-MM-YY HH24:MI:SS')created,to_char(last_ddl_time,'DD-MM-YY HH24:MI:SS')last_ddl from user_objects order by last_ddl_time DESC;

-------------------------------------------------
read *Rman Backup Fails with ORA-19510 and ORA-27045 : No space left on device [ID 283681.1]*

RMAN-03009: failure of backup command on ch00 channel at 07/15/2015 10:14:59
ORA-19510: failed to set size of 2407114 blocks for file "/mnt/dbbackup/bemsvrmbdb01/billsvr1_1_BILLSVR1_S_3208_P_1_T_885073430" (blocksize=8192)
ORA-27045: unable to close the file
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
-----------------------------------------------------------
Nagios
Username: nagiosadmin
Password: n30lqk

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

Add datafile to tablespace ASM diskgroup DATAGROUP

SQL> alter tablespace USER-TABLESPACE add datafile '+DATAGROUP' size 10G;
Tablespace altered.
SQL>

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


2 server active node

11gr2 rac

linux or windows

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
9i database:


---------------------------------------------------------------------------------
select * from app_user for update
select * from user_organization_dtl c where c.u_id=0 for update
select * from user_constraints t where t.constraint_name = 'FK_U_ID'
---------------------

iptables -A OUTPUT -d 216.58.220.46 -p tcp --dport 443 -j REJECT

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

Cleanup trace files more than 7 days old

root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/udump/*.trc -mtime +7 -exec rm {} \;
root> find $DBA/$ORACLE_SID/cdump/*.trc -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all trace files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/bdump/*.trc -mtime +7


find *.trc -mtime +30 -exec rm {} \;
-------------------------------------------------------

ASM Commands:-

. oraenv
+ASM

asmcmd

lsct

ls

pwd

du

find +DATA *

ls -s

ls -l
-------------------------------
BI:

http://test.vmvsystem.com:9704/analytics
http://192.168.0.110:9704/analytics Administrator/Administrator

ssue 2: When you access the Dashboard from the OBIEE Page, you may encounter 500 Internal Error: - servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details. I resolved the issue like this: 1. Edit ./oc4j_bi/j2ee/home/applications/analytics/analytics/WEB-INF/web.xml Replace the port as 9703 instead of 9710 2. Go to $ORACLE_BI_HOME/setup/ $run-sa.sh stop wait for 10 seconds $run-saw.sh stop wait for 10 seconds $run-sa.sh start $run-saw.sh start. Now click on the application Dashboard. It will work fine.

/u01/app/oracle/product/OracleBI/setup

/u01/app/oracle/product/OracleBI/oc4j_bi/bin
oc4j -start
---------------------------------------------