Making invalid Oracle XML Database component to Valid status

How to  make invalid Oracle XML Database component to Valid state/Reinstall Oracle XML Database component ?

Execute the query in sql prompt and check whether "Oracle XML Database" component is valid or not.
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME                                SCHEMA     STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine             SYS        VALID  11.2.0.2.0
OLAP Analytic Workspace                  SYS        VALID  11.2.0.2.0
OLAP Catalog                                   OLAPSYS    VALID  11.2.0.2.0
Oracle Application Express               APEX_04000 VALID  4.0.2.00.0
Oracle Database Catalog Views            SYS        VALID  11.2.0.2.0
Oracle Database Java Packages            SYS        VALID  11.2.0.2.0
Oracle Database Packages and Types       SYS        VALID  11.2.0.2.0
Oracle Enterprise Manager                SYSMAN     VALID  11.2.0.2.0
Oracle Expression Filter                 EXFSYS     VALID  11.2.0.2.0
Oracle OLAP API                          SYS        VALID  11.2.0.2.0
Oracle Rules Manager                     EXFSYS     VALID  11.2.0.2.0
Oracle Text                              CTXSYS     VALID  11.2.0.2.0
Oracle Workspace Manager                 WMSYS      VALID  11.2.0.2.0
Oracle XDK                               SYS        VALID  11.2.0.2.0
Oracle XML Database                      XDB        INVALID  11.2.0.2.0

This result(Oracle XML Database --- invalid state) may be due to library path set up in Up gradation process or else it is already invalid before using this UTL_SMPT enabling.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Then deinstall XMLDB component using the following scripts and install again.
Deinstalling:
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catnoqm.sql ---- this will deinstall XDM component
SQL> exit
Reinstalling:
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catqm.sql ---- this is to install XDM component
SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql ---- this is to recompile the invalid objects
SQL> exit
  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Now check the status of  component once again as below:
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME                                SCHEMA     STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine             SYS        VALID  11.2.0.2.0
OLAP Analytic Workspace                  SYS        VALID  11.2.0.2.0
OLAP Catalog                                  OLAPSYS    VALID  11.2.0.2.0
Oracle Application Express               APEX_04000 VALID  4.0.2.00.0
Oracle Database Catalog Views            SYS        VALID  11.2.0.2.0
Oracle Database Java Packages            SYS        VALID  11.2.0.2.0
Oracle Database Packages and Types       SYS        VALID  11.2.0.2.0
Oracle Enterprise Manager                SYSMAN     VALID  11.2.0.2.0
Oracle Expression Filter                 EXFSYS     VALID  11.2.0.2.0
Oracle OLAP API                          SYS        VALID  11.2.0.2.0
Oracle Rules Manager                     EXFSYS     VALID  11.2.0.2.0
Oracle Text                              CTXSYS     VALID  11.2.0.2.0
Oracle Workspace Manager                 WMSYS      VALID  11.2.0.2.0
Oracle XDK                               SYS        VALID  11.2.0.2.0
Oracle XML Database                      XDB        VALID  11.2.0.2.0

As a final check find number of invalid components of XDB user
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
no rows selected

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

cheers....!!!!!

High Water Mark in Oracle

What is High Water Mark in Oracle?

High water mark is the maximum amount of database blocks used so far by a segment (table/index etc). We know that when we are inserting data into a segment it will be placed in blocks. So whenever data is added to segment further more blocks will be utilized/allocated. 

For example assume a segment having data a,b,c,d,e shown as below.  Now as per my definition the maximum logical mark utilized by a segment is high water mark. Here my high water mark is from the block (with data a) to block (with data e).

a

b

c

d

E

1. Now some of data is deleted from the segment like below.

a

b

c

D

a

b

c

E

Here since we are doing deletion operation the data will be removed from the block but the block can’t be deallocated from the segment. Hence physically it means it is still using all the blocks. So the high water mark won’t change. High water mark is same as previous.

2. In the below case I am adding some more data to the existing segment. So the high water mark increases furthermore.

a

b

c

e

f

G

3. Now we shall again delete some data from the segment. From case-1 we already know that high water mark can’t be changed or can’t be reset by deletion operation, it will remain same.

a

b

c

e

f

4. if we perform any re-org activity on the segment then the blocks will be aligned with data in proper order and un using blocks will release space.so the high water mark changes now as below from block with data to block with data f .

a

b

c

e

F

5. Similarly truncation of segment also releases space and high water mark will change to initial state when table is created. To know the difference between truncation and deletion see

http://orcl11gdba.blogspot.in/2014/03/truncate-vs-deletion-of-table.html

cheers….!!!!!

Enabling UTL_SMTP/ creation of ACL

 How to enable UTL_SMTP in database level and create ACL,assigning to a user?

UTL_SMTP:

This package is used to send email's from database over SMTP(Simple mail transfer Protocol).This utility enables e-mail messages to be sent from the database (PL/SQL blocks/procs/packages) to any valid e-mail address.
Generally this is useful in database monitoring since e-mails can be sent to supporting DBAs when certain events occur. Using these services we can prepare alerts for any type of monitoring   activities.
Prior to 10g we have to configure UTL_TCP, UTL_SMTP separately. But from 11g with the configuration of  UTL_SMTP will invoke  UTL_TCP automatically.

So now we shall configure this UTL_SMTP using below steps:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-1: Execute the query in sql prompt and make sure that "Oracle XML Database" component is valid.
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME                                SCHEMA     STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine             SYS        VALID  11.2.0.2.0
OLAP Analytic Workspace                  SYS        VALID  11.2.0.2.0
OLAP Catalog                                  OLAPSYS    VALID  11.2.0.2.0
Oracle Application Express               APEX_04000 VALID  4.0.2.00.0
Oracle Database Catalog Views            SYS        VALID  11.2.0.2.0
Oracle Database Java Packages            SYS        VALID  11.2.0.2.0
Oracle Database Packages and Types       SYS        VALID  11.2.0.2.0
Oracle Enterprise Manager                SYSMAN     VALID  11.2.0.2.0
Oracle Expression Filter                 EXFSYS     VALID  11.2.0.2.0
Oracle OLAP API                          SYS        VALID  11.2.0.2.0
Oracle Rules Manager                     EXFSYS     VALID  11.2.0.2.0
Oracle Text                              CTXSYS     VALID  11.2.0.2.0
Oracle Workspace Manager                 WMSYS      VALID  11.2.0.2.0
Oracle XDK                               SYS        VALID  11.2.0.2.0
Oracle XML Database                      XDB        VALID  11.2.0.2.0

15 rows selected.
Here in our case it is valid. This means the component "Oracle XML Database" is properly installed and we can configure the mail service by creating ACL (Access control list) services
Note: if it not valid proceed make it valid by using  the steps shown in(http://orcl11gdba.blogspot.in/2014/04/making-invalid-oracle-xml-database.html)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-2: Find out our mail server. If we don’t know what it is we can find it in /etc/mail/sendmail.cf file.
Now go to the $ORACLE_HOME/rdbms/admin path and execute the below scripts.
sqlplus /nolog
Connect / as sysdba
@?/rdbms/admin/utlmail.sql
------ The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt. UTL_MAIL is not installed by default.
@?/rdbms/admin/prvtmail.plb
------ The prvtmail.plb package internally creates some more supporting packages/procedures and updates/modifies the UTL_MAIL package
Now grant the access on UTL_MAIL to either public or specific user depending upon your security policy
SQL>grant execute on utl_mail to public;
SQL>grant execute on utl_mail to user_1;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-3: Now we shall be setting the parameter smtp_out_server with our mail server ip/hostname as shown below.
SQL>alter system set smtp_out_server ='xxx.xx.xxx.xx:25';
Here the number '25' indicates  port number which is default.
Now we can see the effected settings in dba_network_acls view
SQL>select * from dba_network_acls;

Till now half of our part is done. Rest of part will include creation of acl and privilege provision to the user. Access control lists (ACL) maintains the XML DB repository which maintains access users, permissions, network and resources etc.


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-4: Drop the same named access_control list if it already exist
You can then check what ACLs you have already defined for your database using the following query:
SQL>SELECT any_path FROM resource_view WHERE any_path like '%acls%';

ANY_PATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sys/acls/bootstrap_acl.xml
/sys/acls/mail_access.xml
/sys/acls/ocl_form_version_generation.xml
/sys/acls/ro_all_acl.xml
/sys/acls/utl_mail.xml
/sys/acls/utl_mail_uat1.xml
/sys/apps/plsql/xs/netaclsc.xml

Since the ACL we want to create is already exists drop the old one.
SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'mail_access.xml');
END;
/


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-5: Create an access control list:
Now we don’t have the old ACL in our repository so create new one with same name
SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'user_1/public',  -----------if you want to create ACL for public use public in principal field or else keep the specific user
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-6: Now the ACL for the user is created and we have to assign this list to smtp(mail server)
SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'xxx.xx.xxx.xx',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-7: Now the add privilege to other users also to whom you would like to provide access
SQL>
BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'user_1',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'user_2',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl          => 'mail_access.xml',
    principal    => 'user_3',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'mail_access.xml',
        principal   => 'user_4');
END;
/

Now we can see the ports ,hosts in dba_network_acls
SQL>SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-8: Now the entire setup is done .we can review the ports and the users who got privilege  using below query
SQL>
COLUMN host FORMAT A30
COLUMN acl FORMAT A30

SELECT host, lower_port, upper_port, acl FROM   dba_network_acls;

COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
set lines 200

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;

Note:The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-9:Now test the mail service by creating a small proc/plsql block

begin
UTL_MAIL.SEND(sender => 'pavan3417@gmail.com', recipients => 'pavan3417@gmail.com', subject => 'hello', message => 'mail test',mime_type =>'text/html');
end;


Errors i have  faced during the above process:

while following the above steps i got an error at step-6 as below:

ORA-04063: package body "XDB.DBMS_RESCONFIG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "XDB.DBMS_RESCONFIG"

this means the package DBMS_RESCONFIG owned by XDB is invalid. in order to make it valid i tried to recompile it by executing

SQL>alter package XDB.DBMS_RESCONFIG compile body;
SQL>@?/rdbms/admin/utlrp.sql

but still the package was invalid and i got the below errors in package:

SQL> show errors
Errors for PACKAGE BODY XDB.DBMS_RESCONFIG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
67/8 PL/SQL: SQL Statement ignored
69/26 PL/SQL: ORA-00932: inconsistent datatypes: expected REF got CHAR
179/8 PL/SQL: SQL Statement ignored
181/26 PL/SQL: ORA-00932: inconsistent datatypes: expected REF got CHAR

In order to make it valid i have to reinstall the 'Oracle XML Database' component but it requires downtime ,so i followed other process:

----->executed the script in 733667.1 doc to find invalids(this is optional)
----->conn xdb/password
@?/rdbms/admin/prvtxrc.plb
this will  try to recreate the DBMS_RESCONFIG package
----->conn / as sysdba
@?/rdbms/admin/catmetx
this will modify/alter the package DBMS_RESCONFIG and rebuilds the associated indexes and provides privileges.
@?/rdbms/admin/utlrp
this is to compile invalid objects
 select owner,object_type,count(*) from dba_objects where status='INVALID' and owner ='XDB';

compilation of catmetx cleared the error and made the dbms_resconfig valid

Note:The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql)




cheers...!!!!

Recovering database using cold backup

How to perform cold backup? How to recover database using cold backup?

Taking Cold backup:
As we already know cold backup is offline backup , we have to take it by making database offline/shutdown.
shut down should be graceful shutdown,why because during graceful shutdown the transactions will be completed and every datafile, controlfile will be in sync with respect to SCN.This cold backup can be done even if database is in archivelog mode or no archivelog mode.
if it is in archive log mode we don't need to take redo log files backup because redo logs are already emptied during graceful shutdown and all transactions were written to db files and archive log.so it wont contain further more transactions.
if it in no archivelog mode we can take backup of redo logs also. but in both the cases taking redo log backup is not mandatory.
1.Before shutting down database just verify all the datafiles path and control file path.
sql>select FILE_NAME from dba_data_files;
2.shut down database
sql>shutdown/shut immediate
3.find the mount point/file system for space where we are going to keep our cold backup.
]~ df -kh
4.make a directory for backup.
/u01/oracle/app/oradata/pavan/database_1/COLD_BKP
5.copy the files from datafiles location to /u01/oracle/app/oradata/pavan/database_1/COLD_BKP path. this step is called cold backup.
cp /u01/oracle/app/oradata/pavan/database_1/*.dbf /u01/oracle/app/oradata/pavan/database_1/COLD_BKP
cp /u01/oracle/app/oradata/pavan/database_1/*.ctl /u01/oracle/app/oradata/pavan/database_1/COLD_BKP
6.start the database
sql>startup

we don't need to take backup of temp datafile because it will be automatically created when DB is restarted.If want we can keep the backup of archive logs also in the above steps.

Recovering using Cold backup:
When you have lost any of CRD files and you want to recover database using cold backup then simply shut down the database and restore backup.
1.shut down the database
sql>shut immediate/abort
2.copy the files from backup location to CRD files location. this step is called restoring of database.
cp /u01/oracle/app/oradata/pavan/database_1/COLD_BKP u01/oracle/app/oradata/pavan/database_1/*.dbf
cp /u01/oracle/app/oradata/pavan/database_1/COLD_BKP /u01/oracle/app/oradata/pavan/database_1/*.ctl
3.start the database
sql>startup mount
sql>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
sql>alter database open resetlogs;
 database opened.
cold backup recovery is not required if it is in no-archive log mode(you can directly startup the database).


Cheers.....!!!