Różności …

Disable archive log mode – np. przed Importem

Shutdown edit pfile / spfile (spfile when instance is up of course) to say log_archive_start = false.

startup mount

alter database noarchivelog;

alter database open;

Remember that once you disable archive log mode you need to take a fresh backup once archive log mode is turned back on.

Object Already Exists

If a database object to be imported already exists in the database, an object creation error occurs. What happens next depends on the setting of the IGNORE parameter.

If IGNORE=n (the default), the error is reported, and Import continues with the next database object. The current database object is not replaced. For tables, this behavior means that rows contained in the export file are not imported.

If IGNORE=y, object creation errors are not reported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors (such as operating system, database, and SQL errors) are reported and processing may stop.

Specifying IGNORE=y can cause duplicate rows to be entered into a table unless one or more columns of the table are specified with the UNIQUE integrity constraint. This could occur, for example, if Import were run twice.

COMMIT

Default: n

Specifies whether Import should commit after each array insert. By default, Import commits only after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object.

CONSTRAINTS

Default: y

Specifies whether or not table constraints are to be imported. The default is to import constraints. If you do not want constraints to be imported, you must set the parameter value to n.

Note that primary key constraints for index-organized tables (IOTs) and object tables are always imported.

Źródła:

http://download.oracle.com/docs/cd/B10500_01/server.920/a96652/ch02.htm

http://www.dba-oracle.com/bk_disable_archive_log_mode.htm

data segments

rollback segments – store undo information (for rolling back database transactions, store data for read consistency)

index segments

temporaty segments – dynamicly created (sorting np. “order by”)

EXTENT – contiguous set of data blocks.

one to many relation

one to many relation (jeden segment może zawierać wiele extentow)

Oracle began providing its own volume manager software for Linux and Windows in
Oracle9i Release 2. Since Oracle Database 10g, database releases for all supported
operating systems include a cluster file system and volume manager in the database
that is leveraged by ASM. When using ASM, it is recommended that you not try to
leverage an operating system volume manager.

Consider an Oracle database with an 8 KB data block size and the DB_FILE_
MULTIBLOCK_READ_COUNT initialization parameter set to 32. There will be two
sizes of I/O by Oracle: a single 8 KB data block and a 256 KB multiblock read (32 times
8 KB).

Two types of parallelism are possible within an Oracle database:
Block-range parallelism Driven by ranges of database blocks
ability to dynamically parallelize table scans and a variety of scan-based functions.Oracle7 implemented blockrange parallelism by dynamically breaking a table into pieces, each of which was a range of blocks, and then used multiple processes to work on these pieces in parallel.
Partition-based parallelism Driven by the number of partitions or subpartitions involved in the operation.
With partitioned tables, introduced in Oracle8, an operation may involve one, some, or all of the partitions of a partitioned table.

An Oracle instance has a pool of parallel execution (PE) processes that are available to the database users. Oracle8i introduced the notion of self-tuning adaptive parallelism. This feature automatically scales down parallelism as the system load increases and scales it back up as the load decreases.(193)

TABLESPACE -> SEGMENT -> EXTENTS -> DATA BLOCKS

PCTINCREASE – o jaki procent następny extent ma być większy od poprzedniego

The first place you’ll likely begin looking for resource bottlenecks is in the Oracle database software using Oracle Enterprise Manager.

Oracle’s dynamic performance views provide insight into bottlenecks within your Oracle database. Prior to the introduction of Oracle’s Automatic Workload Repository (AWR), the Automatic Database Diagnostics Monitor (ADDM), and Oracle Enterprise Manager Grid Control in Oracle Database 10g, querying the performance views often was the first step database administrators performed in determining bottlenecks. All of these performance views have names that begin with V$, and, from Oracle9i on, there are also global views (for all nodes in a Real Application Clusters or RAC database) that begin with GV$.

V$SYSTEM_EVENT – Provides aggregated, system wide information about the resources for which the whole instance is waiting
V$SESSION_EVENT - Provides cumulative list of events waited for in each session
V$SESSION_WAIT -  Provides detailed, session-specific information about the resources for which individual sessions are currently waiting or last waited for
V$SESSION – Provides session information for each current session including event currently or last waited for

You may find that your problem has a simple source, such as a lower-than-expected database buffer cache hit ratio. Since the cache is not working at its optimal level, you could simply increase the initialization parameter DB_BLOCK_BUFFERS to increase the size of the cache and possibly improve the hit ratio. You can monitor the performance of the buffer cache hit ratio in V$METRICNAME.

The Automatic Workload Repository (AWR) captures and stores information about resource utilization by Oracle workloads. By default, statistics are captured every 30 minutes and are stored for 7 days. These statistics are accessible through views, but Enterprise Manager provides a much simpler-to-use interface.

Oracle’s Automatic Database Diagnostic Monitor ADDM automatically identifies and reports on resource bottlenecks, such as CPU contention, locking issues, or poor performance from specific SQL statements. For tuning your applications, you’ll likely look to the SQL Advisor – it combines the functionality of the SQL Tuning Advisor, the SQL Access Advisor, and the new Partition Advisor. The SQL Advisor leverages information on CPU and I/O consumption captured in the AWR and identifies high impact SQL statements indicated by the ADDM to make recommendations.

Memeory Advizor - For optimal setting of MEMORY_TARGET for automatic memory management in Oracle Database 11g.and optimal setting of SGA_TARGET for shared memory management.
Segment Advizor – for storage management and space allocation.
Undo Advisor –  for managing transactions.
Mean Time to Recovery (MTTR) –  optimize the setup of Oracle, including log files.

The performance of your Oracle database is based on how it uses the machine resources that are available. These machine resources include processing power or CPU, memory, disk I/O, and network bandwidth.

The slowest access is to disk and, as a result, the most common database performance issues are I/O related.

Since the introduction of Enterprise Manager 10g, a performance analyzer called Automatic PerformanceMonitoring (APM) has been included.

The number of blocks in one multiblock I/O is determined by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.

The main destinations of the I/O operations Oracle performs are the following:

• Redo logs

• Data contained in tables

• Indexes on the tables

• The data dictionary, which goes in the SYSTEM tablespace

• Sort activity, which goes in the TEMP tablespace of the user performing the sort

• Rollback information, which is spread across the datafiles of the tablespace containing the database’s rollback segments

• Archived redo logs, which go to the archived log destination (assuming the database is in ARCHIVELOG mode)

Use disk-striping technologies to spread I/O evenly across multiple spindles.
One of the most powerful ways to reduce performance bottlenecks due to disk I/O is the use of RAID disk arrays. (Redundant Array of Inexpensive (or Independent) Disks.

Use tablespaces to clearly segregate and target different types of I/O - Separate table I/O from index I/O by placing these structures in different tablespaces. Youcan then place the datafiles for these tablespaces on various disks to provide better performance for concurrent access.

Place redo logs and redo log mirrors on the two least-busy devices.

Distribute “system overhead” evenly over the available drives. For example, if the application generates a lot of data changes versus data reads, the I/O to the rollback segments may increase due to higher writes for changes and higher reads for consistent read functionality. Sort activity can also affect disk I/O (TEMP – tablespace). Oracle constantly queries and updates the data dictionary stored in the SYSTEM tablespace, and this information is cached in the shared pool section of the SGA.

Use a different device for archiving and redo log files. (183)

Every database has a pseudorole named PUBLIC that includes every user.
Base tables and data dictionary views are stored in the SYS schema. SYSTEM schema tables are used for administrative information and by various Oracle tools and options.
Po połączeniu “as sysdba” można wykonać: startup, shutdown, alter database mount, alter database open, alter database backup control file,  alter database archivelog, alter database recover, create database, drop database, create spfile, restricted session.
Sysoper nie może zrobić: create i drop database.

The CONNECT INTERNAL syntax supported in earlier releases of Oracle is no longer available. When operating system authentication is used, administrative users must be named in the OSDBA or OSOPER defined groups. For password file authentication, the file is created with the ORAPWD utility. Users are added by SYS or by those having SYSDBA privileges.

A policy is a way to extend your security framework. You can specify additional requirements in a policy that are checked whenever a user attempts to activate a role. Policies are written in PL/SQL and can be used, for example, to limit access to a particular IP address or to particular hours of the day.

FGAC - fine-grained access control. Security policies implemented as PL/SQL functions can be associated with tables or views enabling creation of a virtual private database (VPD).You can associate a security policy with a particular view or table by using the builtin PL/SQL package DBMS_RLS, which also allows youto refresh, enable, or disable a security policy.

Oracle Database 10g and newer database releases feature a VPD that is even more fine-grained, enabling enforced rewrites when a query references a specific column.

The Oracle Label Security Option eliminates the need to write VPD PL/SQL programs to enforce row-level label security.

Policies are created and applied, sensitivity labels are defined, and user labels are set and authorized through a policy manager tool accessible through EM.

Application developers can place a SET ROLE command at the beginning of an application to enable the appropriate role and disable others only while the application is running. Similarly, you can invoke a DBMS_SESSION.SET_ROLE procedure from PL/SQL.

Another way application security is sometimes accomplished is by encapsulating privileges in stored procedures. Instead of granting direct access to the various tables
for an application, youcan create stored procedures that provide access to the tables and grant access to the stored procedures instead of the tables. For example, instead of granting INSERT privileges for the EMPLOYEE table, youmight create and grant access to a stored procedure called HIRE_EMPLOYEE that accepts as parameters all the data for a new employee.

When you run a stored procedure normally, the procedure has the access rights that were granted to the owner of the procedure; that owner is the schema in which the procedure resides. If a particular schema has access to a particular database object, all stored procedures that reside in that schema have the same rights as the schema. When any user calls one of those stored procedures, that user has the same access rights to the underlying data objects that the procedure does.

If youattach the keyword AUTHID CURRENT_USER to a stored procedure when it is compiled, security restrictions will be enforced based on the username of the user invoking the procedure, rather than the schema that owns the stored procedure.

Global authentication allows you to maintain a single authentication list for multiple distributed databases.

In typical three-tier implementations, the Oracle Application Server runs some of the application logic, serves as an interface between the clients and database servers, and provides much of the Oracle Identity Management (OIM) infrastructure.

The Oracle Advanced Security Option (ASO), is used in distributed environments linked via Oracle Net in which there are concerns regarding secure access and transmission of data. This option specifically provides data encryption during transmission.

Transparent Data Encryption - the database does the work of encrypting and decrypting data automatically. Data sent to the database is encrypted by Oracle, and data requested from the database is decrypted. No additional code is required in an application. Oracle Database 11g allows youto encrypt entire tablespaces.

Compliance
The Oracle Database Vault Option - restricts DBAs and other highly privileged users from accessing application data to which they should not have access. Key parameters defined in the Oracle Database Vault Option are called factors. Factors include things such as specific application programs, locations, or times of day.

Rules can also be used to define database realms, which consist of a subset of the schemas and roles that an administrator can administer. (173)

Zalecenia:

Multiplexing online redo logs by having multiple log members per group on different disks and controllers

Running the database in ARCHIVELOG mode so that redo log files are archived before they are reused

Archiving redo logs to multiple locations

Maintaining multiple copies of the control file(s)

Backing up physical datafiles frequently—ideally, storing multiple copies in multiple locations

Information Lifecycle Management (ILM) -is most frequently used to move data among various devices that are most appropriate for hosting that data, such as different classifications of disk. (160)

SQL AdvisorStatistics containing active session history are now gathered and populate the Automatic Workload Repository (AWR).

The Automatic Database Diagnostic Monitor(ADDM) automatically tracks changes in database performance leveraging the datain the AWR.

Server-generated alerts occur “just-in-time” and appear in EnterpriseManager.

ADDM is one of several advisors present in Oracle and accessible using EnterpriseManager today. Other performance related advisors include: