Installing Oracle 11gR2 (11.2.0.2) on Solaris 5.10 for Standalone Database on file system

Installation of Oracle 11gR2 on Solaris

Installing 11.2.0.2, as per oracle from 11.2.0.2 onwards we can direclty move to 11.2.0.2 ( from compatible version) & don't have to first install base release & then patch, that is don't have to install 11.2.0.1 & then patch to 11.2.0.2, so installing 11.2.0.2 directly

Please note that this installation is for a Standalone database on filesystem so it is not using the Grid installation required for supporting ASM & Oracle Re-start

Checking Hardware Requirements

memory reqs

hostname:/app/oracle/product/11.2:>/usr/sbin/prtconf | grep "Memory size"

Memory size: 10240 Megabytes <-----------10GB

atleast 2GB of RAM required, so we are good here

System Architecture

hostname:/export/home/oracle:>/bin/isainfo -kv

64-bit sparcv9 kernel modules

Checking run level

hostname:/export/home/oracle:>who -r

   .       run-level 3  Feb 20 16:46     3      0  S

Checking software requirements

hostname:/export/home/oracle:>uname -r

5.10

Checking release

hostname:/export/home/oracle:>cat /etc/release

                      Solaris 10 10/08 s10s_u6wos_07b SPARC

           Copyright 2008 Sun Microsystems, Inc.  All Rights Reserved.

                        Use is subject to license terms.

                            Assembled 27 October 2008

Verfiying Packages

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibms SUNWsprot \ SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

hostname:/export/home/oracle:>pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibms SUNWsprot \

 SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

>  SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

system      SUNWarc   Lint Libraries (usr)

system      SUNWbtool CCS tools bundled with SunOS

system      SUNWhea   SunOS Header Files

system      SUNWi15cs X11 ISO8859-15 Codeset Support

system      SUNWi1cs  X11 ISO8859-1 Codeset Support

system      SUNWi1of  ISO-8859-1 (Latin-1) Optional Fonts

system      SUNWlibC  Sun Workshop Compilers Bundled libC

system      SUNWlibms Math & Microtasking Libraries (Usr)

system      SUNWsprot Solaris Bundled tools

system      SUNWtoo   Programming Tools

system      SUNWxwfnt X Window System platform required fonts

Verifying Operating System Patches

hostname:/usr/bin:>/usr/sbin/patchadd -p | grep 120753

Patch: 120753-05 Obsoletes: Requires: Incompatibles: Packages: SUNWlibm SUNWlibmsr

hostname:/usr/bin:>/usr/sbin/patchadd -p | grep 139574

hostname:/usr/bin:>/usr/sbin/patchadd -p | grep 139574

Patch: 139555-08 Obsoletes: 120062-01 121130-01 125551-02 126264-01 127743-02 127853-02 128253-01 128296-01 128318-01 128322-01 128340-01 128406-01 137095-01 137106-01 137278-01 138058-01 138106-02 138114-01 138231-01 138241-05 138397-01 138639-01 138850-01 138864-01 138878-01 138888-08 139385-02 139458-01 139459-01 139466-04 139483-05 139489-03 139492-01 139494-01 139498-04 139500-04 139502-03 139506-01 139551-01 139558-03 139560-02 139562-02 139566-01 139570-05 139571-01 139572-02 139574-03 139579-04 139931-01 139936-01 139938-01 139939-01 139942-01 139947-01 139951-01 139952-01 139960-02 139965-01 139994-01 140000-04 140076-02 140077-01 140142-01 140173-01 140194-04 140196-01 140197-01 140334-01 140389-01 140402-01 140411-01 140677-01 140679-01 140774-03 140776-01 140794-01 140855-01 141006-01 141008-01 Requires: 125555-02 118731-01 118833-36 118918-24 119042-09 119254-41 119578-30 120011-14 120900-04 121133-02 126897-02 127127-11 127755-01 137137-09 138217-01 140796-01 140899-01 141016-01 Incompatibles: Packages: SUNWcsd SUNWzoneu SUNWzfsr SUNWmdr SUNWnfsckr SUNWsshdu SUNWsshu SUNWmdbr SUNWcry SUNWarc SUNWloc SUNWdcsr SUNWtoo SUNWsshcu SUNWfruip SUNWcakr SUNWmdu SUNWzfsu SUNWsckmu SUNWpiclu SUNWesu SUNWperl584core SUNWudapltu SUNWopenssl-libraries SUNWib SUNWudfr SUNWpool SUNWmdb SUNWsckmr SUNWxcu4 SUNWpd SUNWudaplu SUNWnfssu SUNWtavor SUNWibsdpib SUNWfmd SUNWssad SUNWcslr SUNWnxge SUNWroute SUNWsmapi SUNWhea SUNWipfu SUNWkvm SUNWckr SUNWbtool SUNWcsu SUNWzfskr SUNWefcl SUNWdtrc SUNWdtrp SUNWfmdr SUNWcpcu SUNWcsr SUNWcsl SUNWwbsup

hostname:/usr/bin:>/usr/sbin/patchadd -p | grep 141444

hostname:/usr/bin:>/usr/sbin/patchadd -p | grep 141414

Patch: 141414-02 Obsoletes: 141729-01 Requires: 118833-36 120011-14 127127-11 137137-09 139555-08 Incompatibles: Packages: SUNWmdbr SUNWcakr SUNWzfsu SUNWmdb SUNWcslr SUNWhea SUNWckr SUNWdtrc

Noteà The patches have to be checked in full 120753-14, the underlying OS had 120753-10, since I missed it I had this pop up later in pre-installation checks

Verifying UDP and TCP Kernel Parameters

hostname:/usr/bin:>/usr/sbin/ndd /dev/tcp tcp_smallest_anon_port tcp_largest_anon_port

32768

65535

Checking oracle inventory group & path of oracle inventory directory

hostname:/var/opt/oracle:>more /var/opt/oracle/oraInst.loc

inventory_loc=/app/oracle/product/oraInventory

inst_group=dba

Checking Shell Limits:

hostname:/var/opt/oracle:>ulimit -t

unlimited

hostname:/var/opt/oracle:>ulimit -f

unlimited

hostname:/var/opt/oracle:>ulimit -d

unlimited

hostname:/var/opt/oracle:>ulimit -s

8192

hostname:/var/opt/oracle:>ulimit -n

65536

hostname:/var/opt/oracle:>ulimit -v

unlimited

Had to alter ulimit –s, once root user fixed, I verified it after exiting/logging back in

hostname:/var/opt/oracle:>exit

$ sudo su - oracle

Password:

hostname:/export/home/oracle:>ulimit -s

32768

Checking Kernel Parameters:

hostname:/export/home/oracle:>prctl -n project.max-shm-memory -i project Oracle

project: 200: Oracle

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-shm-memory

        privileged      12.0GB      -   deny                                 -

        system          16.0EB    max   deny                                 -

hostname:/export/home/oracle:>prctl -n project.max-sem-ids -i project Oracle

project: 200: Oracle

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-sem-ids

        privileged      1.02K       -   deny                                 -

        system          16.8M     max   deny                                 -

hostname:/export/home/oracle:>cat /etc/project

system:0::::

user.root:1::::

noproject:2::::

default:3::::

group.staff:10::::

Oracle:200:Database:oracle:dba:process.max-sem-nsems=(priv,2048,deny);project.max-sem-ids=(priv,1024,deny);project.max-shm-ids=(priv,256,deny);project.max-shm-memory=(priv,12884901888,deny)

hostname:/export/home/oracle:>prctl -n project.max-shm-memory -i process $$

process: 10083: -ksh

NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT

project.max-shm-memory

        privileged      12.0GB      -   deny                                 -

Before beginning installation note to set tmp if inadequately sized, also on Solaris, it is to be exported as TEMP & not TMP

export TEMP=/var/tmp ( some place which has space)

this is because.. on solaris
export TEMP & not export TMP
i've used
export TEMP=/var/tmp

Run the fix up script

I had a issue running this script, as previous runs created a project "oracle.user" , this is in /etc/project in solaris. you can manually set the port range with "root" access by using these commands
　If necessary for your anticipated workload or number of servers , update the UDP and TCP ephemeral port range to a broader range. For example:# /usr/sbin/ndd -set /dev/tcp tcp_smallest_anon_port 9000
# /usr/sbin/ndd -set /dev/tcp tcp_largest_anon_port 65500
# /usr/sbin/ndd -set /dev/udp udp_smallest_anon_port 9000
# /usr/sbin/ndd -set /dev/udp udp_largest_anon_port 65500　
rerunning the check.. completed successfully

now run the root.sh script as "root" user

running root.sh script it will ask if you want to overwrite, before saying yes.. backup the files in some other location.# /app/oracle/product/11.2/dbhome_1/root.sh
Running Oracle 11g root script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /app/oracle/product/11.2/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

RMAN DB BLOCK RECOVERY

Performing block recovery using RMAN

1)  First let's corrupt a block.

a)create a user with dba role

creating a user and granting privilege in a single sql statement.

SQL> grant dba to noxi identified by abc123;

Connect to the noxi user.

Creating a table, and inserting a row

SQL> create table tbl_corruption ( id number );

Table created.

SQL> insert into  tbl_corruption values (1);

2) Now let's take full backup plus archive logs  using RMAN .

RMAN>backup database plus archivelog;

backup completed.

3) Now let's check the header_block # from dba_segement

SQL> select header_block from dba_segments where segment_name='TBL_CORRUPTION';

HEADER_BLOCK

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

          59

The block no corresponding  to the table tbl_corruption

4) Now find out  the datafile belonging to the table 'tbl_corruption'.

SQL> select a.name from v$datafile a,dba_segments b where a.file#=b.header_file and b.segment_name='TBL_CORRUPTION';

NAME

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

/u14/oradata/stayalive/system01.dbf

5) Now let's corrupt the block belonging to the table tbl_corruption.

production:(stayalive)$  dd of=/u14/oradata/stayalive/users01.dbf bs=8192 conv=notrunc seek=60 <<EOF

6) Now connect to the sql prompt using the username noxi.

Now flush the buffer cache,then try to select from the table 'tbl_corruption'.

If you dont flush the buffer cache and query the table 'tbl_corruption',the output is fed from buffer cache instead of going to the  datafile.

SQL>alter system flush buffer_cache;

7)   SQL> select * from tbl_corruption;

select * from tbl_corruption

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 4, block # 60)

ORA-01110: data file 4: '/u14/oradata/stayalive/users01.dbf'

Now  when  we  query table tbl_corruption, we get error as expected because as we  have manualy  corrupted the block corresponding to the table tbl_corrruption

8) connect to rman for restore and recovery purpose.

production:(stayalive)$ rman target /

restoring and recovering the block using rman with the following command.

RMAN> blockrecover datafile 4 block 60;

 Restore and recovery completed.

9)    Now let's check again the table 'tbl_corruption'

    SQL> select * from tbl_corruption;

        ID

----------

         2

TDPO notes

Something to digest.. hopefully it helps

TDPO--> TIVOLI DATA PROTECTION FOR ORACLE

• Tivoli Storage Manager (TSM) is the main software.
• Data Protection (There are multiple for different dbs' Oracle, SQL Server etc) is specifically for backing up and restoring the databases.
• You can't install TDP without TSM installed first, TDPO is a component of TSM
• TDPO provides an interface between RMAN API & TSM API
• With TDPO, RMAN controls how the backups are stored, maintained on storage(TSM)
• WITH TDPO, DBA doesn't need to know the commands used by TSM to backup, maintain files on tape(storage)
• RMAN controls removal of expired backups (TSM policy doesn't govern rman backups)
• Oracle backups (oracle objects) are considered active from TSM perspective

 

User on Windows ORA-01031 Unable to connect "sys / as sysdba"

User on Windows 2008 

ORA-01031: insufficient privileges while connecting as "sys / as sysdba"

Things are different in windows environment, as a DBA you will encounter it sooner than later


One of them is trying to login to database on a Windows server I wasn't able to connect as sysdba.


On Windows 2008 R2
start--> run--> cmd
opens up command prompt
C:\Users\youruser>sqlplus / as sysdba
You are greeted with ORA-01031: insufficient privileges


Error suggests insufficient privileges, solution--->
Check if the user who is executing this command "sqlplus / as sysdba" is part of ORA_DBA groups.
There are few groups in windows Administrators, ORA_DBA & ORA_OPER, just like we have dba, oper, orainstall.. .etc on unix.
On a side note for installing Oracle you need to be a part of Administartors group.
Groups creation is the responsibility of Administrator


Ok back to the note to avoid this error you have to make sure "youruser" is a part of ORA_DBA group
we will see how this is done
on windows
start--> programs---> administrative tools--> computer management

This will open a console/window
Under computer management-->
select "local users and groups" ---> "groups"---> open "ora_dba "

click the "Add" button

Enter the object name ---> "youruser" ---> check names & then ok
this will add your user to ORA_DBA group


Now you can connect to database as sys / as sysdba
or
Connect to RMAN without error message ORA-01031

FLASH RECOVERY AREA

FLASH RECOVERY AREA (FRA)

******************************************************************************
Important note, this is an attempt to understand the basics of Oracle Database administration, please ensure you verify it with Oracle documentation
****************************************************************************************


Typically backups are stored in backup location
for example on a server we have assigned the backup location as "/backup"

Once we have decided on a backup location,  we can configure RMAN to send backups to this location.
for ex-->
RMAN> configure channel device type disk format '/backup/ora_%U';

Before FRA the backups would eventually fill up the file system(reserved for backups, in our case "/backup") & needed DBA attention to ensure.. there is certain space available or else backups fails because of inability to write on this location.

One of the benefits of using FRA, where in a DBA defines what files are to be backed up in FRA, depending on the size & retention policy/settings defined in Database, RMAN will clear up the backup location (depending on recovery/retention settings) & thus avoid any failure of backups for lack of space without any DBA intervention.

so DBA's from 10g version have the option of defining FRA
Q) What to look out for while defining FRA-->
     FRA location, size & retention settings

Q) What kind of files can be placed in FRA?
      Archived logs, RMAN backups & other recovery related files

This Q can also be answered in another way--> permanent & transient files
Permanent files--> mirror copies of redologs, multiplexed copies of current control files
Tranisent files--> archived logs, copy of control file, control file auto-backups, backup pieces, copies of datafiles, incase of Flashback DB ( flashback logs)

Another advantage of FRA
Database governs the files in FRA & retains those which are ONLY required for recovery.

Q) What can be the location for FRA--->
      Any file system  or diskgroup but NOT raw file system.

Q) How to enable FRA
      First set db_recovery_file_dest_size & then db_recovery_file_dest
     
To enable fra
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/flash_recovery_area' SCOPE=BOTH SID='*';

For ASM
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+diskgroupname' SCOPE=BOTH SID='*';
Q) How to disable FRA
To disable

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";

Q)  How to find the size & allocation of FRA
      select name, space_limit, space_used, space_reclaimable from v$recovery_file_dest;
      
      View to find out the percentage of the total disk quota used by different types of files  
   SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
SQL>SELECT name, space_limit/1024/1024/1024 "AllocSpaceGB", space_reclaimable/1024/1024/1024 "spacereclGB", number_of_files FROM V$RECOVERY_FILE_DEST;

NAME                                     AllocSpaceGB spacereclGB
---------------------------------------- ------------ -----------
H:\oracle\oradata\dpapp316\flash_recover           40           0
y_area

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                 9.72                         0             228
BACKUPPIECE               65.48                         0              19
IMAGECOPY                     0                         0               0
FLASHBACKLOG                  0                         0               0
6 rows selected.

Datapump Utility to Import tables into another schema

Using Datapump utility to import tables

Scenario:
Previous night export dumps
I have them stored as------> somename.dmp.Z ( compressed by using "compress" unix command)
$ uncompress somename.dmp.Z
$ somename.dmp             <-------------------- uncompressed dumpfile

user A is importing tables into user B ( user B owns those tables)
User A should have appropriate priviliges to import,  read/write on datapump directory & unlimited on tablespace quota

Truncate tables owned by B,
SQL> Truncate table B.table1

At this point we have table structure of table1 in schema B but no data
sql> select count(*) from B.table1
            0

As user A run the impdp
Using vi editor create a file say--> imp.sh ( can use it to run in background, as newbie's often execute it on command prompt only to realize they are stuck in front of computer)

$ vi imp.sh
impdp "userA/psswd" directory=DBA_DP_DIR dumpfile=latestexportdumpbackup.dmp logfile=somename.log tables=B.table1,B.table2 CONTENT=DATA_ONLY


                                 OR
****** if you have multiple dumpfiles to choose from ************

impdp "userA/psswd" directory=DBA_DP_DIR dumpfile=latest_expdump_backup_1.dmp, latest_expdump_2.dmp,.... ,latest_expdump_n logfile=somename.log tables=B.table1,B.table2,...,B,tablen CONTENT=DATA_ONLY

$ nohup ./imp.sh &
you can tail  the import process
$ tail -f nohup.out

Well this is an effort to get started, hoping to refine as we move ahead. Suggestions are welcome!