Purging statistics from the SYSAUX tablespace

/http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

10.2.0.5 patch application and database upgrade from 10.2.0.4-hp-ux

10.2.0.5 patch application and database upgrade from 10.2.0.4-hp-ux

 Applying 10.2.0.5 patch and Database Upgrade:

 Introduction: Database patching primarily involves the following steps:

 1. Applying the patch using OUI.

 2. Upgrade the database to a higher version using Manual upgrade.

 Environment Details: Server Name LAB2.server.org(HP-UX)

Database Name LAB2 Current Oracle DB Version 10.2.0.4.0

Oracle Home Location /oraLAB201/u01/app/oracle/product/10.2.0/LAB2

 Listener Name LISTENER_LAB2

 Pre-checks &

Preparations before applying 10.2.0.5 patch set:

 1. Set the ORACLE_HOME and ORACLE_SID Environment Variables

 Enter the following commands to set the ORACLE_HOME and ORACLE_SID
 environment variables:
 $ ORACLE_HOME=/oraLAB201/u01/app/oracle/product/10.2.0/LAB2
$ ORACLE_SID= LAB2
$ export ORACLE_HOME ORACLE_SID

2. Shut Down Oracle Databases and related Oracle processes
 Shut down any existing Oracle Database instances with normal or immediate priority.
 $ emctl stop dbconsole
 $ lsnrctl stop LISTENER_ LAB2
 $ sqlplus “/ as sysdba”
 SQL> SHUTDOWN IMMEDIATE

 3. Back Up the Oracle Database and Oracle Home:

 Oracle recommends that you create a backup of the Oracle Inventory, Oracle 10g home and Oracle 10g Database before you install the patch set.

 Command to backup Oracle Home:

 tar -cvf orahome_.tar $ORACLE_HOME 


 Make sure that the latest full backup of the database was successful before proceeding with patch installation. 


 Also, take a backup of the following files: o Init.ora or spfile.ora,Tnsnames.ora,Listener.ora


 4. Comment out all the jobs scheduled for the database until the upgrade is over.


 $ crontab –e Comment the entries related to LAB2 database. 


 5. Display settings for GUI: If you are not installing the software on the local computer, then perform the following steps on remote machine (LAB2.server.org). 


 1. Enable the X11 option in Putty. Navigate as shown below. 


 Putty configurationàSSHàEnable X11(after loading the server in putty). 


 2. Launch the Xming application. 


 3. Execute XCLOCK command in the Usetxora02 server and it should give the clock window. 6. 


Initializing the Oracle Universal Installer: 


 Enter the following commands to start Oracle Universal Installer, 


where /home/oracle/patchdir/10205_Patch is the directory where you unpacked the patch set software: 


 $ cd /home/oracle/patchdir/10205_Patch /Disk1 


 $ ./runInstaller


 On the Welcome screen, click Next. 


On the Specify Home Details screen, 


select the name of the Oracle home that you want to update (10.2.0.4 in this case), 


or select the Path that you want to update from the list, then click Next.


 On the Product-specific Prerequisite Checks screen, 


correct any reported errors, and click Next. On the Summary screen, 


click Install. 


This screen lists all of the patches available for installation.


 When prompted, 


run the $ORACLE_HOME/root.sh script as the root user. 


(Contact the system admin to run this script)


. On the End of Installation screen, 


click Exit,


 then click Yes to exit from Oracle Universal Installer. 


 Post Installation Tasks Log in as the Oracle software owner user


. Set the values for the environment variables


 $ORACLE_HOME, 


$ORACLE_SID and 


$PATH. 


Start the listener as follows: 


$ lsnrctl start LISTENER_ LAB2

 Database Upgrade: Manually Upgrading a Release 10.2 Database

 If you are upgrading database manually,

then you should analyze it by running the Pre-Upgrade Information Tool.

 Run the Pre-Upgrade Information Tool

Start the database in the UPGRADE mode:

SQL> STARTUP UPGRADE

 Set the system to spool results to a log file for later analysis:

SQL> SPOOL upgrade_info.log

 Run the Pre-Upgrade Information Tool:

 SQL> @$ORACLE_HOME/rdbms/admin/utlu102i.sql

 Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

 Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file

 Upgrading a Release 10.2 Database After you install the patch set,
you must perform the following steps on every database associated with the upgraded Oracle home:

 1. Log in as the Oracle software owner user


 2. Start the listener as follows: $ lsnrctl start LISTENER_LAB2

3. Enter the following SQL*Plus commands:

 SQL> STARTUP UPGRADE

 SQL> SPOOL patch.log

 SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

 SQL> SPOOL OFF

 4. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
 If necessary, rerun the catupgrd.sql script after correcting any problems.

 5. Restart the database:

 SQL> SHUTDOWN IMMEDIATE

 SQL> STARTUP

 POST UPGRADE CHECKS:

 Run the utlrp.sql script to recompile all invalid PL/SQL packages. 

SQL> @?/rdbms/admin/utlrp.sql

 When the 10.2.0.5 patch set is applied to an Oracle10g Standard Edition database,

there may be 54 invalid objects after the utlrp.sql script runs.

 These objects belong to the unsupported components and do not affect the database operation.

 Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following: BIN$4lzljWIt9gfgMFeM2hVSoA==$0

 Run the following command to check the status of all the components after the upgrade:

 SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

 In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.

 To configure and secure Enterprise Manager follow these steps:

 Ensure the database and Listener are operational.
 Execute
emca -upgrade db

 Note:If you are upgrading a database where Oracle Database Control is configured in non-secure mode, then Oracle Database Control will run in secure mode after upgrade.

 Removing the Patch Set Software The catdwgrd.sql script enables the user to restore the Oracle Database installation of the database back to the original 10.2 release that the user backed up before applying the patch set.
The catdwgrd.sql script is run in the 10.2.0.5 Oracle home to perform necessary downgrade actions.

 After the 10.2.0.4 release is restored, the catrelod.sql script is run from 10.2.0.4 Oracle home to reload the 10.2.0.4 release packages and views.

 Removing the Patch Set Software for Single Instance Installation Perform the following steps for removing the patch set from the 10.2.0.5 patch release:

 Use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
 2. SQL> SHUTDOWN IMMEDIATE
 Take a backup of the following files in 10.2.0.5 Oracle home directory:
 $ ORACLE_HOME/rdbms/admin/catrelod.sql
 $ ORACLE_HOME/network/admin/tnsnames.ora
 Enter the following SQL*Plus commands:
 SQL> CONNECT SYS AS SYSDBA
 SQL> STARTUP DOWNGRADE
 SQL> SPOOL downgrade.out
 SQL> @?/rdbms/admin/catdwgrd.sql
 SQL> SPOOL OFF
 SQL> SHUTDOWN IMMEDIATE
 SQL> EXIT

 Review the downgrade.out file for errors.
 Restore the Oracle10g installation and Central Inventory (see /etc/oraInst.loc) that you backed up before applying the patch set.
After restoring the original Oracle home, copy the saved version of catrelod.sql script into the restored Oracle home rdbms/admin directory.
 Also copy the saved version 10.2.0.5/network/admin/tnsnames.ora
directory into the restored Oracle home /network/admin/tnsnames.ora directory,
 and then perform the following steps:

SQL> CONNECT SYS AS SYSDBA

 SQL> STARTUP DOWNGRADE

 SQL> SPOOL catrelod.out

 SQL> @?/rdbms/admin/catrelod.sql

 SQL> SPOOL OFF

 SQL> SHUTDOWN IMMEDIATE

 Review the catrelod.out file for errors.

Note: Ignore the ORA-29844 and ORA-31085 errors found in the catrelod.out file

. These errors are tracked with Oracle bugs 4758112 and 4425495 respectively.

 After reloading the 10.2.0.2 release, the version numbers for Oracle Data Mining and Oracle OLAP Analytic Workspace remain at 10.2.0.5.

These issues are tracked with Oracle bugs 4758695 and 4751917.

 Start the database and recompile the remaining invalid objects:

SQL> STARTUP

 SQL> @?/rdbms/admin/utlrp.sql

 Cross-check the status of installed components with the following script:

 SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; *********************END OF DOCUMENT************************

Oracle DBA Interview Questions

1. Basic (Every DBA should answer correctly ALL these questions. This knowledge is just basic for a 3+ year experienced DBA) 1.1 Q- Which are the default passwords of SYSTEM/SYS? A- MANAGER / CHANGE_ON_INSTALL 1.2 Q- How can you execute a script file in SQLPLUS? A- To execute a script file in SQLPlus, type @ and then the file name. 1.3 Q- Where can you find alertlog? A- bdump directory (show parameter background) 1.4 Q- What is the address of the Official Oracle Support? A- metalink.oracle.com or support.oracle.com 1.5 Q- What file will you use to establish Oracle connections from a remote client? A- tnsnames.ora 1.6 Q- How can you check if the database is accepting connections? A- lsnrctl status or lsnrctl services 1.7 Q- Which log would you check if a database has a problem? A- Alert log 1.8 Q- Name three clients to connect with Oracle, for example, SQL Developer: A- SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients. 1.9 Q- How can you check the structure of a table from sqlplus? A- DESCRIBE or DESC 1.10 Q- What command will you start to run the installation of Oracle software on Linux? A- runInstaller 2. Moderate (Standard knoledge for a daily-work of every DBA. He could fail one or two questions, but not more) 2.1 Q- What should you do if you encounter an ORA-600? A- Check alertlog file for understanding the cause and investigate. Paralley raise call with Oracle Support 2.2 Q- Explain the differences between PFILE and SPFILE A- A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database. A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions) 2.3 Q- In which Oracle version was Data Pump introduced? A- Oracle 10g 2.4 Q- Say two examples of DML, two of DCL and two of DDL A- DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME DCL: GRANT, REVOKE 2.5 Q- You want to save the output of an Oracle script from sqlplus. How would you do it? A- spool script_name.txt select * from your_oracle_operations; spool off; 2.6 Q- What is the most important requirement in order to use RMAN to make consistent hot backups? A- Your database has to be in ARCHIVELOG mode. 2.7 Q- Can you connect to a local database without a listener? A- Yes, you can. 2.8 Q- In which view can you find information about every view and table of oracle dictionary? A- DICT or DICTIONARY 2.9 Q- How can you view all the users account in the database? A- SELECT USERNAME FROM DBA_USERS; 2.10 Q- In linux, how can we change which databases are started during a reboot? A- Edit /etc/oratab 3. Advanced (A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work he has done, he could still fail up to 4 questions) 3.1 Q- When a user process fails, what Oracle background process will clean after it? A- PMON 3.2 Q- How can you reduce the space of TEMP datafile? A- Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace. 3.3 Q- How can you view all the current users connected in your database in this moment? A- SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME; 3.4 Q- Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT A- SHUTOWN NORMAL = SHUTDOWN : It waits for all sessions to end, without allowing new connections. SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session. SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the fastest method, but can lead to database corruption. 3.5 Q- Is it possible to backup your database without the use of an RMAN database to store the catalog? A- Yes, but the catalog would be stored in the controlfile. 3.6 Q- Which are the main components of Oracle Grid Control? A- OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent). 3.7 Q- What command will you use to navigate through ASM files? A- asmcmd 3.8 Q- What is the difference between a view and a materialized view? A- A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes. 3.9 Q- Which one is faster: DELETE or TRUNCATE? A- TRUNCATE 3.10 Q- Are passwords in oracle case sensitive? A- Only since Oracle 11g. 4. RAC (Only intended for RAC-specific DBAs, with varied difficultied questions) 4.1 Q- What is the recommended method to make backups of a RAC environment? A- RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file. 4.2 Q- What command would you use to check the availability of the RAC system? A- crs_stat -t -v (-t -v are optional) 4.3 Q- What is the minimum number of instances you need to have in order to create a RAC? A- 1. You can create a RAC with just one server. 4.4 Q- Name two specific RAC background processes A- RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG. 4.5 Q- Can you have many database versions in the same RAC? A- Yes, but Clusterware version must be greater than the greater database version. 4.6 Q- What was RAC previous name before it was called RAC? A- OPS: Oracle Parallel Server 4.7 Q- What RAC component is used for communication between instances? A- Private Interconnect. 4.8 Q- What is the difference between normal views and RAC views? A- RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION 4.9 Q- Which command will we use to manage (stop, start…) RAC services in command-line mode? A- srvctl 4.10 Q- How many alert logs exist in a RAC environment? A- One for each instance. 5. Master (A 3+ year experienced DBA would probably fail these questions, they are very specifid and specially difficult. Be glad if he’s able to answer some of them) 5.1 Q- How can you difference a usual parameter and an undocumented parameter? A- Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption 5.2 Q- What is BBED? A- An undocumented Oracle tool used for foresnic purposes. Stans for Block Browser and EDitor. 5.3 Q- The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL) A- False in both cases. 5.4 Q- Explain Oracle memory structure The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA). The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database. 5.5 Q- Will RMAN take backups of read-only tablespaces? A- No 5.6 Q- Will a user be able to modify a table with SELECT only privilege? A- He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table. 5.7 Q- What Oracle tool will you use to transform datafiles into text files? A- Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this. 5.8 Q- SQL> SELECT * FROM MY_SCHEMA.MY_TABLE; SP2-0678: Column or attribute type can not be displayed by SQL*Plus Why I’m getting this error? A- The table has a BLOB column. 5.9 Q- What parameter will you use to force the starting of your database with a corrupted resetlog? A- _ALLOW_RESETLOGS_CORRUPTION 5.10 Q- Name the seven types of Oracle tables A- Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables