12c Database installation.

Hi Guys,

As everybody knows that Oracle is working hard on cloud computing and after 12c EM, 12c Weblogic…Here is 12c Database and FMW. In this post we will explore 12c Database installation process and top 12 features.

1. I am jumping straight to installation process, reason I think everybody knows about O.S. level setting.

2. Launch the Installer

 

Important points:

  1. You can copy the files location from installer for example you need to execute the scripts as root user , just select the location from installer and ctrl +c and simple click on linux server. It will past it.
  2. You can create container database during installation.
  3. You can register database with EM 12 C during installation.
  4. During database creation process you can see the ‘Activity log’ and ‘Alert log’.

I don’t want email notification.

It is not software update.

 

I want to create and configure oracle database.

Server class

Single instance.

Let’s explore advance installation.

I selected UK English also.

EE

You can choose the right home base and home.

Location of inventory directory.

Select the type of database you want to create.

Make a note there are 3 tabs in this screen.

 

Top 12 features of database 12c.

1.            Pluggable database is a neat feature. Basically, you create a container database (CDB) that contains all of the oracle level data and data dictionary. You then create pluggable databases (PDB) that contain user data and the user portion of the data dictionary. Since the PDB files contain everything about the user data, you can unplug a PDB from a CDB and plug it into a different CDB and be up in seconds. All that needs to happen is a quick data dictionary update in the CDB.

2.            Duplicate Indexes – Create duplicate indexes on the same set of columns. In 11.2 and below, if you try to create an index using the same columns, in the same order, as an existing index, you get an error. In some cases, you might want two different types of index on the same data (such as in a data warehouse where you might want a bitmap index on the leading edge of a set of columns that exists in a BTree index).

3.            Implicit Result Sets – create a procedure, open a ref cursor, return the results. No types, not muss, no mess. Streamlined data access (kind of a catch up to other databases).

4.            PL/SQL Unit Security – A role can now be granted to a code unit. That means you can determine at a very fine grain, who can access a sepcific unit of code.

5.            MapReduce in the Database  – MapReduce can be run from PL/SQL directly in the database. I don’t have much more info than that.

6.            Interval-Ref Partitions – Can now create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type. Ease of use feature.

7.            SQL WITH Clause Enhancement – I want to see some examples of this one. In 12c, you can declare PL/SQL functions in the WITH Clause of a select statement.

8.            Catch up with MySQL – Some catch up features: INDENTITY columns (auto-sequence on a PK), can now use a sequence as a DEFAULT column value, (there’s another that I cannot remember right now).

9.            32k VARCHAR2 Support – Yes, 32k varchar2 in the database. Stored like a CLOB.

10.          Yeah – Booleans in SQL (sort of)  – You can use booleans values in dynamic PL/SQL. Still no booleans as database types.

11.  Execute SQL from RMAN, refresh single datafile to and from standby.

12. Rolling forward synchronization of standby.

July 15, 2017 · Sanjay · No Comments
Posted in: 9. DB 12c

RAC Interview questions

 

RAC Questions and Answers

November 30, 2015 · Sanjay · No Comments
Posted in: 1. DB Admin, 6. RAC, 7. High Availability

RAC Database stop and start steps

 

Steps to Shutdown/Start RAC Database

1. Shutdown Oracle Home process accessing database.
2. Shutdown RAC Database Instances on all nodes.
3. Shutdown All ASM instances from all nodes.
4. Shutdown Node applications running on nodes.
5. Shut down the Oracle Cluster ware or CRS.

Starting steps are from 5 to 1(means which stopped last should be started 1st).

Steps_to_Shutdown_n_Start_RAC

November 29, 2015 · Sanjay · No Comments
Posted in: 1. DB Admin, 2. ASM, 6. RAC, 7. High Availability

Data guard broker commands

$dgmgrl /
$dgmgrl sys/pwd
$dgmgrl sys/pwd@oltp
$dgmgrl sys/test@dgprimary “show database ‘prod'”
$dgmgrl -logfile observer.log / “stop observer”
$dgmgrl -silent sys/test@dgprimary “show configuration verbose”
$dgmgrl / “show configuration verbose”

ADD – Adds a standby database to the broker configuration.
DGMGRL> ADD DATABASE db_name [AS CONNECT IDENTIFIER IS conn_identifier] [MAINTAINED AS {PHYSICAL|LOGICAL}];
DGMGRL> ADD DATABASE ‘testdb’ AS CONNECT IDENTIFIER IS testdb MAINTAINED AS PHYSICAL;
DGMGRL> ADD DATABASE ‘logdb’ AS CONNECT IDENTIFIER IS logdb MAINTAINED AS LOGICAL;
DGMGRL> ADD DATABASE ‘devdb’ AS CONNECT IDENTIFIER IS devdb.foo.com;

CONNECT – Connects to an Oracle database instance.
DGMGRL> CONNECT username/password[@connect_identifier]
DGMGRL> CONNECT /
DGMGRL> CONNECT sys;
DGMGRL> CONNECT sys@test;
DGMGRL> CONNECT sys/pwd;
DGMGRL> CONNECT sys/pwd@dwh;
DGMGRL> CONNECT /@dwh;
$dgmgrl connect sys

CONVERT – Converts a database from one type to another (from Oracle 11g).
DGMGRL> CONVERT DATABASE database_name TO {SNAPSHOT STANDBY|PHYSICAL STANDBY};
DGMGRL> CONVERT DATABASE ‘devdb’ to SNAPSHOT STANDBY;
DGMGRL> CONVERT DATABASE ‘devdb’ to PHYSICAL STANDBY;
Translator How to use twitter Running Shoes Connect Statistics Descriptions Condition

CREATE – Creates a broker configuration.
DGMGRL> CREATE CONFIGURATION config_name AS PRIMARY DATABASE IS db_name CONNECT IDENTIFIER IS conn_ident;
DGMGRL> CREATE CONFIGURATION ‘dg’ AS PRIMARY DATABASE IS ‘prod’ CONNECT IDENTIFIER IS prod.foo.com;
DGMGRL> CREATE CONFIGURATION ‘dg_test’ AS PRIMARY DATABASE IS ‘test’ CONNECT IDENTIFIER IS test;

DISABLE – Disables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> DISABLE CONFIGURATION;
DGMGRL> DISABLE CONFIGURATION;

DGMGRL> DISABLE DATABASE database_name;
DGMGRL> DISABLE DATABASE ‘devdb’;

DGMGRL> DISABLE FAST_START FAILOVER [FORCE | CONDITION condition];
DGMGRL> DISABLE FAST_START FAILOVER;
DGMGRL> DISABLE FAST_START FAILOVER FORCE;
DGMGRL> DISABLE FAST_START FAILOVER CONDITION ‘1578’;

EDIT – Edits a configuration, database, or instance.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS {MaxProtection|MaxAvailability|MaxPerformance};
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

DGMGRL> EDIT CONFIGURATION SET PROPERTY property_name = value;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold = 45;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = FALSE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverAutoReinstate = TRUE;
DGMGRL> EDIT CONFIGURATION SET PROPERTY BYSTANDERSFOLLOWROLECHANGE= ‘NONE’;

DGMGRL> EDIT DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘LogArchiveFormat’=’log_%t_%s_%r_%d.arc’;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=SYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ASYNC;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogXptMode=ARCH;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=OFF;
DGMGRL> EDIT DATABASE devdb SET PROPERTY LogShipping=ON;
DGMGRL> EDIT DATABASE prodb SET PROPERTY LogArchiveTrace=8;
DGMGRL> EDIT DATABASE prodb SET PROPERTY NetTimeout=60;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘ReopenSecs’=300;
DGMGRL> EDIT DATABASE prodb SET PROPERTY ArchiveLagTarget=1200;
DGMGRL> EDIT DATABASE prodb SET PROPERTY FastStartFailoverTarget=’standby_name’;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘StandbyArchiveLocation’=’/oradata/archive/’;
DGMGRL> EDIT DATABASE devdb SET PROPERTY ‘DbFileNameConvert’ = ‘/u01/od01/datafile/, /oradisk/od01/datafile/’;
DGMGRL> EDIT DATABASE testdb SET PROPERTY DelayMins=’720′;
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression =’ENABLE’
DGMGRL> EDIT DATABASE prodb SET PROPERTY RedoCompression =’DISABLE’
DGMGRL> EDIT DATABASE testdb SET PROPERTY LogArchiveMinSucceedDest =1

DGMGRL> EDIT DATABASE database_name RENAME TO new database_name;
DGMGRL> EDIT DATABASE ‘devdbb’ RENAME TO ‘devdb’;

DGMGRL> EDIT DATABASE database_name SET STATE = state [WITH APPLY INSTANCE = instance_name];
DGMGRL> EDIT DATABASE devdb SET STATE=’READ-ONLY’;
DGMGRL> EDIT DATABASE devdb SET STATE=’OFFLINE’;
DGMGRL> EDIT DATABASE devdb SET STATE=’APPLY-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’APPLY-ON’;
DGMGRL> EDIT DATABASE devdb SET STATE=’TRANSPORT-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’TRANSPORT-ON’;
DGMGRL> EDIT DATABASE prodb SET STATE=’LOG-TRANSPORT-OFF’;
DGMGRL> EDIT DATABASE devdb SET STATE=’ONLINE’ WITH APPLY INSTANCE=devdb2;

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET AUTO PFILE [={init_file_path|OFF}];
DGMGRL> EDIT INSTANCE ‘devdb1’ ON DATABASE ‘devdb’ SET AUTO PFILE=’initdevdb1.ora’;

DGMGRL> EDIT INSTANCE instance_name [ON DATABASE database_name] SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE * ON DATABASE database_name SET PROPERTY property_name = value;
DGMGRL> EDIT INSTANCE ‘proddb’ ON DATABASE ‘proddb’ SET PROPERTY ‘StandbyArchiveLocation’=’/oradata/arch/’;
Job interview question Tools New Translator How to use twitter Running Shoes Connect

ENABLE – Enables a configuration, a database, or fast-start failover (FSFO).
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> ENABLE CONFIGURATION;

DGMGRL> ENABLE DATABASE database_name;
DGMGRL> ENABLE DATABASE ‘devdb’;

DGMGRL> ENABLE FAST_START FAILOVER [CONDITION condition];
DGMGRL> ENABLE FAST_START FAILOVER;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION ‘1578’;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION “Stuck Archiver”;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION ‘Corrupted Controlfile’;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION ‘Corrupted Dictionary’;
DGMGRL> ENABLE FAST_START FAILOVER CONDITION ‘Inaccessible Logfile’;

EXIT – Exits the program.
DGMGRL> EXIT;

FAILOVER – Changes a standby database to be the primary database.
DGMGRL> FAILOVER TO standby_database_name [IMMEDIATE]
DGMGRL> FAILOVER TO “testdb”;
DGMGRL> FAILOVER TO “snapdb” IMMEDIATE;

HELP – Displays description and syntax for a command.
DGMGRL> HELP [command];
DGMGRL> HELP REINSTATE
DGMGRL> HELP EDIT

QUIT – Exits the program.
DGMGRL> QUIT;

REINSTATE – Changes a database marked for reinstatement into a viable standby.
DGMGRL> REINSTATE DATABASE database_name;
DGMGRL> REINSTATE DATABASE prim1;

REM – Comment to be ignored by DGMGRL.
DGMGRL> REM [comment];

REMOVE – Removes a configuration, Oracle database, or instance.
DGMGRL> REMOVE CONFIGURATION [PRESERVE DESTINATIONS];
DGMGRL> REMOVE CONFIGURATION;
DGMGRL> REMOVE CONFIGURATION PRESERVE DESTINATIONS;
DGMGRL> REMOVE DATABASE database_name [PRESERVE DESTINATIONS];
DGMGRL> REMOVE DATABASE devdb;
DGMGRL> REMOVE DATABASE standby PRESERVE DESTINATIONS;

DGMGRL> REMOVE INSTANCE instance_name [ON DATABASE database_name];
DGMGRL> REMOVE INSTANCE inst1 ON DATABASE racdb;

SHOW – Displays information about a configuration, database, instance or FSFO.
DGMGRL> SHOW CONFIGURATION [VERBOSE];
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW CONFIGURATION VERBOSE;
Statistics Descriptions Condition Job interview question Tools New Translator
DGMGRL> SHOW DATABASE [VERBOSE] db_name [property_name];
DGMGRL> SHOW DATABASE ‘devdb’;
DGMGRL> SHOW DATABASE VERBOSE ‘test’;
DGMGRL> SHOW DATABASE ‘dwhdb’ ‘StatusReport’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘LogXptStatus’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘InconsistentProperties’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘InconsistentLogXptProps’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘ArchiveLagTarget’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘LogShipping’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘PreferredApplyInstance’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘StatusReport’;
DGMGRL> SHOW DATABASE ‘testdb’ ‘RecvQEntries’;
DGMGRL> SHOW DATABASE ‘proddb’ ‘SendQEntries’;

DGMGRL> SHOW INSTANCE [VERBOSE] instance_name [property_name] [ON DATABASE db_name];
DGMGRL> SHOW INSTANCE inst1;
DGMGRL> SHOW INSTANCE VERBOSE inst3;
DGMGRL> SHOW INSTANCE testdb ‘TopWaitEvents’;

DGMGRL> SHOW FAST_START FAILOVER;
DGMGRL> SHOW FAST_START FAILOVER;

SHUTDOWN – Shuts down a currently running Oracle instance.
DGMGRL> SHUTDOWN [NORMAL | IMMEDIATE | ABORT];
DGMGRL> SHUTDOWN;
DGMGRL> SHUTDOWN NORMAL;
DGMGRL> SHUT IMMEDIATE;
DGMGRL> SHUT ABORT;

SQL – Executes a SQL statement
DGMGRL> SQL “sql_statement”;

START – Starts the fast-start failover(FSFO) observer.
DGMGRL> START OBSERVER [FILE=observer_configuration_file];
DGMGRL> START OBSERVER;

STARTUP – Starts an Oracle database instance.
DGMGRL> STARTUP [RESTRICT] [FORCE] [PFILE=filespec]
[NOMOUNT | MOUNT | OPEN [READ ONLY|READ WRITE]];
DGMGRL> STARTUP;
DGMGRL> STARTUP NOMOUNT;
DGMGRL> STARTUP MOUNT;
DGMGRL> STARTUP OPEN;
DGMGRL> STARTUP FORCE;
DGMGRL> STARTUP FORCE RESTRICT NOMOUNT;
DGMGRL> STARTUP PFILE=initdwh.ora NOMOUNT;

STOP – Stops the fast-start failover(FSFO) observer.
DGMGRL> STOP OBSERVER;

SWITCHOVER – Switches roles between a primary and standby database.
DGMGRL> SWITCHOVER TO standby_database_name;
DGMGRL> SWITCHOVER TO “standby”;
How to use twitter Running Shoes Connect Statistics Descriptions Condition
VALIDATE – command to checks whether the database is ready for a role transition or not.

DGMGRL> VALIDATE DATABASE …; — From Oracle Database 12c
$BDUMP/drc*.log
$ORACLE_HOME/rdbms/log/drc*.log

alter system set dg_broker_start=false;
alter system set dg_broker_start=false sid=’*’;
alter system set dg_broker_start=FALSE SCOPE=spfile SID=’*’;
alter system set dg_broker_start=true;
alter system set dg_broker_start=true sid=’*’;
alter system set dg_broker_start=TRUE SCOPE=spfile SID=’*’;

alter system set dg_broker_config_file1=’/u01/dg_broker_config_files/dr1TESTP.dat’ sid=’*’;
alter system set dg_broker_config_file2=’/u01/dg_broker_config_files/dr2TESTP.dat’ sid=’*’;

November 29, 2015 · Sanjay · No Comments
Posted in: 6. RAC, 7. High Availability