Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

10g VS 11g – Differences i’ve noticed so far.

Posted by FatDBA on November 29, 2012

Differences:

1. by default datafiles and flash recovery options are located one level beloe the ORACLE BASE location but in 10g it is used to be saved under oracle home directory.

2. ADR (Automatic Diagnostic Repository) – is a single directory location for all error and trace data in the database. disgnostic directories such as bdump, cdump and udump is replaced with this option in 11g.

3. Deprecated: iSQL*Plus, Oracle Enterprise Manager Java Console.

4. New Paramaters: Memory_max_target –> sets the maximum value for Memory_Target parameter.
SEC_CASE_SENSITIVE_LOGON: enable or disable password case sensitivity Default: True.
SEC_MAX_FAILED_LOGIN_ATTEMPTS: drops the connection after specified number of login attempts     fail for any user (Default – 10)

5. Oracle SQL Developer.

6. Oracle Database Vault: enabled you to secure business data even from DBA’s.

7. Oracle Wrehouse Builder: BI tool.

8. New privelage SYSASM – Should be granted to user’s who perform ASM related tasks.
The CONNECT command in Oracle 11g SQL*Plus is enhanced to allow connecting as ASM administrator.
CONN[ECT] [{logon | / } [AS {SYSOPER | SYSDBA | SYSASM}]]

SYSASM Privilege and OSASM Group
This feature introduces a new SYSASM privilege that is specifically intended for performing ASM administration tasks.

9. ‘Connect‘ role has now only create CREATE SESSION provilege.

Oracle 9i

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘CONNECT’;

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
Oracle 11g/10g

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘CONNECT’;

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
CONNECT                        CREATE SESSION                           NO

For security consideration, Oracle change right scheme of CONNECT in oracle 11g (The same was available in 10g as well) 

10. Use ALTER FLASHBACK command to alter a flashback archive object. Below are examples of using this command:
ALTER FLASHBACK ARCHIVE myflash SET DEFAULT;

11. Read-Only Tables
In Oracle 11g, you can set a table to be read only, i.e. users can only query from the table but no DML statement is allowed on     the table. Following are the commands to achieve this:
ALTER TABLE employees READ ONLY;
ALTER TABLE employees READ WRITE;

12. Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles. Following are the commands to achieve that:
ALTER TABLESPACE temp SHRINK SPACE KEEP 100M; — the KEEP keyword is optional.
ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/app/oracle/oradata/ora11g/temp01.dbf’;

13. Creating an Initialization Parameter File from Memory
In Oracle 11g, you can create a pfile or spfile from the current values of the initialization parameters (active in the  instance). Following is the code example to do so:
CREATE PFILE FROM MEMORY;
CREATE SPFILE FROM MEMORY;

14. Restore Point Enhancements
Creating Restore Point “as of” an SCN or a Timestamp
With Oracle 11g, you can create a restore point for a specific SCN in the past or a past point in time.
CREATE RESTORE POINT res_jun08 AS OF SCN 2340009;

15. When a client process or thread connects to the database, the connection is established between the client and the connection broker (it is a new background process named as connection monitor CMON) in Oracle database.

16. SQL*Plus Error Logging
In Oracle 11g SQL*Plus, the new command SET ERRORLOGGIN can be used to store all errors resulting from executed SQL, PL/SQL and     SQL*Plus commands in special error logging table (by default it is SPERRORLOG).
Following are the commands to enable and disable error logging in SQL*Plus:
SQL>SHOW ERRORLOGGING
SQL>SET ERRORLOGGIN ON
SQL>SET ERRORLOGGIN OFF

The SET ERRORLOGGIN ON command creates the error log table. To view the error stored in the table, use a query like the following:
SELECT USERNAME, STATEMENT, MESSAGE
FROM SPERRORLOG;

17. Oracle Database 11g has new option named as Oracle Advanced Table Compression option which aims at reducing space occupied by     data for both OLTP and warehouse databases.
<<<Using Advanced Compression Option in Oracle 11g requires a separate license from Oracle.>>>
Compression in RMAN Backups
The Oracle Advanced Compression option reduces the compression ratio in RMAN backup (by 20%) and increases the backup performance (by 40%) than it does in Oracle 10g.

18. Enhanced TRUNCATE Statement
DROP ALL STORAGE is a new option added to TRUNCATE statement in Oracle 11g R2. It drops the whole segment. In addition to the     TRUNCATE TABLE statement, DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE (SUB)PARTITION statement.
TRUNCATE TABLE emp DROP ALL STORAGE;

19. Default Retention of AWR Snapshots Changed
By default, Oracle Database 11g will now retain eight days of AWR snapshot information (as opposed to seven in Oracle 10g).

20. Stronger Password Hash Algorithm
In Oracle Database 11g, the SHA-1 standard became the new algorithm for password hashing.

21. Case-Sensitive Passwords
Oracle 11g introduces case-sensitive passwords for databases created with the default Oracle Database 11g enhanced security.
The SEC_CASE_SENTITIVE_LOGON parameter must be set to TRUE to enable case-sensitive database passwords.
alter system set SEC_CASE_SENSITIVE_LOGON = TRUE;

22. Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover … block command replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted     versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
Following are examples of using the new command:
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10;
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10 FROM TAG = sundaynight;
RECOVER DATAFILE 2 BLOCK 24 DATAFILE 4 BLOCK 10 FROM BACKUPSET = 11;

23. New RMAN Configuration Parameters
The COMPRESSION ALGORITHM and ARCHIVELOG DELETION POLICY parameters are new RMAN configuration parameters in the Oracle Database 11g.

You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When
Following are examples:

— archived redo logs are eligible for deletion when there are at least two backups of them on the tape
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO SBT;

24. VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles, backup sets, and even     individual data blocks. The command by default checks for physical corruption. You can optionally specify CHECK LOGICAL.     Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Following are examples of some VALIDATE command options:
validate [CHECK LOGICAL] database;
validate SKIP INACCESSIBLE database;
validate copy of database;
validate tablespace hr_tbs;
validate copy of tablespace hr_tbs;
validate backupset 17,18;
validate datafile 3 block 24;

25. Active Database Duplication
In Oracle Database 11g, you can directly duplicate a database over the network without having to back up and provide the source  database files. This direct database duplication is called active database duplication. It can be done either with Database  Control or through RMAN.

Create a password file in the destination server with the same SYSDBA password as the source database. You can create the password file manually or by specifying the PASSWORD FILE option on the DUPLICATE command.

— use PASSWORD FILE option
RMAN>DUPLICATE TARGET DATABASE … PASSWORD FILE …

— duplicating a database to a remote host with the same directory structure
DUPLICATE TARGET DATABASE
TO dupdb
PASSWORD FILE — RMAN should duplicate the password file from the source
FROM ACTIVE DATABASE
SPFILE
NOFILENAMECHECK;

26. Data Pump Utilities
Compression Enhancement
In Oracle Database 11g, Oracle provides the mechanism to compress both data and metadata during the extract operation. The     available options for the COMPRESSION parameter are as follows:
compression={all | data_only | metadata_only | none}

27. Reusing a Dump File
In Oracle 11g data pump export utility, the new parameter REUSE_DUMPFILES enables you to overwrite a preexisting dump file.
Following is an example:
expdp hr DIRECTORY=dp_dir DUMPFILE=hr.dmp TABLES=employees REUSE_DUMPFILES=y

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: