Tuesday, April 5, 2011

Clonning Through DBCA


Cloning is just about creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in testing environment . Here is one of the easiest way to clone a database . We can clone a database in many different ways .

1.) Clonning using backup of Controlfile
2.) Clonning through DBCA 
3.) Clonning using  OEM 
4.) Clonning using  RMAN Backup

Clonning through DBCA  is one of the least used way to clone or duplicate an Oracle DB but it is the best one. To promote it I write this post. Let's have a look on the steps .

1.) Open the command prompt and type "DBCA" . On the "Welcome" screen click the "Next" button.

2.) On the "Operations" screen select the "Manage Templates" option and click the "Next" button.

3.) On the "Template Management" screen select the "Create a database template" option and select the "From and existing database (structure as well as data)" sub-option then click the "Next" button.

4.) On the "Source database" screen select the relevant database instance and click the "Next" button.

5.) On the "Template properties" screen enter a suitable name and description for the template, confirm the location for the template files and click the "Next" button.

6.) On the "Location of database related files" screen choose either to maintain the file locations or to convert to OFA structure (recommended) and click the "Finish" button.

7.) On the "Confirmation" screen click the "OK" button.

8.) Wait while the Database Configuration Assistant progress screen gathers information about the source database, backs up the database and creates the template.

9.) Depending upon the size of the database it will take some time. For my 8 Gig database, it took like 8 mins. Now we have a template created and we will use to create our new database.

10.) Click on "Next Operation".

11.) Select "Create a Database" option and click "Next".

12.) In "Select a template from the following list to create a database" - select the template name which you provided in Step 6 and click "Next".

13.) Provide the new Service Name for the new database. The SID will automatically be set to the service name entered above. Click "Next".

14.) Let the "Configure the Database with Enterprise Manager" remain checked and "Use Database Control for Database Management" remain checked. Click "Next".

15.) Provide the sys password and click "Next".

16.) Let the "File System" option remain checked unless you want to use ASM or raw for your new database.

17.) Let the "Use Database File Locations from Template remain checked. This is important. Click "Next".

18.) Let the default values for Flash Recover Area remain as they are and click "Next".

19.) Let the "No Scripts to run" remain checked an click "Next".

20.) You can keep the default values for Memory and Sizing over here or change it as per your need and Click "Next".

21.) You are now at the final screen wherein you can all your configurations and verify that they are correct. Clicking next, DBCA will do all your job and your DB should be up and running in next 15-20 mins.

22.) Finally before logging in to the new DB using EM, check the tnsnames.ora and see an entry is created for the new database else add one. You can add a new listenere too in you listener.ora if you want and the do a "lsnrctl reload" to reload the listeners.

23.) Finally do a tnsping on your new database to check all's fine.

24.) Log in using EM and you should have you DB ready in Open mode.

25.) Note all user accounts besides the system account are locked and expired so you need to unlock them to allow users to connect to the new DB.

26.) The whole process took some 30-35 mins and it was all GUI  and no scripts or errors. Seem to be the best way out to duplicate an Oracle 10g database.   


ENJOY   :-)


What is Alert Log File ?

The alert log file is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.Oracle will automatically create a new alert log file whenever the old one is deleted.

When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Worldwide Support. Trace file information is also used to tune applications and instances.

The alert log of a database includes the following information : 
1. ) All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) that occur.
2.) Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements.
3.) Messages and errors relating to the functions of shared server and dispatcher processes.
4.) Errors occurring during the automatic refresh of a materialized view.
5.) The values of all initialization parameters that had non-default values at the time the database and instance startup .

which process writes to alert log file?
Not "one" but all the background processes can/do write to it. The archiver writes to it. LogWriter can write (if we have log_checkpoints_to_alert). When a background process detects that another has died, the former writes to the alert log before panicking the instance and killing it.  Similarly an ALTER SYSTEM command issued by the server process for our database session will also write to the alert.log .

To find the location of alert log file we can find by below command 
SQL > select value from v$parameter where name = 'background_dump_dest' ;   OR 
SQL> show parameter background

If the background_dump_dest parameter is not specified, Oracle will write the alert.log into the $ORACLE_HOME/RDBMS/trace directory.


Enjoy    :-)



Friday, April 1, 2011

Moving Audit Table Out Of SYSTEM Tablespace


Database auditing is the process of recording, monitoring and reporting of the actions performed on a database. AUD$ is the underlying table that holds all of the system auditing information which resides in SYSTEM tablespace. We keep on deleting and truncating the Aud$ table so that it doesnot grow large. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace. 


Until 11g, the way to move out SYS.AUD$ is not supported. If  we want to do it then we to do it manually . In 11g we can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION  provided with the DBMS_MGMT package .So below are the steps .

1.) Connect to database as SYS user.
C:\> sqlplus sys/xxxx@noida  as sysdba

2.) Create a tablespace for audit file as
SQL>create tablespace aud_tbs datafile 'C:\app\Neerajs\oradata\noida/audit.dbf'  size 10M autoextend on ;

3.) Create a table inside aud_tbs tablespace as
SQL> create table aud_tab tablespace aud_tbs  as select * from sys.aud$ where 1=2 ;

4.) Rename the Original Audit table as 
SQL> rename aud$ to aud$_org ;

5.) Rename table  aud_tab  to AUD$
SQL> rename aud_tab  to aud$ ;

Hence, auditing record will be stored in the aud_tbs tablespace .Further, we can create an index on the aud_tbs table for quick access .


Enjoy     :-) 


How to get port number list of Enterprise Manager and isqlplus

 During installation, Oracle Universal Installer assigns port numbers to components from a set of default port numbers.  Many Oracle Database components and services use ports. It is important to know the port numbers used by these services, and to make sure that the same port number is not used by two services on our host.
The portlist information of enterprise manger or isqlplus or others web services are find out at the location  $ORACLE_HOME\install\portlist.ini. In my case the  following details can found in ini file .

iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (noida) = 5500
Enterprise Manager Agent Port (noida) = 1830
Enterprise Manager Console HTTP Port (delhi) = 5501
Enterprise Manager Agent Port (delhil) = 1831

Note: The ports that are in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation


Enjoy       :-) 



Oracle default port list


The following table contains Oracle default ports for different products like Oracle Database or Oracle Application Server.Changing the default ports can help to stop simple attacks but not real portscans. In the world of Oracle it is very often not possible to change the default port because the port is hardcoded. At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else.

The IANA default port number can be found here: http://www.iana.org/assignments/port-numbers

Service

Port

Product

How to change

Oracle HTTP Server listen port / Oracle HTTP Server port80Oracle Application ServerEdit httpd.conf and restart OHS
Oracle Internet Directory(non-SSL)389Oracle Application Server
Oracle HTTP Server SSL port443Oracle Application ServerEdit httpd.conf and restart OHS
Oracle Internet Directory(SSL)636Oracle Application Server
Oracle Net Listener / Enterprise Manager Repository port1521Oracle Application Server / Oracle DatabaseEdit listener.ora and restart listener
Oracle Net Listener1526Oracle DatabaseEdit listener.ora and restart listener
Oracle Names1575Oracle DatabaseEdit names.ora and restart names server
Oracle Connection Manager (CMAN)1630Oracle Connection ManagerEdit cman.ora and restart Connection Manager
Oracle JDBC for Rdb Thin Server1701Oracle Rdb
Oracle Intelligent Agent1748Oracle Application Serversnmp_rw.ora
Oracle Intelligent Agent1754Oracle Application Serversnmp_rw.ora
Oracle Intelligent Agent1808Oracle Application Serversnmp_rw.ora
Oracle Intelligent Agent1809Oracle Application Serversnmp_rw.ora
Enterprise Manager Servlet port SSL1810Oracle Enterprise Manager
Oracle Connection Manager Admin (CMAN)1830Oracle Connection Manager (CMAN)Edit cman.ora and restart Connection Manager
Enterprise ManagerAgent port1831Oracle Enterprise Manager
Enterprise Manager RMI port1850Oracle Enterprise Manager
Oracle XMLDB FTP Port2100Oracle Databasechange dbms_xdb.cfg_update
Oracle GIOP IIOP2481Oracle DatabaseEdit listener.ora/init.ora and restart listener/database
Oracle GIOP IIOP for SSL2482Oracle DatabaseEdit listener.ora/init.ora and restart listener/database
Oracle OC4J RMI3201Oracle Application Server

Oracle OC4J AJP3301Oracle Application Server

Enterprise Manager Reporting port3339Oracle Application ServerEdit oem_webstage/oem.confand restart OHS
Oracle OC4J IIOP3401Oracle Application Server

Oracle OC4J IIOPS13501Oracle Application Server

Oracle OC4J IIOPS23601Oracle Application Server

Oracle OC4J JMS3701Oracle Application Server

Oracle9iAS Web Cache Admin port4000Oracle Application ServerWebcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Invalidation port4001Oracle Application ServerWebcache Admin GUI or webcache.xml
Oracle9iAS Web Cache Statistics port4002Oracle Application ServerWebcache Admin GUI or webcache.xml
Oracle Internet Directory(SSL)4031Oracle Application Server
Oracle Internet Directory(non-SSL)4032Oracle Application Server
OracleAS Certificate Authority (OCA) - Server Authentication4400Oracle Application Server
OracleAS Certificate Authority (OCA) - Mutual Authentication4401Oracle Application Server
Oracle HTTP Server SSL port4443Oracle Application ServerEdit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(SSL) port4444Oracle Application ServerWebcache Admin GUI or webcache.xml
Oracle TimesTen4662Oracle TimesTen
Oracle TimesTen4758Oracle TimesTen
Oracle TimesTen4759Oracle TimesTen
Oracle TimesTen4761Oracle TimesTen
Oracle TimesTen4764Oracle TimesTen
Oracle TimesTen4766Oracle TimesTen
Oracle TimesTen4767Oracle TimesTen
Oracle Enterprise Manager Web Console5500Oracle Enterprise Manager Web
iSQLPlus 10g5560Oracle i*SQLPlus
iSQLPlus 10g5580Oracle i*SQLPlus RMI Port
Oracle Notification Service request port6003Oracle Application Server
Oracle Notification Service local port6100Oracle Application Server
Oracle Notification Service remote port6200Oracle Application Server
Oracle9iAS Clickstream Collector Agent6668Oracle Application Server
Java Object Cache port7000Oracle Application Server
DCM Java Object Cache port7100Oracle Application Server
Oracle HTTP Server Diagnostic Port7200Oracle Application Server
Oracle HTTP Server Port Tunneling7501Oracle Application Server
Oracle HTTP Server listen port / Oracle HTTP Server port7777Oracle Application ServerEdit httpd.conf and restart OHS
Oracle9iAS Web Cache HTTP Listen(non-SSL) port7779Oracle Application ServerWebcache Admin GUI or webcache.xml
Oracle HTTP Server Jserv port8007Oracle Application Server
Oracle XMLDB HTTP port8080Oracle Databasechange dbms_xdb.cfg_update
OC4J Forms / Reports Instance8888Oracle Developer Suite
OC4J Forms / Reports Instance8889Oracle Developer Suite
Oracle Forms Server 6 / 6i9000Oracle Application Server
Oracle SOAP Server9998Oracle Application Server
OS Agent14000Oracle Application Server
Oracle Times Ten15000Oracle Times Ten
Oracle Times Ten15002Oracle Times Ten
Oracle Times Ten15004Oracle Times Ten



Enjoy      :-)