You are currently browsing the daily archive for September 9, 2009.
DBMS_SHARED_POOL.PURGE is available from 11.1. In 10.2.0.4, it is available through the fix for Bug 5614566. However, the fix is event protected. You need to set the event 5614566 to make use of purge. Unless the event is set, dbms_shared_pool.purge will have no effect.
Set the event 5614566 in the init.ora to turn purge on.
event=”5614566 trace name context forever”
SQL> select address, hash_value from v$sqlarea where sql_text = ‘select * from dept’;
ADDRESS HASH_VALUE
——– ———-
2671F27C 3599690174
SQL> exec dbms_shared_pool.purge(‘2671F27C,3599690174′,’C’);
PL/SQL procedure successfully completed.
SQL> select address, hash_value from v$sqlarea where sql_text = ‘select * from dept’;
no rows selected
SQL> alter system flush shared_pool;
SQL> select * from emp where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> select * from v$sqltext where sql_text like ‘%from emp%’;
ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE
—————- ———- ————- ———— ———-
SQL_TEXT
—————————————————————-
07000000223AB1E8 2736814944 gmjgsqfjk0yv0 3 0
select * from emp where empno=:”SYS_B_0″
07000000223B68A0 1745700775 a2dk8bdn0ujx7 3 0
select * from emp
SQL> exec sys.dbms_shared_pool.keep(‘07000000223AB1E8,2736814944′,’c’);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_availability from dual;
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in (‘WRI$_OPTSTAT_HISTHEAD_HISTORY’,’WRI$_OPTSTAT_HISTGRM_HISTORY’);
WRI$_OPTSTAT_HISTHEAD_HISTORY 4
WRI$_OPTSTAT_HISTGRM_HISTORY 15
DECLARE
ts TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT SYSTIMESTAMP INTO ts FROM dual;
dbms_stats.purge_stats(ts);
END;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.alter_stats_history_retention(0);
PL/SQL procedure successfully completed.
SQL> select dbms_stats.get_stats_history_availability from dual;
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
0
SQL> select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY;
0
SQL> truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;
Table truncated.
SQL> truncate table WRI$_OPTSTAT_HISTGRM_HISTORY;
Table truncated.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in (‘WRI$_OPTSTAT_HISTHEAD_HISTORY’,’WRI$_OPTSTAT_HISTGRM_HISTORY’);
WRI$_OPTSTAT_HISTHEAD_HISTORY .0625
WRI$_OPTSTAT_HISTGRM_HISTORY .0625
SQL>
Step 1. Connect Database as SYSDBA
$ sqlplus ” / as sysdba”
Step 2. Alter System to change the addm auto enable
SQL> ALTER SYSTEM set “_addm_auto_enable”=false scope=both;
System altered.
Step 3. backup the spfile and pfile.
SQL> create pfile from spfile;
File created.
SQL> exit
Step 1. Generate ssh private key for PRIMARY Server
[oracle@PRIMARY ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Created directory ‘/home/oracle/.ssh’.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
d9:a9:0b:d6:c7:fe:07:f0:78:89:9e:64:a4:56:39:5a oracle@PRIMARY.localdomain
[oracle@PRIMARY ~]$ ls -ltr
total 35212
drwxr-xr-x 2 oracle dba 4096 Jul 10 16:44 Desktop
-rw-r–r– 1 oracle dba 36010559 Jul 14 11:27 patch.log
[oracle@PRIMARY ~]$ cd .ssh
[oracle@PRIMARY .ssh]$ ls -ltr
total 8
-rw-r–r– 1 oracle dba 237 Jul 29 10:58 id_rsa.pub
-rw——- 1 oracle dba 883 Jul 29 10:58 id_rsa
[oracle@PRIMARY .ssh]$
Step 2. Generate ssh private key for STANDBY Server
[oracle@STANDBY ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):
Created directory ‘/home/oracle/.ssh’.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
f7:13:39:d2:c4:08:41:fd:21:ac:8a:86:d9:42:aa:cc oracle@STANDBY
[oracle@STANDBY ~]$
[oracle@STANDBY ~]$ pwd
/home/oracle
[oracle@STANDBY ~]$ cd .ssh
[oracle@STANDBY .ssh]$ ls -ltr
total 8
-rw——- 1 oracle dba 887 Jul 29 01:56 id_rsa
-rw-r–r– 1 oracle dba 225 Jul 29 01:56 id_rsa.pub
[oracle@STANDBY .ssh]$
Step 3. Create Authorised Key for Both servers
[oracle@PRIMARY .ssh]$ pwd
/home/oracle/.ssh
[oracle@PRIMARY .ssh]$ touch authorized_keys
[oracle@PRIMARY .ssh]$
[oracle@STANDBY .ssh]$ pwd
/home/oracle/.ssh
[oracle@STANDBY .ssh]$ touch authorized_keys
[oracle@STANDBY .ssh]$
Step 4. Restart the ssh service on both servers
[root@STANDBY etc]# service sshd restart
Stopping sshd: [ OK ]
Starting sshd: [ OK ]
[root@PRIMARY etc]# service sshd restart
Stopping sshd: [ OK ]
Starting sshd: [ OK ]
Step 5. Private key (id_rsa.pub) copy to own server and client server
[oracle@STANDBY .ssh]$ scp oracle@96.78.5.55:/home/oracle/.ssh/id_rsa.pub id_rsa.pub.PRIMARY
The authenticity of host ‘96.78.5.55 (96.78.5.55)’ can’t be established.
RSA key fingerprint is a1:89:2f:a9:0f:0e:2a:9e:45:c1:88:de:3c:51:61:01.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘96.78.5.55’ (RSA) to the list of known hosts.
oracle@96.78.5.55’s password:
id_rsa.pub 100% 237 0.2KB/s 00:00
[oracle@STANDBY .ssh]$
[oracle@PRIMARY .ssh]$ scp oracle@96.78.5.26:/home/oracle/.ssh/id_rsa.pub id_rsa.pub.STANDBY
The authenticity of host ‘96.78.5.26 (96.78.5.26)’ can’t be established.
RSA key fingerprint is 31:ab:fd:b3:3e:4d:bf:fe:c9:fa:0d:84:90:cd:d6:fc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘96.78.5.26’ (RSA) to the list of known hosts.
id_rsa.pub 100% 225 0.2KB/s 00:00
[oracle@PRIMARY .ssh]$ ls
authorized_keys id_rsa id_rsa.pub id_rsa.pub.STANDBY known_hosts
[oracle@PRIMARY .ssh]$ cat id_rsa.pub.STANDBY >>authorized_keys
Step 6. Test from Standby
[oracle@STANDBY ~]$ scp oracle@96.78.5.55:/home/oracle/.ssh/id_rsa.pub id_rsa.pub.PRIMARY
id_rsa.pub 100% 237 0.2KB/s 00:00
[oracle@STANDBY ~]$
— Change the Init.ora parameter
SQL> create pfile from spfile;
SQL> show parameter pfile;
SQL> show parameter spfile;
# *.log_archive_dest_1=’location=/u01/archive/ORATEST/’
# *.log_archive_format=’log%t_%s_%r.arc’
SQL> shutdown immediate
—-Backup spfile and pfile
SQL> create spfile from pfile;
SQL> connect / as sysdba
SQL> startup mount
SQL> archive log list;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/ORATEST/
Oldest online log sequence 35
Next log sequence to archive 37
Current log sequence 37
SQL> alter system switch logfile;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archive/ORATEST/
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ORATEST dbs]$ cd /u01/archive/ORATEST/
[oracle@ORATEST ORATEST]$ pwd
/u01/archive/ORATEST
[oracle@ORATEST ORATEST]$ ls -ltr
total 24172
-rw-r—– 1 oracle dba 24722432 Jul 16 11:40 log1_37_692113689.arc
[oracle@ORATEST ORATEST]$
[root@ORATEST ~]# grep MemTotal /proc/meminfo
MemTotal: 2016344 kB
[root@ORATEST ~]# grep SwapTotal /proc/meminfo
SwapTotal: 4192956 kB
[root@ORATEST ~]#
[root@ORATEST ~]# uname -mi
x86_64 x86_64
[root@ORATEST ~]#
+++++++++++++++Packages Check
[root@orcprmy ~]# rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n’ \
gcc gcc-c++ libstdc++-devel cpp glibc-devel glibc-headers glibc-kernheaders gnome-libs compat-db \
ORBit gtk+ imlib libpng10 gdk-pixbuf libungif sysstat xorg-x11-deprecated-libs xorg-x11-libs \
xorg-x11-Mesa-libGL expat fontconfig freetype zlib libaio compat-libstdc++ glibc-devel libgcc binutils \
control-center xscreensaver eel2 gail gnome-desktop gnome-icon-theme libgail-gnome libxklavier \
metacity nautilus startup-notification xloadimage xorg-x11-Mesa-libGLU at-spi desktop-backgrounds-basic \
eog gnome-panel gnome-vfs2-smb hicolor-icon-theme libexif librsvg2 nautilus-cd-burner redhat-artwork \
scrollkeeper cdrecord docbook-dtds evolution-data-server intltool libcroco libgnomeprint22 libgnomeprintui22 \
libgsf libwnck mkisofs samba-common ghostscript ghostscript-fonts gnutls libgnomecups libsoup openjade \
perl-XML-Parser sgml-common urw-fonts xml-common VFlib2 chkfontpath perl-URI perl-libwww-perl \
xorg-x11-font-utils perl-HTML-Parser xorg-x11-xfs perl-HTML-Tagset ttmkfdir
rpm -Uvh xorg-x11-deprecated-libs-6.8.2-1.EL.13.20.i386.rpm \
xorg-x11-libs-6.8.2-1.EL.13.20.i386.rpm \
xorg-x11-Mesa-libGL-6.8.2-1.EL.13.20.i386.rpm \
expat-1.95.7-4.i386.rpm \
fontconfig-2.2.3-7.i386.rpm \
freetype-2.1.9-1.i386.rpm \
zlib-1.2.1.2-1.2.i386.rpm
++++++++++++++++Kernel Parameter – 1GB Memory
[root@ORATEST etc]# pwd
/etc
[root@ORATEST etc]# vi sysctl.conf
[root@ORATEST etc]#
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 512
kernel.msgmax = 32768
kernel.msgmnb = 65535
kernel.msgmni = 512
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.ip_local_port_range = 1024 65000
fs.file-max = 1597481
kernel.threads-max = 270336
[root@ORATEST etc]# sysctl -p
+++++++++++++++++++++ Oracle User and Group – Password oracle
Create the group “dba” and user “oracle”
[root@ORATEST etc]# groupadd dba
[root@ORATEST etc]# useradd -c “Oracle Software Owner” -g dba oracle
[root@ORATEST etc]# passwd oracle
[root@ORATEST etc]# mkdir -p /u01/app/oracle
[root@ORATEST etc]# chown -R oracle.dba /u01
[root@ORATEST etc]# mkdir /var/opt/oracle
[root@ORATEST etc]# chown oracle.dba /var/opt/oracle
[root@ORATEST etc]# chmod 755 /var/opt/oracle
[root@ORATEST etc]#
+++++++++++++++++++ Installer download and extract
[root@ORATEST software]# pwd
/u01/software
[root@ORATEST software]# ls -ltr 10201_database_linux_x86_64.cpio.gz
-rwxr-xr-x 1 oracle dba 758433170 Nov 16 2008 10201_database_linux_x86_64.cpio.gz
[root@ORATEST software]#
[oracle@ORATEST software]$ gunzip 10201_database_linux_x86_64.cpio.gz
[oracle@ORATEST software]$
[oracle@ORATEST software]$ cpio -idmv < 10201_database_linux_x86_64.cpio
[oracle@ORATEST software]$ ls -ltr
total 1945716
drwxr-xr-x 6 oracle dba 4096 Oct 23 2005 database
-rwxr-xr-x 1 oracle dba 801603584 Nov 16 2008 10201_database_linux_x86_64.cpio
+++++++++++++++++++ Installing Oracle software
Connect to CYGWIN
Type startx to get graphics
$ ssh -X -Y ORATEST -l oracle
export ORACLE_BASE=/u01/app
export ORACLE_SID=orcprmy
unset ORACLE_HOME
unset TNS_ADMIN
[root@ORATEST RPM_64]# rpm -Uvh libaio-0.3.102-1.x86_64.rpm
warning: libaio-0.3.102-1.x86_64.rpm: V3 DSA signature: NOKEY, key ID db42a60e
Preparing… ########################################### [100%]
1:libaio ########################################### [100%]
[root@ORATEST RPM_64]# rpm -qa |grep libaio
libaio-0.3.102-1
Make sure ORACLE_HOME in universal installer set to /u01/app/oracle/product/10.2.0/db_1
++++++++++++++ After Universal Installer
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
+++++++++++++++ Management Consoles
isqlplus
http://ORATEST.localdomain:5560/isqlplus
isqlplus dba
http://ORATEST.localdomain:5560/isqlplus/dba
Enterprise Manager 10g
http://ORATEST.localdomain:1158/em
isqlplus stop and start
isqlplusctl stop
EM Console stop and start
emctl stop dbconsole
listener start
++++++++++++++++ Patch Apply 10.2.0.4
1. Stop iSQLPLUS
isqlplusctl stop
2. Stop EM Console
emctl stop dbconsole
3. Stop Listener
listnrctl stop
4. Stop Database
Sqlplus /nolo
connect / as sysdba
shutdown immediate
[oracle@ORATEST software]$ pwd
/u01/software
[oracle@ORATEST software]$ ls -ltr p6810189_10204_Linux-x86-64.zip
-rwxr-xr-x 1 oracle dba 1195551830 Mar 19 2008 p6810189_10204_Linux-x86-64.zip
[oracle@ORATEST software]$
— Database Upgrade manual
[oracle@ORATEST ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Jul 14 09:45:52 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 587202560 bytes
Fixed Size 2085680 bytes
Variable Size 163581136 bytes
Database Buffers 415236096 bytes
Redo Buffers 6299648 bytes
SQL> SHOW PARAMETER PFILE;
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileORATEST.ora
SQL> SHOW PARAMETER SHARED_POOL_SIZE
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 0
SQL> SHOW PARAMETER JAVA_POOL_SIZE
NAME TYPE VALUE
———————————— ———– ——————————
java_pool_size big integer 0
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=’150M’ SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=’150M’ SCOPE=spfile;
System altered.
SQL> SHUTDOWN
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP UPGRADE
ORACLE instance started.
Total System Global Area 587202560 bytes
Fixed Size 2085680 bytes
Variable Size 322964688 bytes
Database Buffers 255852544 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> SPOOL patch.log
SQL>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL>shutdown
SQL>startup
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
DBA Expert Blog 