Generate Tablespace DDL

GENERATE TABLESPACE DDL

select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;

or

select 'select dbms_metadata.get_ddl(''TABLESPACE'','''||tablespace_name||''') from dual;' from dba_tablespaces;

RMAN Backup File Order

RMAN BACKUP FILE ORDER

Question:
Assuming a single channel and filesperset=1, what order does RMAN choose to backup datafiles?

Answer:
The order of files is random. The File Names are read from V$DATAFILE view and due to performance bug, the ORDER BY FILE# clause is removed, hence the query can return file# in random order.

Reset Oracle Sequence in-place

HOW TO RESET A SEQUENCE IN-PLACE

  This avoids drop/create, and re-plumbing grants and synonyms.

  1. Set the "increment by" value to -nextval-1
  2. Increment the sequence
  3. Set the "increment by" value back to 1

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

1887203408

SQL> alter sequence myschema.mysequence increment by -1887203407;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         1

SQL> alter sequence myschema.mysequence increment by 1;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         2

SQL> alter system flush shared_pool;

System altered.

Oracle Database 12c Multitenant Architecture Overview

Oracle Database 12c Architecture Overview

Exadata Optimizations - Jonathan Lewis

Interesting 12c New Features

"I don't always build databases, but when I do, I prefer Oracle 12c."
 -The Most Interesting DBA in the World

Interesting 12c New Features

17-Jul-2013

• Multi-tenant Architecture
• CDB Container Database
• Common Users & Roles
• PDB Pluggable Database
• Local Users & Roles
• Up to 253 PDB's in a CDB
• Listener services for CDB and PDBs
• varchar2(32767)
• Auto Incrementing Identity Column
• Default column value can be sequence.nextval
• Fetch First and Offset syntax for top-N results
• Drop partition without "update global indexes"
• Move partition online
• CPU-Instance Fencing using "processor_group_name".  Used with Linux "control groups" or Solaris "resource pools".
• Grid Scheduler - Enterprise Job Scheduling
• DataPump timestamped logging (finally)
• DataPump export a view as table
• Application Continuity - It claims to mask db side failures to the app.
• New online operations (drop index, drop constraint, etc.)
• Invisible columns
• Move a datafile online (think online migration to ASM)
• Online Index DDL change via multiple indexes on the same column(s)
• DataGuard Max Availability performance enhancement. Standby doesn't wait for RFS to write redo to standby log(s).
• The "Far Sync" instance type, control and log files only. Data Guard transaction distributor for slow WAN's etc.
• RMAN runs native SQL
• PGA_AGGREGATE_LIMIT - to stop runaway process(es) & avoid node eviction.

18-Jul-2013

• Rebalance multiple ASM diskgroups simultaneously.
• ASM Flex server - decoupled from the database server

19-Jul-2013

• Datafiles on ACFS
• Flex Cluster - Hub/Leaf nodes.
• OCR Backup in ASM diskgroup (visible to all nodes)
• IPv6 support
• Auditing enabled by default (audit_admin role necessary to admin audit settings/data)
• Last Login
• sysbackup admin privilege for separation of duties for the backup user
• DBUA - Faster upgrades by using parallel operations (like adpatch?)
•