Get current database character set from system view:
select * from sys.props$;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
To check RMAN backup size:
select INPUT_BYTES/1024/1024, OUTPUT_BYTES/1024/1024, INPUT_TYPE, start_time from V$RMAN_BACKUP_JOB_DETAILS order by start_time desc;
=============================================================================================================================================================
Import command using datapump :
impdp directory=MY_DIR dumpfile=exp_RCSADBO_1210.dmp logfile=impdp_RCSADBO_1211.log SCHEMAS=RCSADBO
=============================================================================================================================================================
Export command using datapump
expdp dpuser/dpuser@TDB10G schemas=dpuser exclude=TABLE:\"= \'EMP_DETAILS\'\"
directory=dpump_dir1 dumpfile=dpuser2.dmp logfile=dpuser.log
( or )
expdp schemas=SYSADM directory=DMP_DIR dumpfile=nmfsdmg1_sysadm_120508.dmp logfile=nmfsdmg1_sysadm_120508.log
=============================================================================================================================================================
To check NLS parameter settings:
select * from nls_session_parameters;
=============================================================================================================================================================
To implement RMAN incremental backup:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/mydir/rman_change_track.f' REUSE;
Sample datafile format: nmfstng1.block_change_tracking.01.dbf
=============================================================================================================================================================
DR Shutdown command:
alter database recover managed standby database cancel
shutdown immediate
=============================================================================================================================================================
DR Startup command:
startup nomount
alter database mount standby database
alter database recover managed standby database disconnect from session;
=============================================================================================================================================================
Tablespace script:
select 'alter tablespace ' || tablespace_name || ' being backup;' from dba_tablespace;
=============================================================================================================================================================
DR related parameters to log shipping:
*.fal_client='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=erpstedb203c)(PORT=13301)))(CONNECT_DATA=(SID=NMFSPRG)(ORACLE_HOME=/optware/oracle/10.
2.0.3_Q108_dbv)(SERVER=DEDICATED)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=erpstedb203p)(PORT=13301)))(CONNECT_DATA=(SID=NMFSPRG)(ORACLE_HOME=/optware/oracle/10.
2.0.3_Q108_dbv)(SERVER=DEDICATED)))'
*.global_names=TRUE
=============================================================================================================================================================
PROD parameters for DR:
log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=erpstedb203p)(PORT=13301)))(CONNECT_DATA=(SID=NMFSPRG)(SERVER=DEDICATED)))"','ARCH NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=60 register '
log_archive_dest_state_2='ENABLE'
=============================================================================================================================================================
Active session along with its SQL text:
SELECT
A.USERNAME,
A.SID,
A.SERIAL#,
C.SPID,
substr(client_info,1,instr(client_info,',',1,1)-1) "Client",
A.osuser,
A.MACHINE,
B.SQL_TEXT
FROM
V$SESSION A,
V$SQLAREA B,
V$PROCESS C
WHERE
A.SQL_ADDRESS = B.ADDRESS
AND A.USERNAME NOT IN ('SYS','DBA_ORACLE')
AND A.USERNAME <> ' '
AND A.PADDR = C.ADDR
AND A.STATUS = 'ACTIVE'
AND A.audsid <> USERENV ('SESSIONID')
ORDER BY A.STATUS,B.DISK_READS DESC
/
( or )
set linesize 150
set pagesize 120
COLUMN sid FORMAT 99999
COLUMN sql_text FORMAT a60 wrap
COLUMN username FORMAT a12
COLUMN module FORMAT a20
select /*+RULE */ SID,SPID,ses.SERIAL#,ses.USERNAME,ses.module,SQL_TEXT,pro.TERMINAL
from v$sqlarea sql, v$session ses,v$process pro where HASH_VALUE=SQL_HASH_VALUE and status = 'ACTIVE' and
ses.username is not null and
ses.PADDR = pro.ADDR
order by sql_text
=============================================================================================================================================================
Creating a tabelspace with AUTOEXTEND ON:
create TABLESPACE RCSAQ508_DATA
datafile '/oradata/RCSAUTG/db/RCSAUTG.rcsaq508_data.01.dbf' SIZE 2500M autoextend on maxsize 8000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
=============================================================================================================================================================
Command to add datafile to a tablespace when database is open:
alter tablespace RCSAOPER_DATA add datafile 'RCSAUTG.rcsaoper_data.08.dbf' SIZE 1000M autoextend on maxsize 8000M;
=============================================================================================================================================================
Renaming a datafile:
- BEFORE EXECUTING BELOW COMMANDS PHYSICALLY COPY TO THE NEW PATH AFTER DB SHUTDOWN.
- AFTER THE ABOVE STEP STARTUP MOUNT THE DB AND EXECUTE BELOW COMMANDS
alter database rename file '/optware/oracle/RCSAUTG/db/dbs/RCSAUTG.rcsaoper_data.08.dbf' to '/oradata/RCSAUTG/db/RCSAUTG.rcsaoper_data.08.dbf';
- AFTER EXECUTION DO ALTER DB OPEN AND PROCEED.
=============================================================================================================================================================
Creating a user:
create user IRMRCSAQ5 IDENTIFIED BY xxxxxxx
default tablespace RCSAQ508_DATA
quota unlimited on RCSAQ508_DATA
quota unlimited on RCSAQ508_INDEX
quota 0 on system
profile SECURITY_PROFILE_FUNCTIONAL;
=============================================================================================================================================================
Change Snapshot setting to 60mins after load test for OSC:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 10080, interval=> 60);
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(10080,60);
For 10g AWR reports data retention you can do the following.
Default AWR reports data retention is 7 days.
To view current settings do the following: (every 1 hour , 7 days retention)
select snap_interval, retention
from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0
To increase the retention or modify the interval do the following with appropriate values in minutes.
This takes snaps every 20 mins, and purges data older then 2 days.
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;
For FRS, I increased my retention time to 65 days, 1 hour snapshots so I can compare previous 2 month's data.
Make sure your SYSAUX tbsp has enough space.
begin
dbms_workload_repository.modify_snapshot_settings (
retention => 65*24*60
);
end;
=============================================================================================================================================================
Block checking sql:
set pagesize 100
set linesize 250
col module for a20
col event for a26
col last_call for a18
col objectname for a30
col userna for a10
select /*+RULE */s.username username , s.sid,s.serial#, p.spid,o.OBJECT_NAME objectname,
s.process,substr(s.module,1,20) module,
to_char(sysdate - s.last_call_et/60/60/24,'DD-MON-YYYY HH24:MI') last_call,
substr(w.event,1,24) event,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock l, v$session s, v$process p, v$session_wait w,v$locked_object lb, dba_objects o where (l.request > 0 or l.block > 0 ) and s.sid=l.sid and p.addr=s.paddr and s.last_call_et > 600 and w.sid=s.sid and s.sid = lb.SESSION_ID and lb.OBJECT_ID = o.OBJECT_ID order by last_call_et desc
=============================================================================================================================================================
To find active sessions:
select SID,USERNAME,STATUS from v$session where USERNAME='RCSADBO' and status='ACTIVE';
=============================================================================================================================================================
To find out the invalid objects :
select object_name,object_type from dba_objects where username='RCSADBO' and status ='INVALID';
=============================================================================================================================================================
To find out locked objects;
select * from v$locked_object;
select * from v$lock;
=============================================================================================================================================================
To find out the locked objects:
select a.OBJECT_NAME,b.SESSION_ID,b.ORACLE_USERNAME,b.OS_USER_NAME,b.PROCESS,b.LOCKED_MODE
from dba_objects a, v$locked_object b where b.OBJECT_ID=a.OBJECT_ID
select object_name from dba_objects where object_id in (76555,76558,76554, 76562,76605,76564);
=============================================================================================================================================================
Process details:
oracle@fn1db1[PFPRDFC] /optware/oracle => ps -e -o pid,user,pcpu,rss,comm|grep 27498
Process activity:
oracle@fn1db1[PFPRDFC] /optware/oracle => truss -pD 27498
truss -r all -w all -aefd -o truss_2 3319 -p 23319
=============================================================================================================================================================
To check the owner on the table:
select owner, object_name, object_type
from all_objects
where object_name='table_name';
=============================================================================================================================================================
To see who are the users that are connected to the database:
select username,count(*) from v$session groupby username;
=============================================================================================================================================================
cd $ORACLE_HOME/dbs or /optware/oracle/10.2.0.2_Q207/dbs-for init parameters
=============================================================================================================================================================
To move files from one (ex: iotrmdb1d) server to another (ex: iotrmdb1c) server:
scp -p iotrmdb1c:/backup/dmpdir/exp_RCSADBO_1210.dmp .
=============================================================================================================================================================
To send a mail from unix prompt:
mailx -s "invalid objects" omprakash.mendu@citi.com < /tmp/invalid_objects.log ============================================================================================================================================================= To create view to used later for checking hot blocks:
SQL> create view vw_x$bh as select * from sys.x$bh;
View created.
SQL> grant select on sys.vw_x$bh to dba_ig05617;
Grant succeeded.
=============================================================================================================================================================
To grep env i.e to find out which env is set on the database at unix prompt:
env | grep ORA
=============================================================================================================================================================
To check for stale statistics:
select owner,table_name,last_analyzed,sample_size from dba_tables where table_name in ('PS_BO_REL','PS_BO_CM','PS_CM','PS_CM_PURP_TYPE');
=============================================================================================================================================================
To gather statistics for a particular table:
exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SYSADM', tabname=>'PS_BI_TRDN_L3_T5', estimate_percent=>20, cascade=>TRUE);
exec dbms_stats.gather_table_stats(ownname=>'SYSADM', tabname=>'PSOPRDEFN', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS', degree=>5, GRANULARITY => 'ALL', CASCADE=>TRUE);
=============================================================================================================================================================
To check when a particular table was last analyzed:
select
*
--table_name, column_name, num_distinct nd, num_nulls nn, density, last_analyzed
from dba_tab_columns
where table_name in (
'PS_BI_TRDN_L3_T5'
)
order by table_name, column_name;
=============================================================================================================================================================
To see whether both the production and UAT are in sync:
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
PHPRDH READ WRITE
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
32412
SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
--------------
32412
Another command to check whether DR and PROD are in sync or not:
SQL>select ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status;
=============================================================================================================================================================
To check whether the database in in archive mode or not:
select log_mode from v$database;
=============================================================================================================================================================
To change the database into noarchive mode:
startup mount
alter database noarchivelog;
alter database open;
=============================================================================================================================================================
To identify processes that no longer are attached to active or inactive sessions:
select * from v$process
where addr not in (select paddr from v$session)
and spid is not null;
=============================================================================================================================================================
To identify inactive sessions:
desc v$session;
select sid,serial# from v$session where status ='INACTIVE';
=============================================================================================================================================================
To kill a oracle session from SQL prompt:
Alter system kill session ('sid,'serial#'); or Alter system kill session 'sid,serial#';
=============================================================================================================================================================
To see the list of items backed up in net backups:
/export/opt/openv/netbackup/bin/bplist -C hfpdb1 -S nyovdlt7 -t 0 -k ux_nj700_prod_ui_ny7 -R -b -s 02/12/2003 -e 02/12/2003 /backup/PHPRDH/sqlbtbck
=============================================================================================================================================================
Query to list oracle tablespaces, that are in backup mode:
SELECT d.tablespace_name, b.time
FROM dba_data_files d, v$backup b
WHERE
d.file_id = b.FILE#
AND b.STATUS = 'ACTIVE' ;
=============================================================================================================================================================
Turn-on extended SQL tracing for the above session using the following command
EXEC DBMS_SYSTEM.SET_EV(86,42297,10046,8,'');
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,
waits => TRUE, binds => FALSE);
or in the current session
alter session set events '10046 trace name context forever, level 12';
alter session set events '10053 trace name context forever';
( or )
**** 10gR2
conn / as sysdba
alter system set timed_statistics=true;
select sid, serial#, username from v$session;
exec sys.dbms_system.set_sql_trace_in_session(sid, serial#, true);
Execute your query from the application
Turn OFF the SQL Trace:
exec sys.dbms_system.set_sql_trace_in_session(sid, serial#, false);
$ sqlplus /nolog
connect / as sysdba
oradebug setorapid
oradebug unlimit
oradebug dump systemstate 10
For 10046 trace, the syntax is :
oradebug setospid
oradebug unlimit
oradebug event 10046 trace name context forever, level 12
( or )
-- turn on (with level 12)
execute sys.dbms_system.set_ev (&sid, &serial, 10046, 12, '')
-- turn off
execute sys.dbms_system.set_ev (&sid, &serial, 10046, 0, '')
ORADEBUG / SET EVENT
Turn on tracing for any session by setting event 10046
-- ---------------------------------------------------------------------------
-- using oradebug (from inside svrmgr or sql*Plus for 9.0 and higher)
select pid, spid, username from v$process;
PID SPID USERNAME
---- ----- --------
8 25807 oracle
SVRMGR> oradebug setorapid 8
-- or -
SVRMGR> oradebug setospid 25807
-- turn on
SVRMGR> oradebug event 10046 trace name context forever, level 12
-- turn off
SVRMGR> oradebug event 10046 trace name context off
-- turn on
alter session set events '10053 trace name context forever, level 1' ;
-- turn off
alter session set events '10053 trace name context off' ;
-- turn on (with level 1)
execute sys.dbms_system.set_ev (&sid, &serial, 10053, 1, '')
-- turn off
execute sys.dbms_system.set_ev (&sid, &serial, 10053, 0, '')
=============================================================================================================================================================
To rename all datafiles at OS level (put the below code in a shell script and execute it):
#!/bin/ksh
for i in /oradata/EPPATST/db/eppadmg1.*.dbf
do
echo mv $i $(echo $i| sed "s/eppadmg1/eppatst/")
mv $i $(echo $i| sed "s/eppadmg1/eppatst/")
done
=============================================================================================================================================================
Identify problematic sessions:
select s.sid,s.serial#,s.process,p.spid
,s.LAST_CALL_ET/60
,s.machine,s.status
,s.module,s.action,s.PROGRAM
,s.SQL_ADDRESS, sq.HASH_VALUE
, substr(sq.SQL_TEXT,1,60)
,SYSDATE - (s.last_call_et / 86400) "Last Call" ,s.logon_time,sysdate
from v$session s, v$process p
, v$sql sq
where s.PADDR = p.ADDR
and s.SQL_ADDRESS = sq.ADDRESS
and s.STATUS = 'ACTIVE'
and type <> 'BACKGROUND'
--and sid in ('3879')
--and spid = '21968'
--and s.action like 'O%CITI US%%'
--and s.module like 'APXINWKB'
--and s.program like 'J%'
--and s.sid = '2773'
--and machine = 'p2pap2c'
and s.LAST_CALL_ET/60 > 3
--and s.LAST_CALL_ET/60 < desc ="=" style="color: rgb(255, 0, 0);">Identify the wait events for the above sessions:
select * from v$session_wait
where sid in (
select sid from v$session where status like 'ACTIVE' and type not like 'BAC%'
)
order by seconds_in_wait desc
=============================================================================================================================================================
Temp space usage:
select
s.sid,
su.SESSION_ADDR, su.SQLHASH, su.TABLESPACE,su.SEGFILE#
,su.CONTENTS,su.SEGTYPE,su.BLOCKS
from v$sort_usage su
, v$session s
where s.SADDR = su.SESSION_ADDR
( or )
select
s.sid,
--su.SESSION_ADDR
--, su.SQLHASH, su.TABLESPACE,su.SEGFILE#
--,su.CONTENTS,su.SEGTYPE
sum(su.BLOCKS)
from v$sort_usage su
, v$session s
where s.SADDR = su.SESSION_ADDR
=============================================================================================================================================================
UNDO:
select * from v$transaction;
select segment_name, status, count(1)
from dba_undo_extents group by segment_name, status;
---
group by s.sid
order by 2 desc
=============================================================================================================================================================
Handy script to identify LOB types in one tablespace and generate SQL to move them to another tablespace by owner and old tablespace:
select 'alter table '||owner||'.'||table_name ||' move lob (' ||column_name||')' ||
' store as (tablespace
from dba_lobs
where segment_name in (select segment_name from dba_segments
where tablespace_name='
and owner='
and segment_type='LOBSEGMENT');
=============================================================================================================================================================
Here are the details for aix server startup script:
=============================================================================================================================================================
Please check the following files in place /dbms/oracle/other/bin/dbshut /dbms/oracle/other/bin/dbshut_standby
/dbms/oracle/logs/dbstart.log (empty file) /dbms/oracle/logs/dbstart_standby.log(empty file) /usr/local/bin/oraenv /usr/local/bin/coraenv /usr/local/home/dbhome (dbshut, dbshut_standby cdm standard scripts Oranev, coraenv and dbhome are updated in usr/local/bin directory by root.sh)
FOR AIX SERVER:
Update the /etc/oratab file with database name and respective home.
Always put the entry in following format for standby databases DBNAME:$ORACLE_HOME:S Request the SA for the server to create the startup script S90Oracle.rc which is under /etc/rc.d/rc2.d script is as follows:
/usr/bin/su - oracle -c ‘/dbms/oracle/other/bin/dbstart' |tee
/dbms/oracle/logs/dbstart.log /usr/bin/su - oracle -c '/dbms/oracle/other/bin/dbstart_standby' | tee
/dbms/oracle/logs/dbstart_standby.log
created the shutdown script in /etc/rc.shutdown script is as follows:
/usr/bin/su - oracle -c '/dbms/oracle/other/bin/dbshut' |tee
/dbms/oracle/logs/dbshut.log /usr/bin/su - oracle -c '/dbms/oracle/other/bin/dbshut_standby' |tee
tee /dbms/oracle/logs/dbshut_standby.log
=============================================================================================================================================================
Cloning Oracle Home:
OH=$1
OHN=$2
echo "inventory_loc=$OH/oraInventory" > $OH/oraInst.loc echo "inst_group=dba" >> $OH/oraInst.loc cd $OH/oui/bin ./runInstaller -silent -clone -ignoresysPrereqs -ignorePrereq -invPtrLoc $OH/oraInst.loc ORACLE_HOME=$OH ORACLE_HOME_NAME=$OHN
=============================================================================================================================================================
Sample .profile under /optware/oracle:
#!/bin/ksh
echo 'Welcome to '
echo
banner `hostname`
echo
############################################################
set +u
export PATH=/bin:/usr/local/bin:/usr/bin:/usr/sbin:$PATH:/etc:/usr/ucb::/usr/bin/X11:/sbin
export HOME=/optware/oracle
export TERM=xterm
export EDITOR=vi
set -u # error if undefined variable.
trap "echo 'logout'" 0 # what to do on exit.
if [ -s "$MAIL" ] # This is at Shell startup. In normal
then echo "$MAILMSG" # operation, the Shell checks
fi # periodically.
set -o vi
stty erase ^?
####### AIX ########
#
export AIXTHREAD_SCOPE=S # uncomment if being used on AIX
#
##########################
alias l='ls -al'
alias ll='ls -al'
alias lll='ls -al|pg'
alias ldt='ls -lt|pg'
alias Grep=grep
alias lt='ls -latrF'
alias sql='sqlplus /nolog'
alias tohome='cd /dbms/oracle/local/GDPFDVG1/etc'
alias torman='cd /backup/GDPFDVG1/rman'
alias todb='cd /oradata/GDPFDVG1/db'
alias todbv='cd /optware/oracle/work/stanma/sec_policies'
#########################
# Call local.env
#########################
. /dbms/oracle/local/local.env
=============================================================================================================================================================
On unix server the command history file can be replaced with default as below:
HISTFILE=~/.hist.$(tty | sed 's;.*/;;')
alias history='hist -l'
=============================================================================================================================================================
To check for memory leak:
SQL> select * from v$sgastat where name like '%NO ACCESS%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool KGH: NO ACCESS 33260224
=============================================================================================================================================================
To check for dead locks:
set pagesize 100
set linesize 250
col module for a20
col event for a26
col last_call for a18
col objectname for a30
col userna for a10
select /*+RULE */s.username username , s.sid,s.serial#, p.spid,o.OBJECT_NAME objectname,
s.process,substr(s.module,1,20) module,
to_char(sysdate - s.last_call_et/60/60/24,'DD-MON-YYYY HH24:MI') last_call,
substr(w.event,1,24) event,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock l, v$session s, v$process p, v$session_wait w,v$locked_object lb, dba_objects o
where (l.request > 0 or l.block > 0 )
and s.sid=l.sid
and p.addr=s.paddr
and s.last_call_et > 600
and w.sid=s.sid
and s.sid = lb.SESSION_ID
and lb.OBJECT_ID = o.OBJECT_ID
order by last_call_et desc
/
=============================================================================================================================================================
Parallel Degree:
Best Scenario - when tables are partitioned
Worst Scenario - Non-partitioned tables when joining multiple tables and using hash-joins and range scans
Better not use parallel degree while creating index
Two ways of using parallel degree:
1. Using as a hint i.e., parallel hint
2. Using parallel degree at object level while creating index or tables
=============================================================================================================================================================
On AIX Unix box:
oracle@erpstedb202u[GDPFUTG1] /dbms/oracle/other/orm/bin => echo $user
21.9
oracle@erpstedb202u[GDPFUTG1] /dbms/oracle/other/orm/bin => echo $cpus_used
0.71
oracle@erpstedb202u[GDPFUTG1] /dbms/oracle/other/orm/bin => user_used=`echo "scale=12; $user * $cpus_used"|bc`
oracle@erpstedb202u[GDPFUTG1] /dbms/oracle/other/orm/bin => echo $user_used
15.549
=============================================================================================================================================================
Query to check for temp tablespace usage:
SELECT b.TABLESPACE, b.segfile#, b.segblk#, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb,
a.SID, a.serial#, a.username, a.osuser, a.program, a.status FROM v$session a, v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr and
ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;
( OR )
SELECT b.TABLESPACE, b.segfile#, b.segblk#, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb,
a.SID, a.serial#, a.username, a.osuser, a.program, a.status FROM v$session a, v$sort_usage b, v$process c, v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr and
a.status='ACTIVE' ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;
Note: the above query checks temp tablespace usage for active user sessions
=============================================================================================================================================================
Adding redo logs to standby database:
1. Shutdown, mount, add redo logs, shutdown and bring up the standby database
Command for adding redo logs:
alter database add standby logfile
GROUP 6 (
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.06a.log',
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.06b.log'
) SIZE 400M,
GROUP 7 (
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.07b.log',
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.07a.log'
) SIZE 400M,
GROUP 8 (
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.08a.log',
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.08b.log'
) SIZE 400M,
GROUP 9 (
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.09a.log',
'/oradata/GCMPPRG/db/GCMPPRG.STDBY_redo.09b.log'
) SIZE 400M,
GROUP 10 (
=============================================================================================================================================================
Orasecure:
select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where profile='DEFAULT';
=============================================================================================================================================================
To pin tables in shared pool:
alter table sysadm.PS_CGH_MGR_RL storage (BUFFER_POOL KEEP);
=============================================================================================================================================================
Gathering stats:
exec dbms_stats.gather_table_stats(ownname=>'OWNER', tabname=>'TABLE_NAME', estimate_percent=>20, NO_INVALIDATE=>FALSE, degree=>6,granularity =>'ALL',CASCADE=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 254');
NO_INVALIDATE=> FALSE - this makes the optimizer to not to use the cached or old explain in the cache.
Show Tablespace freespace
select tablespace_name,sum(bytes)
from dba_free_space group by tablespace_name;
No comments:
Post a Comment