Cut Oracle Alert logfile
from line no 46267 To line no 47000
sed -n '46267,47000p' alert_CAR1.log >alert-anuj
from line no 46267 To line no 47000
sed -n '46267,47000p' alert_CAR1.log >alert-anuj
Tuesday, 1 May 2012
Monday, 30 April 2012
How To shutdown The Oracle Database
Oracle database shutdown
alter system checkpoint;
Shutdown abort
Startup restrict
Shutdown immediate
Startup
alter system checkpoint;
Shutdown abort
Startup restrict
Shutdown immediate
Startup
Tuesday, 17 April 2012
Oracle Job metadata
How to find out Oracle Scheduler /Job text
Oracle Job Text
on dbms_metadata.get_ddlORA-31603: object of type PROCOBJ not found in schema "SYS"
select dbms_metadata.get_ddl('PROCOBJ','FILE_WATCHER','SYS') from dual;
ERROR:
ORA-31603: object "FILE_WATCHER" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','MY_TEST_JOB', 'SCOTT') AS job_def FROM dual;
SELECT dbms_metadata.get_ddl('PROCOBJ','JOB_DEFAULT_USER', 'SCOTT') from dual;
==========================
for sys Job Metadata
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_PROC1','SYS') from dual;
ERROR:
ORA-31603: object "ANUJ_PROC1" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
Created a new user ...
SQL> create user C##TESTUSER identified by TESTUSER;
User created.
SQL> grant dba to C##TESTUSER ;
SQL> exec dbms_scheduler.copy_job('SYS.ANUJ_PROC1','C##TESTUSER.ANUJ_TEST');
PL/SQL procedure successfully completed.
set long 500
SQL> select dbms_metadata.get_ddl('PROCOBJ','ANUJ_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','ANUJ_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"ANUJ_TEST"',
job_type=>'STORED_PROCEDURE', job_action=>
'SYS.ANUJ_PROC'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-FEB-2018 05.52.17.000000000 AM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=MINUTELY;INTERVAL=2'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=>
'Testing...'
);
sys.dbms_scheduler.set_attribute('"ANUJ_TEST"','NLS_ENV','NLS_LANGUAGE=''AMERICA
N'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NL
S_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MON-
RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.
SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=
''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR''
NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NC
HAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set serveroutput on
DECLARE
v_output CLOB := NULL;
v_owner VARCHAR2 (30) := 'SYS';
v_object_name VARCHAR2 (30) := 'ANUJ_PROC';
BEGIN
-- Note, we don't search for package bodies. We will extract the body
-- along with the package spec.
DBMS_OUTPUT.put_line ('Database DDL For Selected Objects Report');
FOR dd IN (SELECT owner, object_name, object_type FROM dba_objects
WHERE owner LIKE v_owner
AND object_name LIKE v_object_name
AND object_type IN ('PROCEDURE', 'PACKAGE', 'TRIGGER', 'FUNCTION'))
LOOP
SELECT DBMS_METADATA.get_ddl (dd.object_type, dd.object_name, dd.owner) INTO v_output FROM DUAL;
BEGIN
DBMS_OUTPUT.put_line (v_output);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Unable to print code for object');
DBMS_OUTPUT.put_line ( dd.owner
|| '.'
|| dd.object_name
|| ' type: '
|| dd.object_type
);
END;
END LOOP;
END;
/
Database DDL For Selected Objects Report
CREATE OR REPLACE NONEDITIONABLE PROCEDURE "SYS"."ANUJ_PROC" AS
BEGIN
insert
into SYS.TEST values ( 'AAAAAAA',sysdate) ;
commit;
END;
==================
2. Example !!!!
set linesize 300 pagesize 500
COL log_id FORMAT 9999 HEADING 'Log#'
COL log_date FORMAT A32 HEADING 'Log Date'
COL owner FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'Job'
COL status FORMAT A10 HEADING 'Status'
COL actual_start_date FORMAT A32 HEADING 'Actual|Start|Date'
COL error# FORMAT 999999 HEADING 'Error|Nbr'
TTITLE 'Scheduled Tasks:'
select
log_date,
job_name,
status,
req_start_date,
actual_start_date,
run_duration
from dba_scheduler_job_run_details
where 1=1
and job_name like '%ANUJ%'
-- status <> 'SUCCEEDED'
order by actual_start_date;
job error
log# LOG_DATE job name step name step status code additional information
------- ----------------------------------- ------------------------------ -------------------- ------------ -------- ----------------------------------------
81068 10-FEB-18 04.00.01.149900 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
76472 09-FEB-18 04.00.00.950431 PM -05:00 APEX_GATHER_JOB FAILED 6550 ORA-06550: line 1, column 804:
PLS-00201: identifier 'APEX_050100' must
be declared
ORA-06550: line 1, column 763:
PL/SQL: Statement ignored
This job is failing
APEX_GATHER_JOB
set linesize 200 pagesize 500
col OWNER for a20
col OBJECT_NAME for a20
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='APEX_GATHER_JOB';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- -----------------------
SYS APEX_GATHER_JOB JOB
exec dbms_scheduler.copy_job('SYS.APEX_GATHER_JOB','C##TESTUSER.APEX_GATHER_JOB_TEST');
set long 500
select dbms_metadata.get_ddl('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER') from dual;
DBMS_METADATA.GET_DDL('PROCOBJ','APEX_GATHER_JOB_TEST','C##TESTUSER')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"APEX_GATHER_JOB_TEST"',
job_type=>'PLSQL_BLOCK', job_action=>
'begin dbms_stats.gather_schema_stats(ownname=>"APEX_050100", estimate_percent=>
10, cascade=>TRUE, no_invalidate=> TRUE); end;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('05-FEB-2018 04.00.00.000000000 PM -05:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=DAILY; INTERVAL=1'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE
====
Another example !!!
create table scott.emp_job as select * from scott.emp;
Table created.
CREATE OR REPLACE PROCEDURE scott.emp_log as
begin
delete from scott.emp_job where HIREDATE < sysdate -15;
end emp_log;
Procedure created.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'SCOTT_EMP_LOG_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN scott.emp_log; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
enabled => TRUE); ----<<<<
END;
/
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a20
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB';
OWNER JOB_NAME ENABLED
-------------------- -------------------- ---------------
SYS SCOTT_EMP_LOG_JOB TRUE
--- for sys job !!!
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB', 'SYS') AS job_def FROM dual;
ORA-31603: object "SCOTT_EMP_LOG_JOB" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6465
ORA-06512: at "SYS.DBMS_METADATA", line 9202
ORA-06512: at line 1
======
copy the Job
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
exec dbms_scheduler.copy_job('SYS.SCOTT_EMP_LOG_JOB','SCOTT.SCOTT_EMP_LOG_JOB_DUP');
PL/SQL procedure successfully completed.
col OWNER for a20
col JOB_NAME for a30
col enabled for a15
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='SCOTT_EMP_LOG_JOB_DUP';
OWNER JOB_NAME ENABLED
-------------------- ------------------------------ ---------------
SCOTT SCOTT_EMP_LOG_JOB_DUP FALSE
SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PROCOBJ','SCOTT_EMP_LOG_JOB_DUP', 'SCOTT') AS job_def FROM dual;
JOB_DEF
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"SCOTT_EMP_LOG_JOB_DUP"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN scott.emp_log; END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('09-DEC-2021 03.38.43.238324000 AM -05:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0; bysecond=0;'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"SCOTT_EMP_LOG_JOB_DUP"','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' NLS_NUMERI
C_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''dd-mm-yy hh24:mi'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.MI.SSXFF AM'' NLS_TIMEST
AMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY
'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
COMMIT;
END;
set linesize 500 pagesize 300
col JOB_ACTION for a40
col SCHEDULE for a30
col SCHEMA_NAME for a25
col LAST_START_DATE for a25
col START_DATE for a25
select owner as schema_name,
job_name,
job_style,
case when job_type is null
then 'PROGRAM'
else job_type end as job_type,
start_date,
case when repeat_interval is null
then schedule_name
else repeat_interval end as schedule,
last_start_date,
next_run_date,
state,
case when job_type is null
then program_name
else job_action end as job_action
from dba_scheduler_jobs
where 1=1
--and JOB_NAME='SCOTT_EMP_LOG_JOB'
order by owner, job_name;
SCHEMA_NAME JOB_NAME JOB_STYLE JOB_TYPE START_DATE SCHEDULE LAST_START_DATE NEXT_RUN_DATE STATE JOB_ACTION
------------------------- ------------------------------ ----------------- ---------------- ------------------------- ------------------------------ ------------------------- --------------------------------------------------------------------------- --------------- ----------------------------------------
SYS SCOTT_EMP_LOG_JOB REGULAR PLSQL_BLOCK 09-DEC-21 03.38.43.238324 freq=hourly; byminute=0; bysec 09-DEC-21 04.00.00.527244 09-DEC-21 05.00.00.533142 AM -05:00 SCHEDULED BEGIN scott.emp_log; END;
AM -05:00 ond=0; AM -05:00
Oracle Table Size
Oracle Table size Oracle Object size How to find oracle Object size
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('SYS', 'COL$', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
286 304 0
Saturday, 14 April 2012
How to create oracle password file
create password file
create Oracle password file
su - oracle
-bash-3.2$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper),504(asmadmin)
-bash-3.2$ $ORACLE_HOME/bin/orapwd file=/u01/oracle/product/11.2.0/db_1/dbs/orapwvihaan password=vihaan entries=5
[oracle@wcp12cr2 dbs]$ $ORACLE_HOME/bin/orapwd help=yUsage: orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n>dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>syskm=<y/n> delete=<y/n> input_file=<input-fname>Usage: orapwd describe file=<fname>wherefile - name of password file (required),password - password for SYS will be promptedif not specified at command line.Ignored, if input_file is specified,entries - maximum number of distinct DBA (optional),force - whether to overwrite existing file (optional),asm - indicates that the password to be stored inAutomatic Storage Management (ASM) disk groupis an ASM password. (optional).dbuniquename - unique database name used to identify databasepassword files residing in ASM diskgroup only.Ignored when asm option is specified (optional),format - use format=12 for new 12c features like SYSBACKUP, SYSDG andSYSKM support, longer identifiers, etc.If not specified, format=12 is default (optional),delete - drops a password file. Must specify 'asm','dbuniquename' or 'file'. If 'file' is specified,the file must be located on an ASM diskgroup (optional),sysbackup - create SYSBACKUP entry (optional and requires the12 format). Ignored, if input_file is specified,sysdg - create SYSDG entry (optional and requires the 12 format),Ignored, if input_file is specified,syskm - create SYSKM entry (optional and requires the 12 format),Ignored, if input_file is specified,input_file - name of input password file, from where old userentries will be migrated (optional),describe - describes the properties of specified password file(required).There must be no spaces around the equal-to (=) character.
[FORMAT = {12.2|12}] old format
On filesystem SID $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle format=12
$ORACLE_HOME/bin/orapwd describe file=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwrac1 Password file Description : format=12.2
another example
[oracle@wcp12cr2 ~]$ $ORACLE_HOME/bin/orapwd describe file=/oracle/db/ohome/dbs/orapworcl Password file Description : format=12 ignorecase=N
Now test the connection ....
-bash-3.2$ sqlplus 'sys/vihaan@prim as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 14 11:27:45 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
prim =
prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.129)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = vihaan)
)
)
===============================================================SYS Password Management with RAC and Data Guard (Doc ID 1267828.1)
===
Then ORA-1031 error will be reported in primary alert log:
Wed Nov 17 15:33:48 2010
Error 1031 received logging on to the standby
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.
Errors in file /u01/diag/rdbms/tl/<PRIM_INST_NAME1>/trace/<PRIM_INST_NAME1>_arc3_24629.trc:
Solution:
1. For password stored on the file system:a. Simply copy (scp) the orapw<SID> file from any of the primary instance to all standby instances:
$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME1>:<$ORACLE_HOME>/dbs/
$ scp $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> oracle@<STBY_HOSTNAME2>:<$ORACLE_HOME>/dbs/
b. Rename the password file on all standby nodes to match the standby SID.on <STBY_HOSTNAME1>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME1>
on <STBY_HOSTNAME2>:
mv $ORACLE_HOME/dbs/<PRIM_PWD_INST_NAME1> $ORACLE_HOME/dbs/<STBY_PWD_INST_NAME2>2. For password stored on the ASM diskgroup:
a. On primary, run srvctl config database -d <db_unique_name> to locate the password file
$ srvctl config database -d <DB_NAME>
Password file: +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217b. using asmcmd to copy the file to file system
asmcmd> pwcopy +<DG_NAME>/<DB_NAME>/PASSWORD/<PWD_DB_NAME>.400.940505217 /tmpc. scp the password file to standby hosts
$ scp /tmp/<PWD_DB_NAME>.400.940505217 oracle@<standbyhost>:/tmpd. restore the password file to ASM diskgroup on standby
asmcmd> pwcopy /tmp/<PWD_DB_NAME>.400.940505217 +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DGe. modify the password location for standby
$ srvctl modify database -d <db_unique_name> -pwfile +<DG_NAME>/<DB_NAME>DG/PASSWORD/<PWD_DB_NAME>DG
3. It is not required to shutdown/restart any of the primary or standby instances.
4. Log shipping will resume automatically after this.
If not, alter parameter log_archive_dest_state_<n> to defer, then enable.SQL> alter system set log_archive_dest_state_2=defer scope=both sid='*';
SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';Grid ASM ASMCMD [+DATA/IBRAC/PWDFILE] > pwcreate --dbuniquename ibrac +DATA/IBRAC/PWDFILE/pwdibrac Very_Complex!1ASMCMD ASMCMD-9465: WARNING: passing password on command line is deprecated ASMCMD [+DATA/IBRAC/PWDFILE] > ls -s Block_Size Blocks Bytes Space Name 512 12 6144 0 pwdibrac => +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 ASMCMD [+DATA/IBRAC/PWDFILE] > ?? ASMCMD [+DATA/IBRAC/PWDFILE] > pwget --dbuniquename ibrac PRCD-1229 : An attempt to access configuration of database ibrac was rejected because its version 12.2.0.1.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/12.2.0/dbhome_1. A +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 from database srvctl modify database -d ibrac -pwfile +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 srvctl config database -d ibrac|grep -i password Password file: +DATA/IBRAC/PASSWORD/pwdibrac.3840.1145270461 now you can change any password !!! SQL> alter user sys identified by sys ; :) User altered.sqlplus sys/sys@'ibrac2' as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 19 10:49:34 2023 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sat Aug 19 2023 10:49:29 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Productionanother example
for password file[oracle@oragrid ~]$ srvctl config database -d v19mpc -vDatabase unique name: v19mpcDatabase name: v19mpcOracle home: /u01/app/oracle/product/19.0.0/dbhome_1Oracle user: oracleSpfile: +DATA/V19MPC/PARAMETERFILE/spfile.273.1236175303Password file: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< no fileDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICDisk Groups: DATAServices:OSDBA group:OSOPER group:Database instance: v19mpc[oracle@oragrid ~]$ cd $ORACLE_HOME[oracle@oragrid dbhome_1]$ cd dbs[oracle@oragrid dbs]$ ls -ltrtotal 16-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora-rw-r-----. 1 oracle asmadmin 24 Jun 17 13:07 lkV19MPC-rw-r-----. 1 oracle oinstall 2048 Jun 17 13:10 orapwv19mpc-rw-rw----. 1 oracle asmadmin 1544 Jun 25 09:14 hc_v19mpc.dat[oracle@oragrid dbs]$ pwd/u01/app/oracle/product/19.0.0/dbhome_1/dbs[oracle@oragrid dbs]$ iduid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),3003(asmadmin),3004(asmdba),54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023as a grid user !!![oracle@oragrid dbs]$ logout[root@oragrid ~]# su - grid[grid@oragrid ~]$ asmcmd -p[grid@oragrid ~]$ cat /etc/oratab#Backup file is /u01/app/grid/crsdata/oragrid/output/oratab.bak.oragrid.grid line added by Agent## This file is used by ORACLE utilities. It is created by root.sh# and updated by either Database Configuration Assistant while creating# a database or ASM Configuration Assistant while creating ASM instance.# A colon, ':', is used as the field terminator. A new line terminates# the entry. Lines beginning with a pound sign, '#', are comments.## Entries are of the form:# $ORACLE_SID:$ORACLE_HOME:<N|Y>:## The first and second fields are the system identifier and home# directory of the database respectively. The third field indicates# to the dbstart utility that the database should , "Y", or should not,# "N", be brought up at system boot time.## Multiple entries with the same $ORACLE_SID are not allowed.###+ASM:/u01/app/19.0.0/grid:N # line added by Agent+ASM:/u01/app/19.31.0/grid:N # line added by Agentv19mpc:/u01/app/oracle/product/19.0.0/dbhome_1:N # line added by Agent[grid@oragrid ~]$ . oraenvORACLE_SID = [grid] ? +ASMThe Oracle base has been set to /u01/app/grid[grid@oragrid ~]$ asmcmd -pASMCMD [+] > pwd+ASMCMD [+] > pwcopy --dbuniquename v19mpc '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc' '+DATA'copying /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc -> +DATA/orapwv19mpcASMCMD [+] >ASMCMD [+] > pwcopy --dbuniquename v19mpc '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc' '+DATA'copying /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwv19mpc -> +DATA/orapwv19mpcASMCMD [+] > pwget --dbuniquename v19mpc+DATA/orapwv19mpcASMCMD [+] >srvctl modify database -d v19mpc -pwfile +DATA/orapwv19mpcsu - oracleorcl /oracle/db/ohome[oracle@oragrid ~]$ setsid1- +ASM2- v19mpcSelect the Oracle SID with given number [1]:2Your profile configured for v19mpc with information below:The Oracle base remains unchanged with value /u01/app/oracle[oracle@oragrid ~]$ srvctl modify database -d v19mpc -pwfile +DATA/orapwv19mpc[oracle@oragrid ~]$ srvctl config database -d v19mpc -vDatabase unique name: v19mpcDatabase name: v19mpcOracle home: /u01/app/oracle/product/19.0.0/dbhome_1Oracle user: oracleSpfile: +DATA/V19MPC/PARAMETERFILE/spfile.273.1236175303Password file: +DATA/orapwv19mpcDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICDisk Groups: DATAServices:OSDBA group:OSOPER group:Database instance: v19mpc[oracle@oragrid ~]$
Password file Description : format=12.2Data Guard Physical Standby Changing the SYS password when a broker configuration exists (Doc ID 1199943.1)
Sunday, 1 April 2012
Oracle Temp space information
Oracle Space usage
Oracle Temp
Oracle Temp
https://anuj-singh.blogspot.com/2012/09/oracle-temp-tablespace-usage-info.html
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 29 0 29
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
break on report
compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128
spool sort_use
/
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
SELECT s.username, u."USER", u.tablespace,u.contents,u.extents, u.blocks,a.sql_text,u.sqladdr,u.sqlhash
FROM v$session s,v$sort_usage u,v$sql a
WHERE s.SADDR = u.SESSION_ADDR
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 29 0 29
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
break on report
compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128
spool sort_use
/
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;
SELECT s.username, u."USER", u.tablespace,u.contents,u.extents, u.blocks,a.sql_text,u.sqladdr,u.sqlhash
FROM v$session s,v$sort_usage u,v$sql a
WHERE s.SADDR = u.SESSION_ADDR
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
Tuesday, 27 March 2012
Oracle Tablespace Monitoring Script
Oracle Tablespace Monitoring
from this Blog
http://blog.csdn.net/flighting/article/details/1119253
from this Blog
http://blog.csdn.net/flighting/article/details/1119253
Oracle Tablespace Monitoring Script
Oracle Tablespace Monitoring
from this Blog
http://blog.csdn.net/flighting/article/details/1119253
-- List tablespaces
- space allocated
- space used
- %free (without maxbytes)
- Autoextend Y|N|Y/N
- Sum of maxbytes
- increment (in Mb) 10/100 or 0/10/.... if more than 2 datafiles with different increment size
- Largest free extent
- Space management (DICT MAN -> Dictionnary Manual| ASSM | LMTS)
- Number of files
- T: tempfile | D: datafile
- %Used (using maxbytes in calcul)
set linesize 145
set pagesize 60
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off
prompt
prompt -- ----------------------------------------------------------------------- ---
prompt -- Tablespaces ---
prompt -- ----------------------------------------------------------------------- ---
prompt
Set Heading Off
Set Termout Off
create or replace function show_autoext_tbs(P_Tablespace_Name In Varchar2) return varchar2
Is
--
Cursor CX Is
Select distinct Substr(autoextensible,1,1)
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct Substr(autoextensible,1,1)
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := Replace(Substr(W_Texte, 2, 4), ' ', '/');
--
Return W_Texte;
--
End;
/
-- show error
Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint
Select value Var_DB_BLOCK_SIZE
from v$parameter
where Upper(name) = Upper ('db_block_size')
;
Set Termout On
Set Heading On
create or replace function show_increment_tbs(P_Tablespace_Name In Varchar2, P_Db_Block_Size In Number) return varchar2
Is
--
Cursor CX Is
Select distinct To_Char(round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_data_files
where tablespace_name=P_Tablespace_Name
Union
Select distinct To_Char(Round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
from dba_temp_files
where tablespace_name=P_Tablespace_Name
;
--
W_Texte Varchar2(20);
W_Temp Varchar2(20);
--
Begin
--
W_Texte := Null;
--
Open CX;
Loop
Fetch CX Into W_Temp;
Exit When CX%notfound;
W_Texte := W_Texte|| ' ' || W_Temp;
End Loop;
Close CX;
--
W_Texte := W_Texte||' ';
W_Texte := Replace(W_Texte,' ', '/');
W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
If Instr(W_Texte, '/', 1,2) != 0 then
W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
End If;
--
Return W_Texte;
--
End;
/
Set Termout On
Set Heading On
clear breaks
break on contents -
skip 1
compute Sum of alloc used maxbytes nfree nbfrag on contents
column tblsp format a22 wrap heading "Tablespace|Name"
column Alloc format 99999999 heading "Alloc|(Mb)"
column file_name format a45 wrap heading "DataFile Name"
column rfno format 999 heading "R.|Fno"
-- column inc format 9999999 heading "Incr|(Mb)"
column inc format a15 heading "Incr|(Mb)"
column maxbytes format 9999999 heading "Sum Max|(Gb)"
column user_bytes format 999999 heading "Userbytes|(Mb)"
column free format 999999 heading "Free|(Mb)"
column nfree format 99999 heading "Max|Free|(Mb)"
column used format 99999999 heading "Used|(Mb)"
column pused format 990.9 heading "%Used"
column maxpused format 990.9 heading "Max|%Used"
column contents format a5 heading "Cont."
column fragmax format 99999 heading "Larg.|Free|Ext|(Mb)"
column nbfrag format 99999 heading "Nb|frag"
column nbfile format 99 heading "Nb|F."
column autoext format a3 wrap heading "Auto|Ext."
column ftype format a2 wrap heading "F.|T."
column ext_manage format a5 wrap heading "Ext.|M."
column seg_sp_mg format a2 wrap heading "S.|M."
column seg_space_mg format a7 wrap heading "Space|Mg."
set linesize 145
Select
dt.contents contents
, dt.Tablespace_Name tblsp
, df.alloc alloc
, df.alloc - nvl(fs.free,0) used
, ((df.alloc - nvl(fs.free,0))* 100)/df.alloc pused
, dt.autoext autoext
, df.summaxbytes/1024 maxbytes
, dt.Inc inc
-- , nvl(fs.free,0) free
-- , fs.nbfrag
, Nvl(fs.fragmax,0) fragmax
-- , dt.ext_manage
-- , dt.seg_sp_mg seg_sp_mg
, Decode(dt.ext_manage||' '||dt.seg_sp_mg,'LOC MA', ' LMTS', 'LOC AU', ' ASSM',dt.ext_manage||' '||dt.seg_sp_mg) seg_space_mg
, df.nbf nbfile
, df.ftype ftype
-- , decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0)) nfree
, 100 - ((decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0))) * 100 / decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))) maxpused
from
( select
Substr(contents,1,4) contents
, tablespace_name
, Substr(extent_management,1,3) ext_manage
, Substr(segment_space_management,1,2) seg_sp_mg
, show_autoext_tbs(tablespace_name) autoext
, show_increment_tbs (tablespace_name, &Var_DB_BLOCK_SIZE) inc
from dba_tablespaces
) dt
, ( select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'D' ftype
from Dba_Data_Files
Group
By tablespace_name
Union
select
Sum(Bytes/1024/1024) alloc
, Max(MaxBytes/1024/1024) maxbytes
, Sum(MaxBytes/1024/1024) summaxbytes
, count(*) nbf
, tablespace_name
, 'T' ftype
from Dba_Temp_Files
Group
By tablespace_name
) df
, (select
Nvl(Sum(nvl(bytes/1024/1024,0)),0) free
, Nvl(max (bytes)/1048576,0) fragmax
, tablespace_name
, count(*) nbfrag
from (Select distinct tablespace_name, bytes from dba_free_space)
Group By tablespace_name
order by max (bytes)/1048576 desc
) fs
Where df.tablespace_name = fs.tablespace_name (+)
and df.tablespace_name = dt.tablespace_name
Group
By dt.contents
, dt.Tablespace_Name
, df.alloc
, df.maxbytes
, df.summaxbytes
, df.nbf
, dt.Inc
-- , nvl(fs.free,0)
, df.alloc - nvl(fs.free,0)
-- , fs.nbfrag
, fs.fragmax
, dt.autoext
, dt.ext_manage
, df.ftype
, dt.seg_sp_mg
Order
By contents
, maxpused desc
, dt.inc
;
Prompt
Set Heading Off
Set Termout Off
Drop function show_autoext_tbs;
Drop function show_increment_tbs;
Set Heading On
Set Termout On
Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
PERM SYSTEM 690 684 99.2 Y 32 10 5 LMTS 1 D 2.1
SYSAUX 550 513 93.4 Y 32 10 32 ASSM 1 D 1.6
EXAMPLE 100 79 78.8 Y 32 .6 19 ASSM 1 D 0.2
USERS 5 4 81.3 Y 32 1.3 1 ASSM 1 D 0.0
***** --------- --------- --------
sum 1345 1280 128
TEMP TEMP 29 29 100.0 Y 32 .6 0 LMTS 1 T 0.1
Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
***** --------- --------- --------
sum 29 29 32
UNDO UNDOTBS1 95 40 41.8 Y 32 5 40 LMTS 1 D 0.1
***** --------- --------- --------
sum 95 40 32
6 rows selected.
Subscribe to:
Posts (Atom)
