Memory check

Huge pages on SRI()
========================================================

300 GB lock ( lock 300 gb memory for oracle out of 376 GB allocated on server )

--------/etc/security/limits.conf
* soft memlock 314572800
* hard memlock 314572800

huge pages and other shared memory settings

vm.nr_hugepages =45098
shmmax=322122547200
shmall=78643200


disable transparent huge pages
it is enabled as of now and needs to be disabled

SGA size for databases

total sga
mgmtdb= 1024 mb
ASM   = 1088 mb
PDWDB= 79872 mb (78 GB)
PRPDB= 8192 mb (8 GB)
total sga=90176 mb (88.0625 GB)

pga size for databases

pga_aggregate_target
PRPDB=8 GB
PDWDB=148 GB
total pga=156 GB

below parameter of both the databases PDWDB and PXPDB

large_pool_size=500M
use_large_pages=only 

materialized views

Oracle materialized views

Materialized views are oracle objects those contains results of the query. They are local copies of the objects located remotely or used to create summary table based on the aggregation of a tables's data.

grant from system:
grant create materialized view, query rewrite to SCOTT;

Create matview:
create materialized view meta_table_info_mview
build immediate refresh complete enable query rewrite as select * from meta_table_info;
--Enable query rewrite needs EE and it will fail if database is XE.
create materialized view md_table_info_mview
build immediate refresh complete as select * from meta_table_info;

Create matview logs:
create materialized view log on meta_table_info;

DML on mat view: DML operations are not permitted on mat views.'
delete from meta_table_info_mview where table_name='SKUIPOPARAM'

ORA-01732: data manipulation operation not legal on this view

Refresh matview:
Execute dbms_mview.refresh('MD_TABLE_INFO_MVIEW');


--Query to check all mviews
select * from user_mviews;

--query to check master table and mview log
select MASTER, LOG_TABLE from USER_MVIEW_LOGS

--Find related mview log tables by using following queries
select TABLE_NAME from USER_TABLES where table_name like '%MD_TABLE_INFO%';
 or
select  master, log, temp_log from sys.mlog$ where mowner = 'WWFMGR' and master = 'MD_TABLE_INFO';

--Update a record in master table and check if mview log table updated or not

update MD_TABLE_INFO set table_name='XXX' WHERE TABLE_NAME='SS';

select * from MLOG$_MD_TABLE_INFO;

undo retention

The DBMS_UNDO_ADV Package provides some excellent feedbackup to help the best value for the undo_retention

Sql> select dbms_unod_adv longest_query(sysdate-1/24,sysdate) as best_undo_time from dual;

This gives longest query of the instance took around 845 seconds is good for starting point of undo retention.

The below query will be appropriate value for undo_retention

Sql> select dbms_undo_adv.required_retention(sysdate-30,sysdate) as reqd_retn from dual;

Its give last 30 days longest _query procedure

SQL> SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;

LONGEST_QUERY

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

         7228

SQL> show parameter undo ;

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS01

SQL> SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;

LONGEST_QUERY

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

         1536

SQL> select dbms_undo_adv.required_retention(sysdate-30,sysdate) as reqd_retn from dual;

 REQD_RETN

----------

      7228

ORA$AT_SA_SPC_SY Jobs failing?

select client_name, status
from dba_autotask_client;

dba jb history

select distinct client_name, window_name, job_status, job_info
from dba_autotask_job_history
where job_status <> 'SUCCEEDED'
order by 1,2;

CLIENT_NAME        WINDOW_NAME     JOB_STATUS JOB_INFO
------------------ --------------- ---------- -------------------------------------------
auto space advisor SATURDAY WINDOW FAILED     ORA-6502: PL/SQL: numeric or value error...
auto space advisor SUNDAY WINDOW   FAILED     ORA-6502: PL/SQL: numeric or value error...

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

PL/SQL procedure successfully completed

select client_name, status
from dba_autotask_client
where client_name = 'auto space advisor';

CLIENT_NAME                     STATUS
------------------------------- --------
auto space advisor              DISABLED

BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

ORA-12012: error

ORA-12012: error on auto execute of job 719358



 select  d.job_name,d.job_action  from  dba_scheduler_jobs d,sys.scheduler$_job s where d.job_action = s.program_action and s.obj# = &myjobnum;

enter no 719358

select job, what from dba_jobs where broken = 'Y' or failures > 0; for broken jobs




provide following grant to the user and then try to execute the job.

'Grant select any table to user_name'

SQL> execute dbms_job.run(444);
BEGIN dbms_job.run(444); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

Elapsed: 00:00:09.82

SQL> Grant select any table to username;

Grant Succeeded.

Elapsed: 00:00:01.30

SQL> execute dbms_job.run(444);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.30

Sequence

sequence rebuild
+++++++++++


select 'drop sequence ' || sequence_name || ';' from user_sequences;


select 'select dbms_metadata.get_ddl('||'''SEQUENCE'''||','''||SEQUENCE_NAME||''','''||SEQUENCE_OWNER||''') FROM DUAL ;' FROM all_sequences where sequence_owner='ODSADMIN'';

select 'select dbms_metadata.get_ddl('||"'SEQUENCE"'||',"'||SEQUENCE_NAME||","||SEQUENCE_OWNER||"’) FROM DUAL ;' FROM all_sequences where sequence_owner='ORADMIN’ and sequence_name like 'MKT_SHOPPING_SEQ';



Create sequence
+++++++++++

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;' from all_sequences where sequence_owner='ODSADMIN';
#!/usr/bin/ksh
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




TOD1DB_get_sequences.ksh


sqlplus /nolog <<EOF
connect / as sysdba

set lines 188
set trimspool on
set pages 0

spool create_sequences.lst

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;'
from all_sequences where sequence_owner='ODSADMIN';

EOF


if [ -f create_sequences.sql ]; then
        rm create_sequences.sql
fi

cat TOD1DB_get_sequences_header.txt > create_sequences.sql

cat create_sequences.lst |grep -v selected |grep -v SQL |grep -v "  2  from all_" >> create_sequences.
sql

echo "" >> create_sequences.sql
echo "quit" >> create_sequences.sql





2)TOD1DB_get_sequences_header.txt

connect / as sysdba

set time on  timing on  echo on pagesize 20000 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;


3) drop sequence


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
spool drop_sequences_r.sql
select 'drop sequence '||sequence_owner||'.'||sequence_name||';'
from dba_sequences
where sequence_owner=upper('&l_schema') ;

spool off



4)grant prilvege




SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS where owner='ODSADMIN';