Yuntao's Oracle Study

Tuesday, February 12, 2013

Creating a 11gR2 DB with the create statement

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=pantst01
export PATH=$PATH:$ORACLE_HOME/bin
export NLA_LANG=american.AL32UTF8
export NLS_DATA_FORMAT=yyyy-mm-dd:hh24:mi:ss

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
cat init.ora|grep -v ^#>initpantst01.ora

mkdir -p /u01/app/oracle/admin/pantst01/adump
mkdir -p /u02/oradata/PANTST01/controlfile/
mkdir -p /u02/oradata/PANTST01/flash_recovery_area
mkdir -p /u02/oradata/tbl/PANTST01/datafile/
mkdir -p /u02/oradata/undo/PANTST01/tempfile/
mkdir -p /u02/oradata/undo/PANTST01/undofile/
mkdir -p /u02/oradata/redo/PANTST01/onlinelog/

cat initpantst01.ora

db_name='pantst01'
memory_max_target=3g
memory_target=2147483648
processes = 150
audit_file_dest='/u01/app/oracle/admin/pantst01/adump'
audit_trail ='db'
control_files='/u02/oradata/PANTST01/controlfile/control_01.ctl','/u02/oradata/PANTST01/controlfile/control_02.ctl'
db_block_size=8192
db_domain='moh.ad.gov.on.ca'
db_recovery_file_dest='/u02/oradata/PANTST01/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
open_cursors=150
remote_login_passwordfile='EXCLUSIVE'
compatible ='11.2.0'
global_names=true
service_names='pantst01.moh.ad.gov.on.ca'
undo_management='auto'
undo_retention=7200
undo_tablespace='undotbs1'

orapwd file=orapwpantst01 password=oracle entries=20

cat createDatabase.sql

CREATE DATABASE pantst01
USER SYS IDENTIFIED BY pantst01
USER SYSTEM IDENTIFIED BY pantst01
LOGFILE GROUP 1 ('/u02/oradata/redo/PANTST01/onlinelog/redo01.log') SIZE 100M,
GROUP 2 ('/u02/oradata/redo/PANTST01/onlinelog/redo02.log') SIZE 100M,
GROUP 3 ('/u02/oradata/redo/PANTST01/onlinelog/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/oradata/tbl/PANTST01/datafile/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/oradata/tbl/PANTST01/datafile/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u02/oradata/tbl/PANTST01/datafile/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u02/oradata/undo/PANTST01/tempfile/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u02/oradata/undo/PANTST01/undofile/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

---create process
$> export ORACLE_SID=pantst01
[10.69.11.141:ontpand0bav121:oracle@pantst01]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs
$> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 15:22:36 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3207790592 bytes
Fixed Size 2185840 bytes
Variable Size 2332034448 bytes
Database Buffers 855638016 bytes
Redo Buffers 17932288 bytes

SQL> @createDatabase.sql

Database created.

SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/sqlplus/admin/pupbld.sql

Tuesday, December 11, 2012

rebuild physical standby databse with missed archivelog files

SQL> ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;

DATABASE altered.

SQL>

alert log is below:
Tue Nov 13 14:45:26 2012
MRP0: Background Managed Standby Recovery process started (test)
Managed Standby Recovery NOT USING REAL TIME Apply
parallel recovery started WITH 2 processes
Tue Nov 13 14:45:31 2012
Waiting FOR ALL non-CURRENT ORLs TO be archived...
Media Recovery Waiting FOR thread 1 SEQUENCE 383
Tue Nov 13 14:45:32 2012
Completed: ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION
Tue Nov 13 14:45:59 2012
Redo Shipping Client Connected AS PUBLIC
-- Connected User is Valid
RFS[2]: Assigned TO RFS process 9542
RFS[2]: IDENTIFIED DATABASE TYPE AS 'physical standby'
RFS[2]: Archived Log: '/arch2/1_385_726529113.dbf'
Tue Nov 13 14:46:02 2012
Fetching gap SEQUENCE IN thread 1, gap SEQUENCE 383-384
Tue Nov 13 14:47:02 2012
FAL[client]: Failed TO request gap SEQUENCE
GAP - thread 1 SEQUENCE 383-384
DBID 2024668720 branch 726529113
FAL[client]: ALL defined FAL servers have been attempted.
-------------------------------------------------------------
CHECK that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter IS defined TO a VALUE that IS sufficiently LARGE
enough TO maintain adequate log switch information TO resolve
archivelog gaps.

------------------------------------------------------------------------
--find scn
SQL> SELECT SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE# FROM v$archived_log WHERE SEQUENCE# > 382 ORDER BY 1;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
383 740638 740911
384 740911 740915
385 740915 740917
385 740915 740917

------------------------------------------------------------------------
--backup based on scn number
[oracle@primarydb ~]$ cd $ORACLE_HOME/bin
[oracle@primarydb bin]$ ./rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Oct 10 18:51:57 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST (DBID=2024668720)

RMAN> backup device type disk incremental from scn 740638 database format '/tmp/test_db_incre.bbk';

Starting backup at 10-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle/product/oradata/test/test01.dbf
input datafile fno=00007 name=/oracle/product/oradata/test/sysaux01.dbf
input datafile fno=00009 name=/oracle/product/oradata/test/test03.dbf
input datafile fno=00006 name=/oracle/product/oradata/test/undo02.dbf
input datafile fno=00001 name=/oracle/product/oradata/test/system01.dbf
input datafile fno=00005 name=/oracle/product/oradata/test/perfstat.dbf
input datafile fno=00003 name=/oracle/product/oradata/test/rman.dbf
input datafile fno=00002 name=/oracle/product/oradata/test/undo01.dbf
input datafile fno=00008 name=/oracle/product/oradata/test/test02.dbf
input datafile fno=00010 name=/oracle/product/oradata/test/test04.dbf
input datafile fno=00011 name=/oracle/product/oradata/test/test05.dbf
channel ORA_DISK_1: starting piece 1 at 10-OCT-12

channel ORA_DISK_1: finished piece 1 at 10-OCT-12
piece handle=/tmp/test_db_incre.bbk tag=TAG20121010T185204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:26
Finished backup at 10-OCT-12

RMAN>

--copy backup piece to standby server
[oracle@primarydb bin]$ cd /tmp
[oracle@primarydb tmp]$ scp test_db_incre.bbk 192.168.3.176:/tmp/backup
The authenticity of host '192.168.3.176 (192.168.3.176)' can't be established.
RSA key fingerprint is a4:54:6b:bf:12:34:42:73:f5:ba:5f:38:c7:28:9c:b5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.3.176' (RSA) to the list of known hosts.
oracle@192.168.3.176's password:
test_db_incre.bbk 100% 736KB 736.0KB/s 00:00
[oracle@primarydb tmp]$

------------------------------------------------------------------------
--recover standby database
oracle@standbydb bin]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production ON Tue Nov 13 15:03:46 2012

Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved.

Connected TO:
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options

SQL> ALTER DATABASE recover managed standby DATABASE cancel;

DATABASE altered.

SQL> exit
Disconnected FROM Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options
[oracle@standbydb ~]$ cd $ORACLE_HOME/bin
[oracle@standbydb bin]$ ./rman target /

Recovery Manager: Release 10.2.0.4.0 - Production ON Tue Nov 13 14:58:25 2012

Copyright (c) 1982, 2007, Oracle. ALL rights reserved.

connected TO target DATABASE: TEST (DBID=2024668720, NOT OPEN)

RMAN> catalog backuppiece '/tmp/backup/test_db_incre.bbk';

USING target DATABASE control file instead OF recovery catalog
cataloged backuppiece
backup piece handle=/tmp/backup/test_db_incre.bbk recid=49 stamp=799253957

RMAN> recover DATABASE noredo;

Starting recover at 13-NOV-12
USING target DATABASE control file instead OF recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=97 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) TO restore FROM backup SET
destination FOR restore OF datafile 00001: /oracle/product/oradata/test/system01.dbf
destination FOR restore OF datafile 00002: /oracle/product/oradata/test/undo01.dbf
destination FOR restore OF datafile 00003: /oracle/product/oradata/test/rman.dbf
destination FOR restore OF datafile 00004: /oracle/product/oradata/test/test01.dbf
destination FOR restore OF datafile 00005: /oracle/product/oradata/test/perfstat.dbf
destination FOR restore OF datafile 00006: /oracle/product/oradata/test/undo02.dbf
destination FOR restore OF datafile 00007: /oracle/product/oradata/test/sysaux01.dbf
destination FOR restore OF datafile 00008: /oracle/product/oradata/test/test02.dbf
destination FOR restore OF datafile 00009: /oracle/product/oradata/test/test03.dbf
destination FOR restore OF datafile 00010: /oracle/product/oradata/test/test04.dbf
destination FOR restore OF datafile 00011: /oracle/product/oradata/test/test05.dbf
channel ORA_DISK_1: reading FROM backup piece /tmp/backup/test_db_incre.bbk
channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/backup/test_db_incre.bbk tag=TAG20121010T185204
channel ORA_DISK_1: restore complete, elapsed TIME: 00:00:01
Finished recover at 13-NOV-12

RMAN>

Wednesday, June 6, 2012

OS Watcher

OSWatcher (oswbb) is a downloadable utility to capture performance metrics from the operating system. When you install and run oswbb as part of a performance diagnostic data collection best practice, you can aid in a quicker resolution of your SR with support and development.

Installation
1. Download the tool from metalink
2. Create folder for each node separately.
3. Unzip file oswbb601.tar
4. Set up file private.net by modifying the file ../oswbb/Exampleprivate.net
5. Output log will be placed under ../oswbb/archive/

Starting OSWbb
To start the OSWbb utility execute the startOSWbb.sh shell script. This script has 2 arguments which control the frequency that data is collected and the number of hours of data to archive.

ARG1 = snapshot interval in seconds.
ARG2 = the number of hours of archive data to store.
If you do not enter any arguments the script runs with default values of 30 and 48 meaning collect data every 30 seconds and store the last 48 hours of data in archive files.

Example:
./startOSWbb.sh 60 10

This would start the tool and collect data at 60 second intervals and log the last 10 hours of data to archive files.

Stopping OSWbb:
To stop the OSWbb utility execute the stopOSWbb.sh command. This terminates all the processes associated with the tool.

Example:
./stopOSWbb.sh

Reference
OSWatcher Black Box (Includes: [Video]) (Doc ID 301137.1)

Thursday, May 10, 2012

When patching, you have two entities in your environment
- Oracle Database Software
- Oracle Database

You can check the patches installed using opatch utility which you can find in $ORACLE_HOME/OPatch:

./opatch lsinv -bugs_fixed | grep -i 'database cpu'

Following patching your database with a PSU/CPU, you want to keep a history of the patches applied.
In 11g (actually this started since 10.2.0.4), there is a new post patch installation script which does the job.
It is catbundle.sql, and is located in $ORACLE_HOME/rdbms/admin. (In previous releases this was done by catcpu.sql)
This shall be invoked with 2 parameters, patch type (PSU/CPU) and action (apply or rollback).
The purpose of the script is to update the entry in registry$history.

SQL> @catbundle.sql CPU APPLY
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1. PSU (and CPU) patch README already contains information about when to execute post install step (i.e loading modified .sql files, ex: catbundle.sql) on databases created using DBCA.

2. PSU/CPU entry in "registry$history" gets updated by the patch post installation step (i.e catbundle.sql). Skipping this step (where it is not actually MANDATORY in above mentioned case) results no PSU/CPU entry in registry$history. Even though it is not mandatory to run PSU/CPU post install step (i.e catbundle.sql), there is no harm in running as it helps to update the registry$history.

References:
How to confirm that a Critical Patch Update (CPU) has been installed in Linux / UNIX [ID 821263.1]
Is it required to run PSU/CPU patch post installation step after creating the database using DBCA in already patched Oracle Home ? [ID 1388555.1]
Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch [ID 865255.1]

Thursday, December 8, 2011

error message in alert.log after dnfs is enabled for a database...

Database Alert Log entries: Direct NFS: Failed to set socket buffer size.wtmax=[1048576] rtmax=[1048576], errno=-1 (Doc ID 1352886.1)

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later

Information in this document applies to any platform.

Symptoms

Database alert log shows these 'Direct NFS: Failed to set socket buffer size.wtmax=[1048576] rtmax=[1048576], errno=-1' entries:

alert_ORCL.log

~~~~~~~~~~~

...

Mon Aug 08 21:04:00 2011

Adjusting the default value of parameter parallel_max_servers

from 320 to 185 due to the value of parameter processes (200)

Starting ORACLE instance (normal)

...

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

...

Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0

...

Mon Aug 08 21:05:19 2011

Direct NFS: Failed to set socket buffer size.wtmax=[1048576] rtmax=[1048576], errno=-1 <-- here

...

Cause

tcp_max_buf: is set too small.

Solution

The error can be fixed by increasing the value of tcp_max_buf:

# /usr/sbin/ndd -set /dev/tcp tcp_max_buf 1056768

To display its value:

# /usr/sbin/ndd /dev/tcp tcp_max_buf

Also check what are tcp_xmit_hiwat and tcp_recv_hiwat set to and if set low, see if increasing these helps:

# /usr/sbin/ndd -set /dev/tcp tcp_xmit_hiwat 1056768

# /usr/sbin/ndd -set /dev/tcp tcp_recv_hiwat 1056768

To display their values:

# /usr/sbin/ndd /dev/tcp tcp_xmit_hiwat

# /usr/sbin/ndd /dev/tcp tcp_recv_hiwat

Sunday, September 25, 2011

Cannot start oms EM 12c

The OMS is failing to start and the problem seems to the the connectivity to the repository DB. I have observe that the connection to the DB from SQL Plus is failing. To check if the OMS is able to connect, perform the connectivity check using the rcuJDBCEngine.

1. Get the connection details by running the command
./emctl config oms -list_repos_details

2. Please refer to the following note for details on checking the connectivity from OMS to repository using rcuJDBCEngine.
12c Cloud Control: How to Verify the Connectivity from 12c OMS to Repository Database using rcuJDBCEngine (Doc ID 1448007.1)

Solution

1) stop any running processes using:-

$/bin/emctl stop oms -all -force

2) Check whether there are any remaining processes:-

ps -ef | grep EMGC_ADMINSERVER
ps -ef | grep EMGC_OMS1
ps -ef | grep java

kill any of the above processes which are running from the OMS $ORACLE_HOME using 'kill -9 '

3) Remove the *.DAT and *.lok files:

$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/store/default/_WLS_EMGC_OMS1000000.DAT
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/store/default/_WLS_EMGC_ADMINSERVER000000.DAT

and

[oracle@hostname gc_inst]$ find . -name "*.lok"
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/config/config.lok
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/EmbeddedLDAP.lok
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/EmbeddedLDAP.lok
$EM_INSTANCE_BASE/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok

4) start the OMS

$/bin/emctl start OMS

11g/12c OMS failed to start "Unable to obtain lock on EMGC_OMS1.lok" [ID 1302822.1]

Tuesday, August 16, 2011

Recreate EM 12C repository

> ./emca -config dbcontrol db -repos recreate

STARTED EMCA at Dec 8, 2012 10:33:43 AM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:

Database SID: pacloud

Listener port number: 1521

Listener ORACLE_HOME [ /opt/oracle/app/oracle/product/11.2.0/db_1 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional): dbasupport@protectair.com

Outgoing Mail (SMTP) server for notifications (optional):

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

You have specified the following settings

Database ORACLE_HOME ................ /opt/oracle/app/oracle/product/11.2.0/db_1

Local hostname ................ culp-dbmgmt01.pa.parsonsvic.com

Listener ORACLE_HOME ................ /opt/oracle/app/oracle/product/11.2.0/db_1

Listener port number ................ 1521

Database SID ................ pacloud

Email address for notifications ............... dbasupport@protectair.com

Outgoing Mail (SMTP) server for notifications ...............

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

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Dec 8, 2012 10:34:51 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/oracle/app/oracle/cfgtoollogs/emca/pacloud/emca_2012_01_08_10_33_43.log.

Dec 8, 2012 10:34:52 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

http://docs.oracle.com/cd/B28359_01/server.111/b28319/emca.htm