|
‘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 cannot drop or rename this tablespace.
The SYSAUX tablespace
contents
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
· Log Minor,
Log Standby, Streems
· Statspack
Monitoring the SYSAUX space
usage
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
/
Attributes of SYSAUX
The Mandatory attributes of
SYSAUX are:
· PERMANENT
· READ WRITE
· EXTENT
MANAGEMENT LOCAL
· SEGMENT SPACE
MANAGEMENT AUTO
Benefit of SYSAUX
What are its benefits?
It reduces the number of
tablespaces to manage. You do not need to create the TOOLS,
OEM_REPOSITROY, DRSYS, CWMLITE, ODM, etc tablespaces.
You reduce the load on the
SYSTEM tablespace.
Renaming a tablespace
Now you can rename
tablespace in the Oracle 10g database. You cannot 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
Now, you can change the
default permanent tablespace in the Oracle 10g database 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’
/
The COPY_FILE procedure
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
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-Using Size
Advisor
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))
PCT 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.
Is there any
recommendation?
|
“Segregation is the
adultery of an illicit intercourse between injustice and
immorality.” Martin Luther King Jr. |
Questions:
Questions on
General
Storage Enhancements in the Oracle 10g
database
Q: What is System Auxiliary
Tablespace in the Oracle 10g Database?
Q: What does the SYSAUX
tablespace hold?
Q: How can you monitor the
space usage of each occupant inside the SYSAUX tablespace?
Q: What are the Mandatory
attributes of SYSAUX?
Q: What are its benefits?
Q: Can you rename SYSTEM or
SYSAUX tablespaces?
Q: How do you rename a
tablespace?
Q: Can you change the
default Permanent Tablespace in the Oracle 10g database?
Q: How can you check what
your default permanent tablespace is?
Q: Change your default
tablespace to USERS.
Q: How can you copy files
without using system operating system commands?
Q: What does the Redo
Logfile size advisor in the Oracle 10g database?
Q: Make the retention
period for one day with an interval off “0” which switches off
snapshot collection.
|