/*
|| Oracle 11gR2: Using ASM Features Listing
||
|| Contains code samples demonstrating how ASM and ACFS can be used in concert with
|| Oracle 11gR2 databases as part of the Oracle 11gR2 New Features article series.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various Oracle 11gR2 ASM and ACFS
|| features, and they should be carefully proofread before being executed
|| against any existing Oracle database to avoid potential damage!
*/

/*
|| Listing 3.1: 
|| Validating if a database upgrade is possible with the utli112i.sql utility
*/

SQL> @utlu112i Oracle Database 11.2 Pre-Upgrade Information Tool 03-21-2010 18:05:57 ********************************************************************** Database: ********************************************************************** --> name: ORCL --> version: 11.1.0.6.0 --> compatible: 11.1.0.0.0 --> blocksize: 8192 --> platform: Linux x86 64-bit --> timezone file: V11 . ********************************************************************** Tablespaces: [make adjustments in the current environment] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 1070 MB .... AUTOEXTEND additional space required: 350 MB --> SYSAUX tablespace is adequate for the upgrade. .... minimum required size: 1027 MB .... AUTOEXTEND additional space required: 289 MB --> UNDOTBS1 tablespace is adequate for the upgrade. .... minimum required size: 59 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 61 MB --> EXAMPLE tablespace is adequate for the upgrade. .... minimum required size: 78 MB . ********************************************************************** Flashback: OFF ********************************************************************** ********************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No update parameter changes are required. . ********************************************************************** Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No renamed parameters found. No changes are required. . ********************************************************************** Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** -- No obsolete parameters found. No changes are required . ********************************************************************** Components: [The following database components will be upgraded or installed] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> OLAP Catalog [upgrade] VALID --> EM Repository [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle interMedia [upgrade] VALID --> Spatial [upgrade] VALID --> Oracle Ultra Search [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] VALID . ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> Database contains schemas with stale optimizer statistics. .... Refer to the Upgrade Guide for instructions to update .... schema statistics prior to upgrading the database. .... Component Schemas with stale statistics: .... SYS .... SYSMAN .... CTXSYS .... ORDSYS .... WKSYS WARNING: --> Database contains INVALID objects prior to upgrade. .... The list of invalid SYS/SYSTEM objects was written to .... registry$sys_inv_objs. .... Because there were more than 5000 invalid non-SYS/SYSTEM objects .... the list was not stored in registry$nonsys_inv_objs. .... Use utluiobj.sql after the upgrade to identify any new invalid .... objects due to the upgrade. .... USER WKSYS has 115 INVALID objects. .... USER MDSYS has 521 INVALID objects. .... USER WK_TEST has 1 INVALID objects. .... USER PUBLIC has 1700 INVALID objects. .... USER OLAPSYS has 288 INVALID objects. .... USER CTXSYS has 188 INVALID objects. .... USER HR has 5 INVALID objects. .... USER FLOWS_FILES has 6 INVALID objects. .... USER OUTLN has 1 INVALID objects. .... USER EXFSYS has 118 INVALID objects. .... USER SYSTEM has 5 INVALID objects. .... USER ORACLE_OCM has 6 INVALID objects. .... USER ORDSYS has 551 INVALID objects. .... USER SYSMAN has 1226 INVALID objects. .... USER DBSNMP has 20 INVALID objects. .... USER OE has 49 INVALID objects. .... USER PM has 3 INVALID objects. .... USER ORDPLUGINS has 10 INVALID objects. .... USER SH has 3 INVALID objects. .... USER XDB has 221 INVALID objects. .... USER IX has 13 INVALID objects. .... USER FLOWS_030000 has 709 INVALID objects. .... USER QRC has 1 INVALID objects. .... USER TRBTKT has 2 INVALID objects. .... USER SYS has 3231 INVALID objects. .... USER WMSYS has 88 INVALID objects. WARNING: --> EM Database Control Repository exists in the database. .... Direct downgrade of EM Database Control is not supported. Refer to the .... Upgrade Guide for instructions to save the EM data prior to upgrade. WARNING:--> recycle bin in use. .... Your recycle bin turned on. .... It is REQUIRED .... that the recycle bin is empty prior to upgrading .... your database. .... The command: PURGE DBA_RECYCLEBIN .... must be executed immediately prior to executing your upgrade. . PL/SQL procedure successfully completed.
/* || Listing 3.2: || Upgrading the database to Oracle Release 11gR2 */
SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> @?/rdbms/admin/catupgrd DOC>####################################################################### DOC>####################################################################### DOC> DOC> The first time this script is run, there should be no error messages DOC> generated; all normal upgrade error messages are suppressed. DOC> DOC> If this script is being re-run after correcting some problem, then DOC> expect the following error which is not automatically suppressed: DOC> DOC> ORA-00001: unique constraint (<constraint_name>) violated DOC> possibly in conjunction with DOC> ORA-06512: at "<procedure/function name>", line NN DOC> DOC> These errors will automatically be suppressed by the Database Upgrade DOC> Assistant (DBUA) when it re-runs an upgrade. DOC> DOC>####################################################################### DOC>####################################################################### DOC># DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the user running this script is not SYS. Disconnect DOC> and reconnect with AS SYSDBA. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected DOC>###################################################################### DOC>###################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database server version is not correct for this script. DOC> Shutdown ABORT and use a different script or a different server. DOC>###################################################################### DOC>###################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the database has not been opened for UPGRADE. DOC> DOC> Perform a "SHUTDOWN ABORT" and DOC> restart using UPGRADE. DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot DOC> be run with the Oracle Database Vault option set to TRUE since DOC> AS SYSDBA connections are restricted. DOC> DOC> Perform a "SHUTDOWN ABORT", relink the server without the Database DOC> Vault option (but with the OLS option), and restart the server using DOC> UPGRADE. DOC> DOC> Restriction is for: DOC> 1) When source database has no DV installed. DOC> OR DOC> 2) When source database has DV installed and DV version is prior to 11.2. DOC> DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if Database Vault is installed in the database but the Oracle DOC> Label Security option is FALSE. To successfully upgrade Oracle DOC> Database Vault, the Oracle Label Security option must be TRUE. DOC> DOC> Perform a "SHUTDOWN ABORT", relink the server with the OLS option DOC> (but without the Oracle Database Vault option) and restart the server DOC> using UPGRADE. DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause an "ORA-01722: invalid number" DOC> error if bootstrap migration is in progress and logminer clients DOC> require utlmmig.sql to be run next to support this redo stream. DOC> DOC> Run utlmmig.sql DOC> then (if needed) DOC> restart the database using UPGRADE and DOC> rerun the upgrade script. DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statement will cause: DOC> - "ORA-00942: table or view does not exist" ; or DOC> - "ORA-00904: "TZ_VERSION": invalid identifier"" ; or DOC> - "ORA-01722: invalid number" DOC> if the pre-upgrade utility (utlu112i.sql) has not been run to: DOC> a) create and update registry$database table to include the current DOC> database timezone file version used in the old release; or DOC> b) do inserts into sys.props$. DOC> DOC> o Action: DOC> Shutdown ABORT and revert to the original ORACLE_HOME. Then run DOC> utlu112i.sql to populate registry$database with the database timezone DOC> file version used by the lower version database and to populate DOC> sys.props$ with Day Light Saving Time (DST) properties information. DOC> DOC> OR DOC> - An "ORA-01722: invalid number" DOC> if the old release uses a timezone file version newer than 8 (shipped with DOC> 11.2) but the new release has not been patched yet. DOC> DOC> o Action: DOC> Shutdown ABORT and patch new ORACLE_HOME to the same timezone file DOC> version as used in the old ORACLE_HOME. DOC> DOC> DOC>####################################################################### DOC>####################################################################### DOC># no rows selected DOC>####################################################################### DOC>####################################################################### DOC> The following statements will cause an "ORA-01722: invalid number" DOC> error if the SYSAUX tablespace does not exist or is not DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and DOC> SEGMENT SPACE MANAGEMENT AUTO. DOC> DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from DOC> a number of tablespaces that were separate in prior releases. DOC> Consult the Oracle Database Upgrade Guide for sizing estimates. DOC> DOC> Create the SYSAUX tablespace, for example, DOC> DOC> create tablespace SYSAUX datafile 'sysaux01.dbf' DOC> size 70M reuse DOC> extent management local DOC> segment space management auto DOC> online; DOC> DOC> Then rerun the catupgrd.sql script. DOC>####################################################################### DOC>####################################################################### DOC># << several hundred thousand lines removed for sake of brevity >> SQL> /*****************************************************************************/ SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!! SQL> */ SQL> /*****************************************************************************/ SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> SQL> DOC DOC>####################################################################### DOC>####################################################################### DOC> DOC> The above sql script is the final step of the upgrade. Please DOC> review any errors in the spool log file. If there are any errors in DOC> the spool file, consult the Oracle Database Upgrade Guide for DOC> troubleshooting recommendations. DOC> DOC> Next restart for normal operation, and then run utlrp.sql to DOC> recompile any invalid application objects. DOC> DOC>####################################################################### DOC>####################################################################### DOC># SQL> SQL> Rem Set errorlogging off SQL> SET ERRORLOGGING OFF; SQL> SQL> Rem ********************************************************************* SQL> Rem END catupgrd.sql SQL> Rem ********************************************************************* SQL> spool off
/* || Listing 3.3: || Displaying ASM Disk Group attributes using ASMCMD’s lsdg command */
ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 6612 409 0 409 0 N ACFS/ MOUNTED NORMAL N 512 4096 1048576 8816 8584 60 4262 0 N DATA/ MOUNTED EXTERN N 512 4096 1048576 8816 8758 0 8758 0 N FRA/
/* || Listing 3.4: || Using ASMCMD’s ls command to view file location and storage details */
ASMCMD> ls -ls +DATA/ORCL/CONTROLFILE Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name CONTROLFILE HIGH FINE APR 15 17:00:00 Y 16384 621 10174464 50331648 current.257.716404861 ASMCMD> ls -ls +DATA/ORCL/DATAFILE Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 16385 134225920 273678336 BASICFILES.262.716405363 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 12801 104865792 214958080 EXAMPLE.264.716405395 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 32769 268443648 542113792 SECUREFILES.261.716405339 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 134161 1099046912 2203058176 SYSAUX.258.716405081 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 115201 943726592 1892679680 SYSTEM.259.716405187 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 60801 498081792 1001390080 UNDOTBS1.260.716405283 DATAFILE MIRROR COARSE APR 15 17:00:00 Y 8192 16313 133636096 271581184 USERS.263.716405381 ASMCMD> ls -ls +DATA/ORCL/TEMPFILE Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name TEMPFILE MIRROR COARSE APR 15 17:00:00 Y 8192 12801 104865792 214958080 TEMP.265.716405987 ASMCMD> ls -Ls +DATA/ORCL/ONLINELOG Block_Size Blocks Bytes Space Name 512 262145 134218240 273678336 group_4.266.716406451 512 262145 134218240 273678336 group_5.267.716406463 512 262145 134218240 273678336 group_6.268.716406467 ASMCMD> ls -Ls +FRA/ORCL/ONLINELOG Block_Size Blocks Bytes Space Name 512 262145 134218240 136314880 group_4.257.716408773 512 262145 134218240 136314880 group_5.258.716408781 512 262145 134218240 136314880 group_6.259.716408791
/* || Listing 3.5: || Using ASMCMD’s new iostat command to query ASM file system performance */
ASMCMD> iostat 5 Group_Name Dsk_Name Reads Writes ACFS ACFS_0000 1474560 467341312 ACFS ACFS_0001 376832 8192 ACFS ACFS_0002 1531904 24576 DATA DATA_0000 7200768 1099345920 DATA DATA_0001 3795456 1101507072 DATA DATA_0002 10399744 1096343552 DATA DATA_0003 10543104 629115392 FRA FRA_0000 1363968 9367552 FRA FRA_0001 303104 6004736 FRA FRA_0002 81920 4689920 FRA FRA_0003 86016 4612096 Group_Name Dsk_Name Reads Writes ACFS ACFS_0000 0.00 819.20 ACFS ACFS_0001 0.00 0.00 ACFS ACFS_0002 0.00 0.00 DATA DATA_0000 0.00 7344128.00 DATA DATA_0001 0.00 7134412.80 DATA DATA_0002 3276.80 7344128.00 DATA DATA_0003 0.00 7340032.00 FRA FRA_0000 0.00 4096.00 FRA FRA_0001 0.00 0.00 FRA FRA_0002 0.00 0.00 FRA FRA_0003 0.00 0.00 Group_Name Dsk_Name Reads Writes ACFS ACFS_0000 0.00 819.20 ACFS ACFS_0001 0.00 0.00 ACFS ACFS_0002 0.00 0.00 DATA DATA_0000 0.00 5666406.40 DATA DATA_0001 0.00 5666406.40 DATA DATA_0002 3276.80 5876121.60 DATA DATA_0003 0.00 5662310.40 FRA FRA_0000 0.00 4096.00 FRA FRA_0001 0.00 0.00 FRA FRA_0002 0.00 0.00 FRA FRA_0003 0.00 0.00 Group_Name Dsk_Name Reads Writes ACFS ACFS_0000 0.00 1638.40 ACFS ACFS_0001 0.00 0.00 ACFS ACFS_0002 0.00 0.00 DATA DATA_0000 0.00 7138508.80 DATA DATA_0001 0.00 7348224.00 DATA DATA_0002 6553.60 6928793.60 DATA DATA_0003 0.00 7130316.80 FRA FRA_0000 0.00 8192.00 FRA FRA_0001 0.00 0.00 FRA FRA_0002 0.00 0.00 FRA FRA_0003 0.00 0.00 Group_Name Dsk_Name Reads Writes ACFS ACFS_0000 0.00 819.20 ACFS ACFS_0001 0.00 0.00 ACFS ACFS_0002 0.00 0.00 DATA DATA_0000 0.00 5037260.80 DATA DATA_0001 0.00 5037260.80 DATA DATA_0002 3276.80 5037260.80 DATA DATA_0003 0.00 5033164.80 FRA FRA_0000 0.00 4096.00 FRA FRA_0001 0.00 0.00 FRA FRA_0002 0.00 0.00 FRA FRA_0003 0.00 0.00