General
Storage Enhancements in the Oracle 10g database
"The
only virtue I want to claim is truth and non-violence. I lay no claim
to superhuman powers. I want none. I wear the same corruptible flesh that
the weakest of my fellow beings wears, and am therefore as liable to err
as any. My services have many limitations, but God has up to now blessed
them in spite of the imperfections."
Gandhi
|
General
Storage Enhancements in the Oracle 10g database
SYSAUX
tablespace in the Oracle 10g database
The Oracle 10g
database added one more tablespace (SYSAUX) to help the SYSTEM tablespace. The
SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. It is a
mandatory tablespace and should be created at database creation time. You can
not drop or rename this tablespace. The SYSAUX tablespace holds the following
database information:
-
Text, Ultra Search
-
Intermedia, Spatial
-
Scheduler
-
OLAP
-
XML DB
-
Workspace Manager
-
Data Mining
-
Server
Manageability Components
-
Recovery Catalog
-
EM Repository
-
Analytical
Workspace Object table
-
LogMinor, Log
Standby, Streems
-
Statspack
To monitor the
space usage of each about occupant inside the SYSAUX tablespace, use the
following SQL statement.
SQL>
SELECT occupant_name, space_usage_kbytes
FROM v$sysaux_occupants
/
The Mandatory
attributes of SYSAUX are:
PERMANENT
READ WRITE
EXTENT MANAGEMENT
LOCAL
SEGMENT SPACE
MANAGEMENT AUTO
What are its
benefits?
Reduces the number
of tablespaces to manage. You don"t need to create the TOOLS, OEM_REPOSITROY,
DRSYS, CWMLITE, ODM, etc tablespaces.
You reduce the load
on the SYSTEM tablespace.
Renaming
a tablespace in the Oracle 10g database
Now you can rename
tablespace in the Oracle 10g database. You can not rename the SYSTEM and SYSAUX
tablespaces. You can rename any permanent or temporary tablespaces. All
tablespaces must have their datafiles online. Your database spfile will be
updated.
To rename a
tablespace do the following SQL statement.
SQL>
ALTER TABLESPACE tools RENAME TO my_tools;
Default
Permanent Tablespace in the Oracle 10g database
Now, you can change
the default permanent tablespace for non-system users.
Check what your
default permanent tablespace is.
SQL>
SELECT property_value
FROM database_properties
WHERE property_name =
"DEFAULT_PERMANENT_TABLESPACE"
/
If it is SYSTEM,
then change it to USERS.
SQL>
ALTER DATABASE DEFAULT TABLESPACE users;
Now, check to see
the default was changed.
SQL>
SELECT property_value
FROM database_properties
WHERE property_name =
"DEFAULT_PERMANENT_TABLESPACE"
/
Copy
files without using system operating system
You can use the
COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file from one
place to another. You should make sure to create source and destination
directories and you have a read access on the source directory and a write
access on the destination directory.
Assuming we are
going to copy file a1.dbf from location /u01/oradata/school to location /u02/oradata/school.
SQL>
CREATE DIRECTORY my_source AS "/u01/oradata/school";
SQL>
CREATE DIRECTORY my_destination AS "/u02/oradata/school";
SQL>
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE (
SOURCE_DIRECTORY_OBJECT => "MY_SOURCE",
SOURCE_FILE_NAME => "a1.dbf",
DESTINATION_DIRECTORY_OBJECT => "MY_DESTINATION",
DESTINATION _FILE_NAME => "a1.dbf");
END;
/
Redo
Logfile size advisor in the Oracle 10g database
It determines the
optional smallest online redo log file size based on the current
FAST_START_MTTR_TARGET setting and the corresponding statistics. To enable the
Redo Logfile Size Advisor, you should set the FAST_START_MTTR_TARGET parameter.
Note that an online redo log file size is considered optimal if it does not
drive incremental check pointing more aggressively than needed by
FAST_START_MTTR_TARGET.
Hands-On
#1:
Connect as sysdba
SQL>
CONNECT / AS SYSDBA
Control the
interval and retention of snapshot. Make the retention period for one day with
an interval off "0" which switches off snapshot collection.
SQ> EXEC
dbms_workload_repository.modify_snapshot_settings (1440, 0);
-- 1440 minute or 1 day and "0" switches OFF snapshot collection.
Create a table with
the following storage parameter.
SQL>
CREATE TABLE logfile_advisor
(c1 NUMBER, c2 CHAR(2000))
PCTFREE 0
STORAGE (INITIAL 4m NEXT 2m PCTINCREASE 0);
Execute the
following PL/SQL block to populate the table.
SQL>
BEGIN
FOR this IN 1..10000 LOOP
INSERT INTO logfile_advisor VALUES (this, null);
END LOOP;
COMMIT;
END;
/
Go to the Database
Control home page and click on the Administration tab. On the Administration
tab, click on the Redo Log Groups link. Now, you should see the size of each
group. On the same page, select Sizing Advice in the Actions drop-down list and
click Go. You should see the recommended optimal redo log file size if database
recommended.
Now change the
STATISTICS_LEVEL parameter set to TYPICAL.
Do the following.
SQL>
BEGIN
UPDATE logfile_advisor SET
c2 = "this is a long long long long data"""."
WHERE mod (c1, 1) = 0;
COMMIT;
END;
/
Now, go to the
Database Control home page and do the same you did. Repeat the process. On the
Administration tab, click on the Redo Log Groups link. Now, you should see the
size of each group. On the same page, select Sizing Advice in the Actions
drop-down list and click Go. You should see the recommended optimal redo log
file size if database recommended.
|