Connect to 11gR2 Oracle database with SCAN IP using SQL Developer

1. Open SQL Developer and click + sign for new connection

2. Connection Type: Advanced
Enter the jdbc URL of the Database with scan ip.

3. Click Test, if it shows success then save.

RMAN-06059: expected archived log not found and ORA-19625

Problem:

RMAN-03002: failure of backup command at 03/04/2013 08:00:23
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /opt/oracle/archredo0/foprod1/1_49507_765747965.arc
ORA-27037: unable to obtain file status

Solution:

1. Login rman

fodbsx7.hk.ocean.local:/opt/oracle/archredo0/foprod1$ so -a -s foprod1 rman target /

2. Run crosscheck copy

RMAN> crosscheck copy;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=263 devtype=DISK
validation failed for archived log
archive log filename=/opt/oracle/archredo0/foprod1/1_49507_765747965.arc recid=67567 stamp=809109558
validation failed for archived log
archive log filename=/opt/oracle/archredo0/foprod1/1_49508_765747965.arc recid=67568 stamp=809109559
validation failed for archived log
archive log filename=/opt/oracle/archredo0/foprod1/1_49509_765747965.arc recid=67569 stamp=809109560
validation failed for archived log
archive log filename=/opt/oracle/archredo0/foprod1/1_49510_765747965.arc recid=67570 stamp=809109562
validation failed for archived log
archive log filename=/opt/oracle/archredo0/foprod1/1_49511_765747965.arc recid=67571 stamp=809109564
validation failed for archived log

3. Delete expired copy;

RMAN> delete expired copy;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=263 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
67567   1    49507   X 02-MAR-13 /opt/oracle/archredo0/foprod1/1_49507_765747965.arc
67568   1    49508   X 02-MAR-13 /opt/oracle/archredo0/foprod1/1_49508_765747965.arc
67569   1    49509   X 02-MAR-13 /opt/oracle/archredo0/foprod1/1_49509_765747965.arc
67570   1    49510   X 02-MAR-13 /opt/oracle/archredo0/foprod1/1_49510_765747965.arc

4. Re-run the log backup for check from crontab

fodbsx7.hk.ocean.local:/opt/oracle/archredo0/foprod1$ /usr/local/bin/descron "/opt/oracle/ocean_rman_backup.sh log" "Oracle Backups" logs-oracle

How to change DBID of Oracle Database

use Note: 224266.1 From Metalink

-Check DBID for both target and source instance ( they will be same)

select dbid, name, open_mode, activation#, created from v$database;

Command to change: 

-nid target=sys/manager@TEST_DB

Command to change Password file:

--Backup password file

[orafvnd@db1001]$ mv orapwTEST orapwTEST_old

 -rwSr-----   1 oratest  fvndba      1536 Sep  3 17:05 orapwFVND_old

[oratest@db1001]$ orapwd file=orapwTEST password=manager entries=2

SQL query to check Tablespace Utilization in Oracle Database.

SET LINESIZE 150

SET PAGESIZE 9999

SET VERIFY OFF

COLUMN status FORMAT a9 HEADING 'Status'

COLUMN name FORMAT a25 HEADING 'Tablespace Name'

COLUMN ts_size FORMAT 999999.99 HEADING 'Tablespace Size'

COLUMN used FORMAT 999999.99 HEADING 'Used (in megabytes)'

COLUMN free FORMAT 999999.99 HEADING 'Free (in megabytes)'

COLUMN pct_used FORMAT 999.99 HEADING 'Pct. Used'

SELECT d.tablespace_name name,

       d.status status,

       NVL(a.bytes, 0)/1024/1024 ts_size,

       NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used,

       NVL(f.bytes, 0)/1024/1024 free,

       NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

  FROM sys.dba_tablespaces d,

       ( select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,

       ( select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = f.tablespace_name(+)

ORDER BY pct_used desc;

Sample output:

Install Oracle XE Database on Linux/Centos (Oracle Express Database Installation)

Download Oracle XE DB repository and place it in the server

Link: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

[root@test linux]# ls -rlt oracle*
-rw-r--r-- 1 root root 315891481 Dec 17 2013 oracle-xe-11.2.0-1.0.x86_64.rpm.zip
[root@test linux]# pwd
/var/software/linux

1.  Install and Configure Centos 6.3, with required rpm packages

After installing Centos 6.3, be sure to install the following rpm packages.

□       LIBAIO: libaio-0.3.107-10.el6.x86_64.rpm

□       BC: bc-1.06.tar.gz

Connect to DB Server and log in as root

# yum install libaio bc

or        

# rpm –Uvh package_name.rpm        download the rpm package file if no internet access available;

Note: Type y when prompted for confirmation; Type y when prompted for warning on the GPG key. You should see the Complete!

2.  Install Oracle11g XE

2.1

linux =>
(root)
groupadd dba
useradd oracle -g dba -G dba

2.2

Create Oracle installation folder:

# mkdir /opt/oracle

Copy oracle-xe-11.2.0-1.0.x86_64.rpm.zip to /opt/Oracle folder,

# cd /opt/oracle

# unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip;

Modify kernel parameters,

# vi /etc/sysctl.conf

add the following parameters

kernel.shmall = 2097152

kernel.shmmni = 4096

Verify the change,

# /sbin/sysctl –p

2.3

Switch to folder Disk1,

# cd Disk1

# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

Centos VM may not accept some kernel parameters and just ignore the error message.

3.  Configure Oracle11g XE

3.1

Configure oracle database,

# /etc/init.d/oracle-xe configure

Unless you want to change the port numbers, you only need to set the password for SYS and SYSTEM

The installation process will create directory /u01, which Oracle XE will be installed

3.2

Set required Oracle environment variables, login in as oracle,

# su - oracle

# vi /home/oracle/.bash_profile

And add the following lines after export PATH

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

export ORACLE_SID=XE

export ORACLE_BASE=/u01/app/oracle

export PATH=$ORACLE_HOME/bin:$PATH

LSNR=$ORACLE_HOME/bin/lsnrctl

ORACLE_OWNER=oracle

LOG="$ORACLE_HOME_LISTNER/listener.log"

Or

Add the following line to the .bashrc or .bash_profile of the users you want to access the environment:

/u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

Test the access to Oracle database,

# sqlplus /nolog

# connect sys/password as sysdba

To allow remote access to Oracle 11g XE GUI (as well as Application Express GUI),

EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

You should now be able to access the Oracle 11g XE Home Page GUI at:

http://ip_address:8080/apex/f?p=4950:1

ip_address is your server IP and 8080 is the default listener port;

ORA-00020: maximum number of processes

How to check and resolve : ORA-00020: maximum number of processes

1. Login into the Oracle Database and if you cannot sqlplus into it. Try killing a few oracle server processes.

ps -ef|grep oracle
kill -9

2. Try to sqlpus now, check existing sessions:

select inst_id, username, machine, program, status, count(*) "Connections"
from gv$session
where username is not null
group by inst_id, username, machine, program, status
order by 1, 6 desc;

3. Increase the number of processes and sessions

SQL> alter system set processes=700 scope=spfile sid='*';

System altered.


SQL> alter system set sessions=1072 scope=spfile sid='*';

System altered.

4. Bounce the db

if Oracle RAC

[oracle@ test-db2 ~]$ srvctl stop database -d DB_NAME

[oracle@ test-db2 ~]$ srvctl start database -d DB_NAME

[oracle@ test-db2 ~]$ srvctl status database -d DB_NAME
Instance TEST1 is running on node test-db1
Instance TEST2 is running on node test-db2

5. Verify if the parameters are increased using the below query

select * from gv$resource_limit

where resource_name in ('processes', 'sessions')

order by 2, 1;

For example:

Compiling Invalid Objects in Oracle Database

 UTLRP.sh (script)

1^st – sshsu ora _ _ _ _

2^nd – Check to make sure utlrp.sh script is in ORACLE_HOME

>cd $ORACLE_HOME

>cd rdbms/admin/utlrp.sh

3^rd – logon to sqlplus (as sysdba)

            >sqlplus ‘/as sysdba’

4^th – Run query to count how many invalid objects in the instance

            > SELECT COUNT(*) "num_invalid_objects"

   FROM DBA_OBJECTS

   WHERE STATUS = 'INVALID'

   and OWNER NOT IN ('NOETIX', 'NOETIX_SYS');

5^th – Run the UTLRP script

            >@utlrp

6^th – Run the query to count invalid objects again, the number must go down.

            > SELECT COUNT(*) "num_invalid_objects"

   FROM DBA_OBJECTS

   WHERE STATUS = 'INVALID'

   and OWNER NOT IN ('NOETIX', 'NOETIX_SYS') ;