Database and Systems Administrator: December 2016

ORA-27492: unable to run job scheduler

My Oracle application have stopped transferring data to another table.
After doing a bit of troubleshooting,I have narrowed the issue down to the scheduled job PERSON.FIG_TIME_DAILY
The procedure that is called by this job works fine when I run in manually
However, this scheduled job hasn't successfully run in over a day
I tried dropping the job entirely and rebuilding it using the DDL from the same job in my testing environment, but that didn't seem to work.

Unable to run job "PERSON.FIG_TIME_DAILY" scheduler unavailable

From the online research I've done, most users have resolved this problem with some variation of the command

SQL>show parameter job_queue_processes;
the value was default set to 1000

Than i Google out and run the following

SQL>select attribute_name, value
from dba_scheduler_global_attribute
where attribute_name = 'SCHEDULER_DISABLED';

ATTRIBUTE_NAME VALUE
------------------ -----
SCHEDULER_DISABLED TRUE

-- Attribute vanishes when set to false (run as sysdba)

SQL>exec dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'FALSE' );

After this Scheduler started Working

Introduction and Uses

Oracle Dataguard is a disaster recovery solution from Oracle that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.

Uses of Oracle Dataguard

a) Oracle Dataguard ensures high availability, data protection and disaster recovery for enterprise data

b) Dataguard provides a comprehensive set of services that create, maintain, manage and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions

c) With Dataguard, administrators can optionally improve production database performance by offloading resource – intensive backup and reporting operations to standby systems.

Why are OPS$ accounts a security risk in a client/server environment

If you allow people to log in with OPS$ accounts from Windows Workstation, you cannot be sure who they really are. With terminals, you can rely on operating system passwords with Windows you cannot.

If you set REMOTE_OS_AUTHENT = TRUE in your init.ora file, Oracle assumes that remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password

IDENTIFIED EXTERNALLY will only be in effect from the local host.

Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY

What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk.

This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo log to the database to roll it forward.

One can roll – forward until a specific point – in – time (before the disaster occurred) or roll – forward until the last transaction recorded in the log files.

SQL> connect SYS as SYSDBA

SQL> RECOVER DATABASE UNTIL TIME '2016 – 03 – 06:16:00:00' USING BACKUP CONTROLFILE;

What strategies are available for backing – up an Oracle database?

The following methods are valid for backing – up an Oracle database

Export / Import – Exports are "logical" database backups in that they extract logical definitions and data from the database to a file.

Cold or Offline Backups – Shutdown the database and backup up all data, log and control files

Hot or Online Backups – If the databases are available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files

Also remember to back up the control files and archived redo log files.

RMAN Backups – While the database is Offline or Online use "RMAN" utility to back up database

Oracle DataPump - ExpDp

It is advisable to use more than one of these methods to backup your database.

For example, if you choose to do Online database backups; also cover yourself by doing database exports.

Also test all backup and recovery scenarios carefully. It is better to be safe than sorry.

Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files etc.

If your database is in ARCHIVELOG mode, you also need to backup archived log files.

Why and when should one backup database?

Backup and recovery is one of the most important aspects of a DBA job.

If you lose your company's data, you could very well lose your job.

Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule.

Backup frequency normally depends on the following factors
Rate of data change/ transaction rate
Database availability/ Can you shutdown for cold backups?
Criticality of the data/ Value of the data to the company
Read – only table space needs backing up just once right after you make it read only
If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes

Carefully plan backup retention periods.

Ensure enough backup media (tapes) are available and that old backups are expired in – time to make media available for new backups.

Offsite vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios.

Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunications

Can one resize tablespaces and datafiles

One can manually increase or decrease size of a datafile from Oracle 7.2 using command.

SQL>ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;

Because one can change the sizes of data files, one can add more space to your database without adding more datafiles. This is beneficial if concerned about reaching the maximum number of datafiles allowed in your database
Manually reducing the sizes of datafiles allows one to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.

Also, datafiles can be allowed to automatically extend if more space is required.

Command

SQL>CREATE TABLESPACE pcs_data_ts
DATAFILE 'c: \ora_apps\pcs\pcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE (INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table.

A list of defined policies can be obtained from DBA_AUDIT_POLICIES

Audit records are stored in DBA_FGA_AUDIT_TRAIL

Example:

Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

SQL>analyze table EMP compute statistics;
SQL>select * from EMP where c1 = 11; -- Will trigger auditing
SQL>select * from EMP where c1 = 09; -- No auditing

Now we can see the statements that triggered the auditing condition...
SQL>select sqltext from sys.fga_log$;
SQL>delete from sys.fga_log$;

When should one rebuild an index?

You can run 'ANALYZE INDEX VALIDATE STRUCTURE' command on the affected indexes; each invocation of this command creates a single row in the INDEX_STATS view.

This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE

The 'badness' of the index can then be judged by the ratio of 'DEL_LF_ROWS' to 'LF_ROWS'

How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called RMAN)

Assign an appropriate table space to it and grant it recovery_catalog_owner role.

sqlplus sys
SQL>create user RMAN identified by RMAN;
SQL> alter user RMAN default table space tools temporary tablespace temp;
SQL> alter user RMAN quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to RMAN;

Next, log in to RMAN and create the catalog schema.

Prior to Oracle 8i this was done by running catRMAN.sql script.

RMAN> catalog RMAN/RMAN
RMAN>create catalog tablespace tools;
RMAN> exit;

You can now continue by registering your databases in the catalog.

RMAN catalog RMAN/RMAN target backdba/backdba
RMAN> register database;

I've lost some Rollback Segments, how can I get my DB back

Restart your database with following INIT.ORA parameter _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04) if one of your rollback segments is corrupted.

You can then drop the corrupted rollback segments and create it from scratch.

Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed.

One can very well end up with lost or inconsistent data!!!

Please contact Oracle Support before using it.

_Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04)

I've lost my REDO LOG files, how can I get my DB back?

The following INIT.ORA parameter _allow_resetlogs_corruption = true may be required if your current redo logs are corrupted or blown away.

Caution is advised when enabling this parameter as you might end up losing your entire database

Please contact Oracle Support before using it. _allow_resetlogs_corruption = true

My database is down and I cannot restore. What now?

Recovery without any backup is normally not supported; however, Oracle Consulting can sometimes extract data from an Offline database using a utility called DUL (Disk Unload).

This utility reads data in the data files and unloads it into SQL*Loader or export dump files.

DUL does not care about rollback segments, corrupted blocks etc. and can thus not guarantee that the data is not logically corrupt

It is intended as an absolute last resort and will most likely cost your company a lot of money!

My database was terminated while in BACKUP MODE, do I need to recover

If a database was terminated while one of its tablespaces was in BACKUP MODE

ALTER TABLESPACE <table space name> BEGIN BACKUP;

It will tell you that media recovery is required when you try to restart the database.

DBA is then required to recover the database and apply all archived logs to the database.

However, from Oracle7.2, you can simply take the individual data files out of backup mode and restart the database

ALTER DATABASE DATAFILE '/path/filename' END BACKUP;

One can select from V$BACKUP to see which data files are in backup mode. This normally saves a significant amount of database down time.

From Oracle9i onwards, the following command can be used to take all of the data files out of hot backup mode

ALTER DATABASE END BACKUP;

The above commands need to be issued when the database is mounted

What are the common Import / Export problems

ORA – 00001: Unique constraint (...) violated
You are importing duplicate rows.

Use IGNORE=NO to skip tables that already exist

(Imp will give an error if the object is re – created)

ORA – 01555: Snapshot too old
Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO

ORA – 01562: Failed to extend rollback segment
Create bigger rollback segments or set parameter
COMMIT = Y while importing

IMP – 00015: Statement failed ... object already exists...

Use IGNORE = Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows

Can one export to multiple files? Can one beat UNIX 2 Gig limit

From Oracle8i, export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi – file export you must provide the same filenames in the same sequence in the FILE = parameter

Look at this example

exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m LOG=scott.log

Use the following technique if you use an Oracle version prior to 8i

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.

# creates a named pipe
mknod exp.pipe p

# read the pipe – output to zip file in the background
gzip < exp.pipe > scott.exp.gz &

# feed the pipe
exp userid=scott/tiger file=exp.pipe...

Can one import/export between different versions of Oracle

Different versions of import utility are upward compatible. This means that one can take an export file created from an old export version and import it using a later version of the import utility.

This is quite an effective way of upgrading a database from one release of Oracle to the next.

Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility)

For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow Oracle 7.3 exp/imp utilities to run against an Oracle 8 database

Does one need to drop / truncate objects before importing

Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables.

One must always DROP existing sequences before re – importing.

If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database.

Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported.

Where should be the tuning effort directed

Consider the following areas for tuning.

The order in which steps are listed needs to be maintained to prevent tuning side effects.

For example, it is not good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement. Database Design (if it's not too late)
Poor system performance usually results from a poor database design.
One should generally normalize to the 3^rd Normalization Format.
Selective denormalization can provide valuable performance improvements.
When designing, always keep the "data access path" in mind.
Also look at proper data partitioning, data replication and aggregation tables for decision support systems etc.

Application Tuning
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.
Memory Tuning

Properly size your database buffers (shared pool, buffer cache, log buffer etc.) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.
Disk I/O Tuning
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation etc
Eliminate Database Contention
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System

Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

What database aspects should be monitored

One should implement a monitoring system to constantly monitor the following aspects of a database.

Writing custom scripts, implementing Oracle's Enterprise Manager or buying a third party monitoring product that can achieve this. If an alarm is triggered, system should automatically notify the DBA (e – mail, pager etc.) to take appropriate action.

Infrastructure availability

Is the database up and responding to request
Are the listeners up and responding to request
Are Oracle Names and LDAP Servers up and responding to request
Are the Web Listeners up and responding to requests

Things that can cause service outrages

Is the archive log destination filling up?
Objects getting close to their max extents
User and process limits reached

Why and when should one tune

One of the biggest responsibilities of a DBA is to ensure that Oracle database is tuned properly.

Oracle RDBMS is highly tunable and allows database to be monitored and adjusted to increase its performance.

One should do performance tuning for the following reasons

The speed of computing might be wasting valuable human time (users waiting for response)

Enable your system to keep up with the speed business is conducted and Optimize hardware usage to save money (companies are spending millions on hardware)

How does one add users to a password file

One can select from SYS.V_$PWFILE_USERS view to see which users are listed in the password file.

New users can be added to the password file by granting them SYSDBA or SYSOPER privileges or by using the orapwd utility.

SQL>GRANT SYSDBA TO Scott;

How does one create a password file

Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with

administrative privileges

One needs to create a password file before remote administrators (like OEM) will be allowed to connect.

Follow this procedure to create a new password file

Command

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

Shutdown the database

SQL> SHUTDOWN IMMEDIATE

Edit INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE = exclusive is set.

Startup the database

SQL> STARTUP

NOTE: The orapwd utility presents a security risk in that it receives a password from command line.

This password is visible in the process table of many systems.

An administrator needs to be aware of this!

How does one connect to an administrative user

If an administrative user belongs to "dba" group on UNIX or "ORA_DBA" (ORA_sid_DBA) group on Windows, One can connect to Oracle Database like this

connect / as sysdba

No password is required. This is equivalent to the desupported "connect internal" method.

A password is required for "non – secure" administrative access. These passwords are stored in password files.

Remote connections via Net8 are classified as non – secure

Example

connect sys/password as sysdba

What is an administrative (privileged) user

Oracle DBAs and operators typically use administrative accounts to manage database and database instance.

An administrative account is a user that is granted SYSOPER or SYSDBA privileges.

SYSDBA and SYSOPER allow access to a database instance even if it is not running.

Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with orapwd utility

Does Oracle write to data files in begin/hot backup mode

Oracle will stop updating file headers, but will continue to write data to database files even if a table space is in backup mode.

In backup mode, Oracle will write out complete changed blocks to the Redo log files.

Normally only changes are logged to the Redo logs; this is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during online backups

How does one backup archived log files

One can backup archived log files using RMAN or any operating system backup utility.

Remember to delete files after backing them up to prevent archive log directory from filling up. If the archive log directory becomes full, your database will hang!

Look at this simple RMAN backup script

RMAN> run {

Allocate channel dev1 type disk;

Backup Format '/app/oracle/arch_backup/log_t%t_s%s_p%p'

(Archive log all delete input);

Release channel dev1;

}

How does one put a database into ARCHIVE LOG mode

The main reason for running database in archive log mode is that one can provide 24 hour availability and guarantee complete data recovery

It is also necessary to enable ARCHIVELOG mode before one can start to use online database backups.

To enable ARCHIVELOG mode, simply change your database startup command script and bounce the database

SQL> connect sys as sysdba

SQL> shutdown immediate;

SQL> startup mount exclusive;

SQL> alter database archivelog;

SQL> archive log start;

SQL> alter database open;

NOTE1: Remember to take a baseline database backup right after enabling archive log mode. Without it one would not be able to recover. Also, implement an archive log backup to prevent the archive log directory from filling – up.

NOTE2: ARCHIVE LOG mode was introduced with Oracle Version 6 and is essential for database point – in – time recovery. Archiving can be used in combination with online and offline database backups.

NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE,

log_archive_dest=... and

log_archive_format=...

NOTE4: You can change archive log destination of a database online with ARCHIVE LOG START TO 'directory' statement. This statement is often used to switch archiving between a set of directories.

NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to Archive Log mode.

How does one backup a database using RMAN

The biggest advantage of RMAN is that it only backup used space in the database.

RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead.

RMAN will re – read database blocks until it gets a consistent image of it.

Look at this simple backup example

RMAN target sys/*** nocatalog

Run {

Allocate channel t1 type disk;

Backup

Format '/app/oracle/db_backup/%d_t%t_s%s_p%p'

(Database);

Release channel t1;

}

Example

RMAN restore

RMAN target sys/*** nocatalog

Run {

Allocate channel t1 type disk;

# set until time 'Aug 07 2000:51';

Restore table space users;

Recover table space users;

Release channel t1;

}

Examples above are extremely simplistic and only useful for illustrating basic concepts.

By default Oracle uses the database control files to store information about backups.

Normally one would rather setup a RMAN catalog database to store RMAN metadata in.

Read Oracle Backup and Recovery Guide before implementing any RMAN backups

Note: RMAN cannot write image copies directly to tape. One needs to use a third party media manager that integrates with RMAN to backup directly to tape.

Alternatively one can backup to disk and then manually copy the backups to tape.

How does one do Online database backup

Each tablespace that needs to be backed up must be switched into backup mode before copying the files out to secondary storage (tapes)

Look at this simple example

SQL>ALTER TABLESPACE xyz BEGIN BACKUP;

Then Copy

!cp xyfFile1 /backupDir/

SQL>ALTER TABLESPACE xyz END BACKUP;

It is better to backup table space than to put all table spaces in backup mode.

Backing them up separately incurs less overhead. When done, remember to backup your control files.

Look at this example

SQL>ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers

SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';

Note: Do not run Online backups during peak processing periods.

Oracle will write complete database blocks instead of the normal deltas to redo logfiles while in backup mode.

This will lead to excessive database archiving and even database freezes.

How does one do Offline database backup

Shutdown the database from SQLPLUS or Server Manager.

Backup all files to secondary storage (e.g. tapes). Ensure that you backup all data files, all control files and all logfiles.

When completed, restart your database.

Do the following queries to get a list of all files that needs to be backed up

SQL>Select name from sys.v_$datafile;

SQL>Select member from sys.v_$logfile;

SQL>Select name from sys.v_$controlfile;

Sometimes Oracle takes forever to shutdown with the "immediate" option.

As workaround to this problem, shutdown using these commands

SQL>alter system checkpoint;

SQL>shutdown abort

SQL>startup restrict

SQL>shutdown immediate

Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off line backup.

If you cannot take your database down for a cold (Offline) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (Online) backups.

How can one see that is using a temporary segment

For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'

All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. If you’re temporary tablespace is set to TEMPORARY all sorts are done in one large temporary segment

For usage stats, see SYS.V$SORT_SEGMENT from Oracle 8.0, one can just query SYS.v$sort_usage

Example

SQL>select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v$sort_usage u
where s.saddr = u.session_addr;

SQL>select s.osuser, s.process, s.username, s.serial#,
Sum (u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter VP
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value;

How do I find used or free space in a TEMPORARY tablespace

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE

Instead use V$TEMP_SPACE_HEADER view

SQL>SELECT tablespace_name, SUM (bytes_used), SUM (bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name

Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE

Tempfiles, unlike normal data files are not listed in v$datafile or dba_data_files.

Instead query v$tempfile or dba_temp_files

SQL>SELECT * FROM v$tempfile;

SQL>SELECT * FROM dba_temp_files;

How does one see the uptime for a database

Look at the following SQL query

SQL>SELECT to_char (startup_time, 'DD – MON – YYYY HH24: MI: SS') "DB Startup Time"
FROM sys.v_$instance;
or
SQL>SELECT to_char (logon_time, 'Dy dd Mon HH24: MI: SS') "DB Startup Time"
FROM sys.v_$session
WHERE Sid=1; /* this is pmon */

Can one rename a database user (schema)

No, this is listed as Enhancement Request 158508

Workaround

Do a user – level export of user A

Create new user / schema by name of B

Import system/manager fromuser=A touser=B

SQL>Drop user A

How are extents allocated to a segment

Oracle8 and above rounds off extents to multiple of 5 blocks when more than 5 blocks are requested

If one requests 16K or 2 blocks (assuming an 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested.

If one asks for 8 blocks, Oracle will round it up to 10 blocks.
Space allocation also depends upon size of contiguous free space available. If one asks for 8 blocks and Oracle finds contiguous free space that are exactly 8 blocks it would give it to you.

If it were 9 blocks, Oracle would also give it to you.

Clearly Oracle doesn't always round extents to a multiple of 5 blocks.
The exception to this rule is locally managed table spaces. If a table space is created with local extent management and the extent size is 64K, then Oracle allocates 64K or 8 blocks assuming 8K – block size. Oracle doesn't round it up to multiple of 5 when a tablespace is locally managed

Where can one find the high watermark for a table

There is no single system table, which contains the High Water Mark (HWM) for a table.

A table's HWM can be calculated using results from the following SQL statements

SQL>SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER = UPPER (owner)
AND SEGMENT_NAME = UPPER (table);

ANALYZE TABLE owner.Table ESTIMATE STATISTICS;

SQL>SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER = UPPER (owner)
AND SEGMENT_NAME = UPPER (table);

Thus, the tables HWM = (query result 1) - (query result 2) – 1

NOTE: You can also use DBMS_SPACE package and calculate

HWM = TOTAL_BLOCKS – UNUSED_BLOCKS – 1

How does one prevent table space fragmentation

Always set PCTINCREASE to 0 or 100.

Bizarre values for PCTINCREASE will contribute to fragmentation.

For example if you set PCTINCREASE to 1 you will see that your extents are going to have weird and wacky sizes: 100K, 100K, 101K, 102K etc.

Such extents of bizarre size are rarely re – used in their entirety.

PCTINCREASE of 0 or 100 gives you nice round extent sizes that can easily be reused.

Example: .100K, 100K, 200K, 400K etc.

Note that the number of extents per segment does not cause any performance issue anymore, unless they run into thousands and thousands where additional I/O may be required to fetch the additional blocks where extent maps of the segment are stored.

How does one coalesce free space?

SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time.

SMON will not coalesce free space if a table space’s default storage parameter "pctincrease" is set to 0. With Oracle 7.3 one can manually coalesce a table space using

ALTER TABLESPACE ... COALESCE;

Command, until then use

SQL> alter session set events 'immediate trace name coalesce level n';

Where 'n' is the tablespace number you get from

SELECT TS#, NAME FROM SYS.TS$;

You can get status information about this process by selecting from SYS.DBA_FREE_SPACE_COALESCED dictionary view

What database block size should I use

Oracle recommends that your database block size match or be in multiples of your Operating System block size

One can use smaller block sizes, but the performance cost is significant.

Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size with fewer, but larger transactions as with a DSS application use a larger block size.

If you are using a Volume Manager, consider your "Operating System block size" to be 8K, this is because Volume Manager products use 8K blocks (and this is not configurable)

ORA - 19625: error identifying file

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

Starting backup
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 10/09/2014 00:16:23
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/oradata/archive/1_241_850299214.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Cause: RMAN cannot find the following archive log file in corresponding destination cause of expire or delete file.

Solution: You can check your expire archive log list and delete expired archive log files from RMAN, If you really lost your archive log file and you are not able to collect this file.

RMAN> CROSSCHECK ARCHIVELOG ALL;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
validation failed for archived log
archived log file name=/u01/app/oracle/oradata/archive/1_241_850299214.arc RECID=351 STAMP=860457193
validation failed for archived log
archived log file name=/u01/app/oracle/oradata/archive/1_242_850299214.arc RECID=351 STAMP=860457670
validation succeeded for archived log
archived log file name=/u01/app/oracle/oradata/archive/1_243_850299214.arc RECID=352 STAMP=860458582
Crosschecked 3 objects

RMAN> DELETE EXPIRED ARCHIVELOG ALL;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
List of Archived Log Copies for database with db_unique_name ORA11GS
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
2730 1 241 X 08-OCT-14
Name: /u01/app/oracle/oradata/archive/1_241_850299214.arc

3002 1 242 X 08-OCT-14
Name: /u01/app/oracle/oradata/archive/1_242_850299214.arc

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/oradata/archive/1_241_850299214.arc RECID=351 STAMP=860457193
deleted archived log
archived log file name=/u01/app/oracle/oradata/archive/1_242_850299214.arc RECID=351 STAMP=860457670
Deleted 2 EXPIRED objects

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

Starting backup
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=243 RECID=352 STAMP=860458582
input archived log thread=1 sequence=244 RECID=353 STAMP=860459023
channel ORA_DISK_1: starting piece 1 at 09-OCT-14
channel ORA_DISK_1: finished piece 1 at 09-OCT-14
piece handle=/u01/rman_backup/ora11gs_bkp_1gpkj40g tag=TAG20141009T002344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/archive/1_243_850299214.arc RECID=352 STAMP=860458582
archived log file name=/u01/app/oracle/oradata/archive/1_244_850299214.arc RECID=353 STAMP=860459023
Finished backup at 09-OCT-14

Starting backup at 09-OCT-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11gp/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11gp/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11gp/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11gp/users01.dbf
channel ORA_DISK_1: starting piece 1
channel ORA_DISK_1: finished piece 1
piece handle=/u01/rman_backup/ora11gs_bkp_1hpkj40h tag=TAG20141009T002345 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup

Starting backup
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=245 RECID=354 STAMP=860459111
channel ORA_DISK_1: starting piece 1 at 09-OCT-14
channel ORA_DISK_1: finished piece 1 at 09-OCT-14
piece handle=/u01/rman_backup/ora11gs_bkp_1ipkj437 tag=TAG20141009T002511 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/oradata/archive/1_245_850299214.arc RECID=354 STAMP=860459111
Finished backup

Starting Control File and SPFILE Autobackup
piece handle=/u01/rman_backup/bkp_cf_c-3519562480-20141009-01 comment=NONE
Finished Control File and SPFILE Autobackup

RMAN>

ORA-38706: Cannot turn on FLASHBACK DATABASE logging

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

Cause : For enable flashback database must be archive mode. To resolve this error enable archive log mode.

SQL>SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 381685712 bytes
Database Buffers 243269632 bytes
Redo Buffers 3330048 bytes
Database mounted.

SQL> archive log list;

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/oradata/archive
Oldest online log sequence 434
Current log sequence 436

SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL>alter database open;
database altered

SQL>select flashback_on from v$database;
FLASHBACK_ON
--------------------
ON

./ggsci: error while loading shared libraries: libnnz11.so

Following error might encountered while initializing ggsci prompt for Oracle Golden Gate

[oracle@oraprm ogg_1]$ ./ggsci

./ggsci: error while loading shared libraries: libnnz11.so: 

cannot open shared object file: No such file or directory

This error is encountered as ggsci is unable to locate lib files which are available under database ORACLE_HOME. This error can be avoided by

Set LD_LIBRARY_PATH to point to "lib" directory under database ORACLE_HOME

[oracle@orapm ogg_1]$ cd $HOME

[oracle@orapm ogg_1]$ gedit .bash_profile

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[oracle@oraprm ogg_1]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (oraprm.localdomain) 1>

INS-10002 Inventory Initialization failed

This error occurs when installing Oracle Client_32 bit on Windows 64 bit Server

To resolve this error, delete Registry Entry "inst_loc"

Type Regedit in "Run" Dialog Box from Start Menu

Go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

and Delete "inst_loc"

Close the Registry and install Oracle Client 12c again

Try deleting repository before Creating Repository

This error might occur if there is an existing repository

To solve this error

Delete the Repository

or

Drop Oracle user who has created the Repository and again recreate it