Pages

Tuesday 24 September 2013

Renaming the ASM DISKGROUP

Note : For this post I have referred the other source. Just for knowledge sharing i have shared the same info here :) 
Some of the times we might face the situation to change the name of the Diskgroup, The same senario has been replicated here :
SELECT NAME,STATE FROM V$ASM_DISKGROUP;
NAME                         STATE
—————————- ————
ARCHLOG                      MOUNTED
DATAFILE_ORAPROD6            MOUNTED
DATAFILE_ORAPRD7             MOUNTED
LOG1                         MOUNTED
LOG2                         MOUNTED
Before to start the actual migration found DATAFILE_ORAPRD7 dg has typo error it should be like DATAFILE_ORAPRD7 so Instead of dropping the diskgroup I decided to go for rename it.
Below are the steps which I followed to rename Diskgroup in 11.2 grid environment.
First we need to dismount the DG which we are going to rename.
Stop ASM Diskgroup.
oracle:+ASM@linuxprd5 /oracle > asmcmd umount DATAFILE_ORAPRD7
Check the Status of Diskgroup.
oracle:+ASM@linuxprd5 /oracle > crsctl stat res ora.DATAFILE_ORAPRD7.dg
NAME=ora.DATAFILE_ORAPRD7.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE
STATE=OFFLINE
Now we are good to go for Renaming Diskgroup.
Inorder to Rename any diskgroup in 11.2 gird we can use “renamedg” command.
oracle:+ASM@linuxprd5 /oracle > renamedg phase=both dgname=DATAFILE_ORAPRD7 newdgname=DATAFILE_ORAPROD7 verbose=true
Parsing parameters..
Parameters in effect:
         Old DG name       : DATAFILE_ORAPRD7
         New DG name          : DATAFILE_ORAPROD7
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=DATAFILE_ORAPRD7 newdgname=DATAFILE_ORAPROD7 verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA_DISK1 with disk number:0 and timestamp (32971243 -722582528)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA_DISK2 with disk number:1 and timestamp (32971243 -722582528)
Checking for hearbeat…
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA_DISK1 with disk number:0 and timestamp (32971243 -722582528)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:DATA_DISK2 with disk number:1 and timestamp (32971243 -722582528)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Checking disk number:1
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:DATA_DISK1
Modifying the header
Looking for ORCL:DATA_DISK2
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b4c8410a0a0
oracle:+ASM@linuxprd5 /oracle >
Connect to ASM Instance and verify the new diskgroup name.
oracle:+ASM@linuxprd5 /oracle > sqlplus / as sqlasm
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 19 03:17:26 2012


Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Automatic Storage Management option


SQL> select name ,state from v$asm_diskgroup;
NAME                          STATE
—————————– —————-
DATAFILE_ORAPROD7             DISMOUNTED  —> Diskgroup got Renamed Now.
DATAFILE_ORAPROD6             MOUNTED
ARCHLOG                       MOUNTED
LOG1                          MOUNTED
LOG2                          MOUNTED


SQL> alter diskgroup DATAFILE_ORAPROD7 mount;
Diskgroup altered.


SQL> select name ,state from v$asm_diskgroup;
NAME                          STATE
—————————– ————-
DATAFILE_ORAPROD7             MOUNTED
DATAFILE_ORAPROD6             MOUNTED
ARCHLOG                       MOUNTED
LOG1                          MOUNTED
LOG2                          MOUNTED
After Renaming the diskgroup we need to remove old diskgroup name from the cluster resource also.
oracle:+ASM@linuxprd5 /oracle > crsctl stat res -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.ARCHLOG.dg
               ONLINE  ONLINE       linuxprd5
ora.DATAFILE_ORAPROD6.dg
               ONLINE  ONLINE       linuxprd5
ora.DATAFILE_ORAPRD7.dg
               OFFLINE OFFLINE      linuxprd5   –> Still this resource is not removed from the cluster but its in OFFLINE.
ora.DATAFILE_COORAPROD73A.dg
               ONLINE  ONLINE       linuxprd5
ora.LOG1.dg
               ONLINE  ONLINE       linuxprd5
ora.LOG2.dg
               ONLINE  ONLINE       linuxprd5
ora.asm
               ONLINE  ONLINE       linuxprd5                 Started
ora.ons
               OFFLINE OFFLINE      linuxprd5
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.cssd
      1        ONLINE  ONLINE       linuxprd5
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       linuxprd5
Below are the detailed info for the new and old diskgroup resource status.
oracle:+ASM@linuxprd5 /oracle > crsctl stat res ora.DATAFILE_ORAPRD7.dg ora.DATAFILE_ORAPROD7.dg
NAME=ora.DATAFILE_ORAPRD7.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE
STATE=OFFLINE


NAME=ora.DATAFILE_ORAPROD7.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on linuxprd5
To remove diksgroup from the cluster resource we can use SRVCTL command.
oracle:+ASM@linuxprd5 /oracle > srvctl remove diskgroup -g DATAFILE_ORAPRD7


oracle:+ASM@linuxprd5 /oracle > crsctl stat res -t
——————————————————————————-
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————-
Local Resources
——————————————————————————-
ora.ARCHLOG.dg
               ONLINE  ONLINE       linuxprd5
ora.DATAFILE_ORAPROD6.dg
               ONLINE  ONLINE       linuxprd5
ora.DATAFILE_ORAPROD7.dg
               ONLINE  ONLINE       linuxprd5
ora.LOG1.dg
               ONLINE  ONLINE       linuxprd5
ora.LOG2.dg
               ONLINE  ONLINE       linuxprd5
ora.asm
               ONLINE  ONLINE       linuxprd5                 Started
ora.ons
               OFFLINE OFFLINE      linuxprd5
——————————————————————————-
Cluster Resources
——————————————————————————-
ora.cssd
      1        ONLINE  ONLINE       linuxprd5
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       linuxprd5
****************************Happy Learning *******************************

Oracle 12C new features

New features in Oracle Database 12c:
 
  ·    Oracle Database 12c, c for cloud, a multi-tenant database management system, with nearly 500 new features.
  ·   Increased size limit for VARCHAR2, NVARCHAR2, RAW data types to 32K.
  ·    Oracle Database 12c has new feature called "Identity Columns" which are auto-incremented at the time of insertion (like MySQL).
  ·    Temporary undo (for global temporary tables) will not generate undo.
  ·    Oracle Database 12c Data Pump will allow to turn off redo for the import operation only.
  ·     Enhanced statistics (Hybrid histograms for more than 254 distinct values, dynamic sampling up to eleven, stats automatically gathered during load).
  ·    No need to shutdown database to take out of archive log mode.
  ·    Row pattern matching - "MATCH_RECOGNIZATION" (identification of patterns within a table ordered/sorted by the SQL statement).
  ·    Adaptive execution plans (change of the plan at runtime).
  ·    Centralised patching.
  ·    We can test patches on database copies, rolling patches out centrally once testing is complete.
  ·     PL/SQL inside SQL: this new feature allows to use DDL inside SQL statements (i. e.: to create a one shot function)
  ·     The object DEFAULT clause has been enhanced. Adding a column to an existing table with a default value (much faster with Oracle 12c and it consumes less space than before, pointer to the Oracle Data Dictionary), applies also to sequences, identity types etc...
  ·      Partitioning enhancements (partition truncate, cascading, global index cleanup, online moving of a partition, ...)
  ·     Oracle 12c includes database level redaction, allowing granular control of access to sensitive data.
·     Pagination query, SQL keywords (LIMIT) to replace ROWNUM records

Compression:
  ·      Automated compression with heat map.
  ·      Optimisation can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.
  ·       Advanced Row compression(for Hot Data).
  ·       Columnar Query compression(for Warm Data).
  ·      Columnar Archive compression(for Archive Data).


Data Guard:
  ·      Oracle Database 12c introduces a new redo transportation method which omits the acknowledgement (to primary) of the transaction on the standby. This feature is called "Fast Sync" redo transport.
  ·       creating a new type of redo destination called "Far Sync Standby". A "Far Sync Standby" is composed only of the standby control files, the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.
  ·       Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for a role transition or not.

Pluggable Databases:
  ·         In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background  processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduce the overhead of managing multiple desperate databases.
  ·         Container Database (CDB): is the core data dictionary objects that comes after an Oracle database installation.
  ·         Pluggable Database (PDB): Data dictionary objects and data related to the application. We can have many PDB plugged into a single CDB.
  ·       All Oracle database options/features are available on the PDB level.
  ·       RMAN backup at CDB level.
  ·       We can unplug a PDB from a CDB to another CDB.
  ·        PDB's can be cloned inside the CDB.
  ·        Management of PDB (clone/creation/plug/unplug/drop) are implemented as SQLs.
  ·       Extremely fast PDB-provisioning (clone inside the CDB).
  ·       Database patch/upgrade management very quick as CDB is a single point of installation.
  ·       Each PDB has its own data dictionary.
  ·       Data Guard configuration on CDB as whole.
  ·       RMAN point-in-time recovery at PDB level (while other PDB's remains open).
  ·       Flashback of a PDB should be available for Oracle 12c Release 2.
  ·       Entire containers can be backed up in single run, regardless of how many databases they contain.
  ·       Upgrade one container database and all pluggable databases are upgraded.

Monday 23 September 2013

12C CDB+PDB creation :


12C Pluggable database creation  in NON OMF:

               Here in this post lets see how to  create the CONTAINER DB and PDB which Is one of the best feature in ORACLE 12C , we can create the normal database also which NON PDB+CDB as we did in previous (10g,11g) versions.
Here Iam using DBCA to create CDB+ PDB:
      


As shown above by default Oracle will create the datafiles in OMF mode. So if you want to create the database with usermaned file system Please choose “Advanced”.



Select General purpose:

Here iam creating only a container DB with out having any Pluggable DB’s:

                                    






We need to uncheck the OMF file system which is by default in 12C, and here I have enabled the archive log mode :

   

If you want to configure the database valut or else OID  You can configure in this:




Review the initialization parameters:

      

After this step Database creation steps will start as follows :


Check for the database summary report to which we are going to create:




After this Summary report processing of creating our new DB will start as follows:







********************HAPPY LEARNING********************



12C

12C Database Architecture :

12 C Supports Multi tenet Architechture  which is a newly added feature in Oracle 12C.The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

As shown in the above Diagram  A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

  

About Containers in a CDB
container is either a PDB or the root container (also called the root). The root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong (see "Overview of Containers in a CDB").
Every CDB has the following containers:
  • Exactly one root
The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages (see"Data Dictionary Architecture in a CDB"). A common user is a database user known in every container (see "Common Users in a CDB"). The root container is named CDB$ROOT.
The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.
  • Zero or more user-created PDBs
A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

  

Benefits of the Multitenant Architecture

Large enterprises may use hundreds or thousands of databases. Often these databases run on different platforms on multiple physical servers. Because of improvements in hardware technology, especially the increase in the number of CPUs, servers are able to handle heavier workloads than before. A database may use only a fraction of the server hardware capacity. This approach wastes both hardware and human resources.
For example, 100 servers may have one database each, with each database using 10% of hardware resources and 10% of an administrator's time. A team of DBAs must manage the SGA, database files, accounts, security, and so on of each database separately, while system administrators must maintain 100 different computers.


Benefits of the Multitenant Architecture for Database Consolidation
The process of consolidating data from multiple databases into one database on one computer is known as database consolidation. Starting in Oracle Database 12c, the Oracle Multitenant option enables you to consolidate data and code without altering existing schemas or applications.
 
The PDB/non-CDB compatibility guarantee means that a PDB behaves the same as a non-CDB as seen from a client connecting with Oracle Net. The installation scheme for an application back end that runs against a non-CDB runs the same against a PDB and produces the same result. Also, the run-time behavior of client code that connects to the PDB containing the application back end is identical to the behavior of client code that connected to the non-CDB containing this back end.
Operations that act on an entire non-CDB act in the same way on an entire CDB, for example, when using Oracle Data Guard and database backup and recovery. Thus, the users, administrators, and developers of a non-CDB have substantially the same experience after the database has been consolidated.
 
Using the multitenant architecture for database consolidation has the following benefits:
·  Cost reduction:
    By consolidating hardware and sharing database memory and files, you reduce costs for hardware, storage, availability, and labor. For example, 100 PDBs on a single server share one database instance and one set of database files, thereby requiring less hardware and fewer personnel.
 
·    Easier and more rapid movement of data and code:
     By design, you can quickly plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. The implementation technique for plugging and unplugging is similar to the transportable tablespace technique.

·         Easier management and monitoring of the physical database:
       The CDB administrator can attend to one physical database (one set of files and one set of database instances) rather than split attention among dozens or hundreds of non-CDBs. Backup strategies and disaster recovery are simplified.
 
·         Separation of data and code
       Although consolidated into a single physical database, PDBs mimic the behavior of non-CDBs. For example, if user error loses critical data, a PDB administrator can use Oracle Flashback or point-in-time recovery to retrieve the lost data without affecting other PDBs.
 
·        Secure separation of administrative duties:
         A user account is common, which means that it can connect to any container on which it has privileges, or local, which means that it is restricted to a specific PDB. A CDB administrator can use a common user account to manage the CDB. A PDB administrator uses a local account to manage an individual PDB. Because a privilege is contained within the container in which it is granted, a local user on one PDB does not have privileges on other PDBs within the same CDB.

·       Ease of performance tuning:
           It is easier to collect performance metrics for a single database than for multiple databases. It is easier to size one SGA than 100 SGAs.
 
·         Support for Oracle Database Resource Manager
          In a multitenant environment, one concern is contention for system resources among the PDBs running on the same computer. Another concern is limiting resource usage for more consistent, predictable performance. To address such resource contention, usage, and monitoring issues, you can use Oracle Database Resource Manager (see "Database Resource Manager").
 
·       Fewer database patches and upgrades :
     It is easier to apply a patch to one database than to 100 databases, and to upgrade one database than to upgrade 100 databases.