27 March 2014
18 March 2014
ORA-27054 While Backup/Restore with RMAN on NFS Mount Point
Common issue with RMAN while working with NFS mount points (Linux/Solaris).
Reason :
a) The mount table (eg; /etc/mnttab) can be read to check the mount options
b) The NFS mount is mounted with the "hard" option
c) The mount options include rsize>=32768 and wsize>=32768 (in /etc/fstab)
Example 192.168.3.1:/oracle/backup/rman /oracle/backup/rman nfs rsize=32768,wsize=32768,timeo=14,intr,bg,retry=16
d) For RAC environments, where NFS disks are supported, the "noac" mount option is used.
Workaround :
Reason :
Oracle
10G R2 onwards, Oracle checks the options with which a NFS mount is mounted on
the filesystem to ensure that no corruption of the database.
There are no single set of NFS mount options that work across all Oracle platforms
Check :
The exact checks used for an NFS mounted disk vary between platforms but in general the basic checks will include the following checks
There are no single set of NFS mount options that work across all Oracle platforms
Check :
The exact checks used for an NFS mounted disk vary between platforms but in general the basic checks will include the following checks
a) The mount table (eg; /etc/mnttab) can be read to check the mount options
b) The NFS mount is mounted with the "hard" option
c) The mount options include rsize>=32768 and wsize>=32768 (in /etc/fstab)
Example 192.168.3.1:/oracle/backup/rman /oracle/backup/rman nfs rsize=32768,wsize=32768,timeo=14,intr,bg,retry=16
d) For RAC environments, where NFS disks are supported, the "noac" mount option is used.
Workaround :
sql> alter system set
events '10298 trace name context forever, level 32';
or
sql> alter system set
events '10298 trace name context forever, level 32' scope=spfile;
or
Set the following event in the init.ora
event="10298 trace name context forever, level 32"
event="10298 trace name context forever, level 32"
7 March 2014
Various usages of FNDLOAD Command in R12
1 - Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE="lookup name"
3 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEXP_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
4 - Concurrent Programs
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"
Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt WARNINGS=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
5 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
6 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
7 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"
8 - Profile option at responsibility level
FNDLOAD apps/password@connect_identifier 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="GL_SET_OF_BKS_ID" APPLICATION_SHORT_NAME="SQLGL" LEV="RESPONSIBILITY" LEV_NAME="KR OIE SELF SERVICES EXPENSES"
9 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"
10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"
11 - Request Set links
FNDLOAD apps/devdr4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET_LINKS REQUEST_SET_NAME='FNDRSSUB1380'
12 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
13 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
14 - Forms Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
15 - User Creation
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct File_name.ldt FND_USER USER_NAME=’user name'
16 - Grants
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt GRANT GNT_MENU_NAME=<GRANTED_MENU>
17 - Alerts
FNDLOAD apps/ 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME="alert shprt name" ALERT_NAME="alert name"
18 - Download FND Messages
For the entire message names which starts with XX_MESSAGE,
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME=XX_MESSAGE%
For a particular message XXT_REGN_SUCCESS
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME= XXT_REGN_SUCCESS
19 - Functions
FNDLOAD apps/<APPS_PASSWORD> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt FUNCTION FUNCTION_NAME=<YOUR FUNCTION NAME>
20 - AK Attributes
java oracle.apps.ak.akload apps apps THIN "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host =
<DB_HOSTNAME>)(Port = <DB_PORT>)) (CONNECT_DATA = (SID = <DB_SID>)))" DOWNLOAD
file_name.ldt GET CUSTOM_REGION AMS AMS_ASSOCIATE_DELV
AMS - Application Short Name
21 - WFLOAD
WFLOAD apps/apps 0 Y DOWNLOAD APEXP.wft APEXP
22 - Data source & Template Definition
FNDLOAD apps/apps O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXNAME' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXNAME' TEMPLATE_CODE='XX_SOURCE_CODE'
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE="lookup name"
3 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEXP_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
4 - Concurrent Programs
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"
Upload
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt WARNINGS=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
5 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"
6 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
7 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"
8 - Profile option at responsibility level
FNDLOAD apps/password@connect_identifier 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="GL_SET_OF_BKS_ID" APPLICATION_SHORT_NAME="SQLGL" LEV="RESPONSIBILITY" LEV_NAME="KR OIE SELF SERVICES EXPENSES"
9 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"
10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"
11 - Request Set links
FNDLOAD apps/devdr4u 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET_LINKS REQUEST_SET_NAME='FNDRSSUB1380'
12 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
13 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
14 - Forms Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME
15 - User Creation
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct File_name.ldt FND_USER USER_NAME=’user name'
16 - Grants
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt GRANT GNT_MENU_NAME=<GRANTED_MENU>
17 - Alerts
FNDLOAD apps/ 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME="alert shprt name" ALERT_NAME="alert name"
18 - Download FND Messages
For the entire message names which starts with XX_MESSAGE,
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME=XX_MESSAGE%
For a particular message XXT_REGN_SUCCESS
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME= XXT_REGN_SUCCESS
19 - Functions
FNDLOAD apps/<APPS_PASSWORD> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt FUNCTION FUNCTION_NAME=<YOUR FUNCTION NAME>
20 - AK Attributes
java oracle.apps.ak.akload apps apps THIN "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host =
<DB_HOSTNAME>)(Port = <DB_PORT>)) (CONNECT_DATA = (SID = <DB_SID>)))" DOWNLOAD
file_name.ldt GET CUSTOM_REGION AMS AMS_ASSOCIATE_DELV
AMS - Application Short Name
21 - WFLOAD
WFLOAD apps/apps 0 Y DOWNLOAD APEXP.wft APEXP
22 - Data source & Template Definition
FNDLOAD apps/apps O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXNAME' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXNAME' TEMPLATE_CODE='XX_SOURCE_CODE'
4 March 2014
How to move AUD$ table from system tablespace to another in 11gR2
In
this article, I will explain how to manage audit trails in Oracle Database 11gR2
One
of the most significant aspects of database security involves setting up
auditing to record user activities. The very knowledge that a user’s actions
are being recorded can act as a significant deterrent to prevent wrongdoers
from committing malicious acts.
When
auditing is enabled, the audit output is recorded in an audit trail, which is
usually stored in the database in a table under the SYS schema called AUD$. It
can also reside as files in the file system, and the files can optionally be
stored in XML format. For more-precise control, the Fine Grained Auditing
feature of Oracle Database 11g provides granular control of what to
audit, based on a more detailed set of policies. Fine Grained Auditing audits
are usually stored in another table, FGA_LOG$, under the SYS schema.
These
various audit trails can quickly grow out of control when database activity
increases. As audit trails grow, two main challenges must be addressed:
- Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
- Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt.
New
auditing features in Oracle Database 11g Release 2 can help address
these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT,
enable you to move audit trails from the SYSTEM tablespace to one of your
choice.
Relocating the Audit Trail Tables
Let’s first examine
how to relocate an audit trail from the default SYSTEM tablespace to a new one.
In case you don’t already have a suitable target tablespace, the code below
shows how to create one:
create tablespace
audit_ts datafile '+DATA' size 1000M segment space management auto;
For
moving an audit trail to the new tablespace, Oracle 11g R2 provides a
procedure in DBMS_AUDIT_MGMT called SET_AUDIT_TRAIL_LOCATION. Listing 1 shows
how to move a “standard” audit trail, which is the Oracle Database audit
recorded in the AUD$ table.
Point
1: Relocating a standard audit
trail
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_location_value => 'AUDIT_TS');
end;
/
This
move operation can be performed even when the database is up and an audit trail
is being written. The target tablespace (AUDIT_TS in this case) must be
available and online. If the tablespace is not available, auditing will stop,
also stopping the database in the process. You should therefore be very careful
about where you create the tablespace. The location should be permanent (and
not on a temporary file system such as /tmp), and the underlying hardware
should be resilient against failures (using RAID-1, for example).
The
procedure can also be used for Fine Grained Auditing audit trails. To move a
Fine Grained Auditing audit trail, simply replace the value of the
audit_trail_type parameter in Listing 1 with
dbms_audit_mgmt.audit_trail_fga_std. If you want to move both the standard and
Fine Grained Auditing audit trails to the new tablespace, use the
dbms_audit.audit_trail_db_std value as the audit_trail_type parameter.
Purging Old Data
Next, let’s examine
how to purge audit trails. The audit management package includes a procedure
that automatically performs the purge for you. But before you can actually use
it, you must call a one-time initialization procedure—INIT_CLEANUP—to set up
the audit management infrastructure. Listing 2 shows how to perform the
initialization.
Point
2: Initializing cleanup of audit
entries
begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_db_std,
default_cleanup_interval => 24 );
end;
The
INIT_CLEANUP procedure takes two parameters, neither of which takes a default
value:
- audit_trail_type—designates the type of audit trail being initialized. For instance, audit_trail_aud_std indicates the standard database audit trail (the AUD$ table). Table 1 lists the possible values for this parameter and the audit trail types they represent.
- default_cleanup_interval—designates the default interval in hours between executions of automatic purge jobs (to be discussed later in this article).
Parameter
Description
audit_trail_aud_std The standard AUD$ audit trail in the database
audit_trail_fga_std The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std Both standard and FGA audit trails
audit_trail_os The OS audit trail
audit_trail_xml The XML audit trail
audit_trail_files Both OS and XML audit trails
audit_trail_all All of the above
audit_trail_aud_std The standard AUD$ audit trail in the database
audit_trail_fga_std The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std Both standard and FGA audit trails
audit_trail_os The OS audit trail
audit_trail_xml The XML audit trail
audit_trail_files Both OS and XML audit trails
audit_trail_all All of the above
In
addition to setting the default cleanup frequency, the INIT_CLEANUP procedure
moves the audit trail out of the SYSTEM tablespace. If the FGA_LOG$ and AUD$
tables are in the SYSTEM tablespace, the procedure will move them to the SYSAUX
tablespace. Needless to say, you should ensure that the SYSAUX tablespace has
sufficient space to hold both of these tables. The process of moving data from
one tablespace to the other can have an impact on performance, so you should
avoid calling the procedure during peak hours.
If
you have already relocated these two tables to another tablespace (as described
in the previous section), they will stay in the new location and the procedure
will execute much more quickly.
After
calling the initialization procedure, you can perform the actual audit trail
cleanup, but you likely wouldn’t just remove an audit trail blindly. In most
cases, you would archive the trail first before performing a permanent purge.
When doing so, you can call another procedure—SET_LAST_ARCHIVE_TIMESTAMP—to let
the purge process know the time stamp up to which an audit trail has been
archived. This procedure accepts three parameters:
- audit_trail_type—the type of audit trail you are about to purge.
- last_archive_time—the last time the audit trail was archived for this type.
- rac_instance_number—with an Oracle Real Application Clusters (Oracle RAC) database, OS audit trail files exist on more than one server. It’s possible to archive these files at different times, so this parameter tells the purge process the archive time of each node (or instance number) of the cluster. This parameter is applicable to Oracle RAC databases only; it has no significance for single-instance databases. Furthermore, this parameter is irrelevant for database audit trails, because they are common to all Oracle RAC instances.
After
you set the archive time stamp, you can check its value from a data dictionary
view, DBA_AUDIT_MGMT_LAST_ARCH_TS. Below code shows how to set the cutoff time
stamp to Jan 01, 2014 at 10 a.m. and subsequently check its value from the
view.
Point
3: Setting the last archived time
begin
dbms_audit_mgmt.set_last_archive_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time =>
to_timestamp('2014-01-01
10:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null
);
end;
/
SQL> select * from
DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL RAC_INSTANCE
——————————————————————————————————
LAST_ARCHIVE_TS
——————————————————————————————————
STANDARD AUDIT TRAIL 0
01-JAN-14 10.00.00.000000
AM +00:00
Now
you can execute the purge. To do so, run the code shown in Point 4. The
CLEAN_AUDIT_TRAIL procedure in the listing accepts two parameters. The first
one is audit_trail_type. The second parameter—use_last_arch_timestamp—specifies
whether the purge should be performed, depending on the last archive time
stamp. If the parameter is set to TRUE (the default), the purge will delete the
records generated before the time stamp (Jan 01, 2014 at 10 a.m. in this case).
If it is set to FALSE, all audit trail records will be deleted.
Point
4:
Purging a standard database audit trail
begin
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
use_last_arch_timestamp => TRUE
);
end;
/
This
same procedure is also used to purge file-based audit trails such as OS file
audit trails and XML trails. To purge those trails, just specify the
appropriate value for the audit_trail_type parameter (as shown in Table 1).
However, note that for file-based audit trails, only the files in the current
audit directory (as specified by the audit_file_dest initialization parameter)
will be deleted. If you have audit trail files in a different directory from
the one specified in audit_file_dest, those files will not be deleted.
Note
that in Microsoft Windows, audit trails are entries in Windows Event Viewer and
not actual OS files. So purging OS-based audit trails on that platform will not
delete the trails.
Setting Up Automatic Purge
The foregoing process
is good for a one-time purge of audit trails. To ensure that audit trails do
not overwhelm their tablespace, you may want to institute an automatic purge
mechanism. The DBMS_AUDIT_MGMT package has another procedure—CREATE_PURGE_JOB—to
do just that. This procedure takes four parameters:
- audit_trail_type—the type of the audit trail
- audit_trail_purge_interval—the duration, in hours, between executions of the purge process
- audit_trail_purge_name—the name you assign to this job
- use_last_arch_timestamp—an indication of whether the job should delete audit trail records marked as archived. The default is TRUE. If the parameter is set to FALSE, the procedure will delete the entire trail.
Point
5 shows how to create a purge job that deletes standard audit trail records
every 24 hours. As with one-time purges, you can create different jobs for each
type of trail—such as standard, Fine Grained Auditing, OS files, and XML—simply
by specifying different values for audit_trail_type when calling
CREATE_PURGE_JOB. You can even set different purge intervals for each audit
trail type to suit your archival needs. For instance, you can use a simple
database-link-based script to pull database audit trail records to a different
database while using a third-party tool to pull the OS audit trails. The
execution time of each approach may be different, causing the database records
to be pulled every day while the OS files are being pulled every hour. As a
result, you might schedule purge jobs with an interval of 24 hours for
database-based trails and with an interval of one hour for OS-file-based
trails.
Point
5:
Creating a purge job for a standard audit trail
begin
dbms_audit_mgmt.create_purge_job (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_purge_interval => 24,
audit_trail_purge_name => 'std_audit_trail_purge_job',
use_last_arch_timestamp => TRUE
);
end;
/
You
can view information about automatic purge jobs by accessing the DBA_AUDIT_MGMT_CLEANUP_JOBS
data dictionary view. It shows all the important attributes of the job, such as
the name, the type of audit trail being cleaned, and the frequency.
Setting Audit Trail Properties
When setting up a
purge job, you should always remember one very important fact. It performs a
DELETE operation—not TRUNCATE—on database-based trails, so the purge operation
generates redo and undo records, which may be quite significant, depending on
the number of trail records deleted. A large deletion can potentially fill up
the undo tablespace. To reduce the redo size of a transaction, the purge job
deletes in batches of 1,000 and performs commits between them. If the database
is very large, it may be able to handle much more redo easily. You can change
the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure. Listing
6 shows how to set the delete batch size to 100,000.
Point
6:
Setting the deletion batch size
begin
dbms_audit_mgmt.set_audit_trail_property(
audit_trail_type =>
dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_property =>
dbms_audit_mgmt.db_delete_batch_size,
audit_trail_property_value => 100000);
end;
/
In
addition to the db_delete_batch_size property referenced in Listing 6, you can
use SET_AUDIT_TRAIL_PROPERTY to set several other important properties. They
include the following:
- file_delete_batch_size specifies how many OS audit trail files will be deleted by the purge job in one batch.
- cleanup_interval specifies the default interval, in hours, between executions of a purge job.
- os_file_max_age specifies how many days an OS file or an XML file can be left open before a new file is created.
- os_file_max_size specifies the maximum size of an audit trail file (in kilobytes).
To
find the current value of a property, you can check the data dictionary view
DBA_AUDIT_MGMT_CONFIG_PARAMS.
Conclusion
Audit
trails establish accountability. In Oracle Database 11g, there are
several types of audit trails—standard, fine-grained, OS-file-based, and XML.
In this article, you learned how to relocate a database-based audit trail from
its default tablespace—SYSTEM—to another one designated only for audit trails.
You also learned how to purge audit trails of various types to keep them within
a manageable limit, and you finished by establishing an automatic purge
process.
How to Reclaim Space from DBA_FREE_SPACE and assign back to OS
Dear Friends,
Today I will like to explain how to reclaim space from DBA_FREE_SPACE and allocate the same to Operating system.
1. Query from dba_free_space to verify the available free space.
select tablespace_name "Tablespace Name" ,sum(bytes/1024/1024/1024) "Free Space in GB" from dba_free_space group by tablespace_name order by 2 desc;
2. Check the objects available in present tablespace.
3. Create a New Tablespace (XYZ_NEW) and add sufficient datafiles.
CREATE TABLESPACE <Tablaspace_name> DATAFILE '<Datafile Name>' SIZE 4000M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Note :
a. Object migration from one tablespace to another tablespace will use the Temporary Tablespace. Usages of Temp tablespace is approx the same size of Objects (Table/Index).
b. Object migration will create excessive archive log files. Which may be another issue to manage space. To avoid this we can use nologging option in command.
c. We can use parallel command with alter index/table.
Today I will like to explain how to reclaim space from DBA_FREE_SPACE and allocate the same to Operating system.
1. Query from dba_free_space to verify the available free space.
select tablespace_name "Tablespace Name" ,sum(bytes/1024/1024/1024) "Free Space in GB" from dba_free_space group by tablespace_name order by 2 desc;
2. Check the objects available in present tablespace.
3. Create a New Tablespace (XYZ_NEW) and add sufficient datafiles.
CREATE TABLESPACE <Tablaspace_name> DATAFILE '<Datafile Name>' SIZE 4000M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Note :
a. Object migration from one tablespace to another tablespace will use the Temporary Tablespace. Usages of Temp tablespace is approx the same size of Objects (Table/Index).
b. Object migration will create excessive archive log files. Which may be another issue to manage space. To avoid this we can use nologging option in command.
c. We can use parallel command with alter index/table.
4. Migrate objects in new tablespace (Sample query depend on the objects) | ||
select 'ALTER INDEX ' || owner ||'.' || segment_name || ' REBUILD TABLESPACE XYZ_NEW parallel 4 nologging; ' from dba_segments where tablespace_name='XYZ' and segment_type='INDEX'; select 'ALTER INDEX ' || owner ||'.' || segment_name || ' REBUILD PARTITION ' || partition_name || ' TABLESPACE XYZ_NEW; ' from dba_segments where tablespace_name='XYZ' and segment_type='INDEX PARTITION'; select 'alter table ' || owner|| '.' ||segment_name || ' move tablespace XYZ_NEW ;' from dba_segments where tablespace_name='XYZ' and segment_type='TABLE'; select 'alter table ' || owner|| '.' ||segment_name || ' move partition ' || partition_name ||' tablespace XYZ_NEW ;' from dba_segments where tablespace_name='XYZ' and segment_type='TABLE PARTITION'; |
||
5. Drop old tablespace | ||
DROP TABLESPACE XYZ INCLUDING CONTENTS AND DATAFILES; | ||
6. Rename tablespaces | ||
ALTER TABLESPACE XYZ_NEW RENAME TO XYZ; | ||
7. Compile invald objects sqlplus > @$ORACLE_HOME/rdbms/admin/utlrp.sql | ||
8. Check if any invalid index and rebuild index if any unusable. |
||
select distinct status from all_indexes; | ||
alter
index <index_name> rebuild online; Activity is completed and again we can verify the free space from select tablespace_name "Tablespace Name" ,sum(bytes/1024/1024/1024) "Free Space in GB" from dba_free_space group by tablespace_name order by 2 desc; |
Subscribe to:
Posts (Atom)