Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 192,973
  • Archives

  • ŗ•ź

  • Categories

  • Subscribe

  • 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.

Posts Tagged ‘Basics’

Using DBCA (DB Create) in Silent Mode.

Posted by FatDBA on August 23, 2013

DBCA is one of the most important and easiest utility to create new databases. Most of us have used it during our careers in GUI mode, but we have an option to launch it in non-graphical mode (Non-Interactive Mode) as well. Below provided steps can be performed to create database in silent/non-interactive mode. So you can launch DBCA in text mode from your favorite ssh client/terminal like putty, secureCRT, SSH Tectia Client etc. and can save yourself from that ‘old’ manual DB create methods (Using catprocs, catalog sql’s and creating new dump directories).

To create database: This requires a response file which includes all necessary details like GGDBNAME (Global Database Name), SID, Listener Name, SYS/SYSTEM/DBSNMP/SYSMAN Passwords etc.
Browser the response file (ORACLE_HOME/assistants/dbca) and make all mandatory changes.

Step 1: Location of response file
[oracle@prashant dbca]$ pwd
/u01/app/oracle/product/11.2.0/db_1/assistants/dbca¬†¬†¬† — Default Location of Response File.

[oracle@prashant dbca]$ ls
dbca.rsp  doc  jlib  logs  templates

Create a copy of original file.
[oracle@prashant dbca]$ cp dbca.rsp  dbcacreatedb.rsp

[oracle@prashant dbca]$ ls
dbcacreatedb.rsp  dbca.rsp  doc  jlib  logs  templates

Step 2:
Edit all mandatory changes in response file.
GDBNAME = “silent”
SID = “silent”
SYSPASSWORD = “oracle90”
SYSTEMPASSWORD = “oracle90”
SYSMANPASSWORD = “oracle90”
DBSNMPPASSWORD = “oracle90”
CHARACTERSET¬†¬† = “US7ASCII” (Default)
NATIONAL CHARACTERSET = “UTF8” (Default)

Although there are various sections inside the response file “.rsp” file like createdatabase, deleteDatabase, createTemplateFromDB or createCloneTemplate. In this post we have talk only about ‘createDatabase‘ and is the area of interest (Used to create new database).

All Examples :
—————————–
1. dbca -progress_only -responseFile <response file>
Display a progress bar depicting progress of database creation
process.

2. dbca -silent -responseFile <response file>
Creates database silently. No user interface is displayed.

3. dbca -silent -createDatabase -cloneTemplate
-responseFile <response file>    
Creates database silently with clone template. The template in
responsefile is a clone template.

4. dbca -silent -deleteDatabase -responseFile <response file>
Deletes database silently.

Step: 3

So, we are going to install the database using ‘STEP 3’ without using -cloneTemplate. This will create database silently and will show progress in percentages.
[oracle@prashant dbca]$ dbca -silent -createDatabase -responseFile dbcacreatedb.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log” for further details.

It seems that Database Creation is successful completed. Let’s check creation¬† log file.
File: “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log”

Okay, while checking log file i found some new entries after creating database:

Database creation complete. For details check the log-files at:
/u01/app/oracle/cfgtoollogs/dbca/silent.
Database Information:
Global Database Name:silent
System Identifier(SID):silent

[oracle@prashant dbca]$ cd /u01/app/oracle/cfgtoollogs/dbca/silent
[oracle@prashant silent]$ ls -ltr
total 104
-rw-r—– 1 oracle oinstall¬† 1372 Aug 23 11:47 rmanRestoreDatafiles.sql
-rw-r—– 1 oracle oinstall¬†¬† 330 Aug 23 11:49 CloneRmanRestore.log
-rw-r—– 1 oracle oinstall¬† 1135 Aug 23 11:50 cloneDBCreation.log
-rw-r—– 1 oracle oinstall¬†¬†¬†¬† 8 Aug 23 11:50 postScripts.log
-rw-r—– 1 oracle oinstall¬†¬†¬†¬† 6 Aug 23 11:50 lockAccount.log
-rw-r—– 1 oracle oinstall¬†¬† 349 Aug 23 11:51 postDBCreation.log
-rw-r—– 1 oracle oinstall¬†¬† 458 Aug 23 11:51 OraDb11g_home1_silent_creation_checkpoint.xml
-rw-r—– 1 oracle oinstall 66392 Aug 23 11:51 trace.log
-rw-r—– 1 oracle oinstall¬†¬† 654 Aug 23 11:51 silent.log

* There are some interesting files generated during DB creation like CloneRmanRestore.log, cloneDBCreation.log, postDBCreation.log and rmanRestoreDatafiles.sql.

Let’s check our new database ‘Silent’.
[oracle@prashant silent]$ ps -ef|grep pmon
oracle   12671     1  0 Aug20 ?        00:00:03 ora_pmon_sairam
oracle   20771     1  0 11:50 ?        00:00:00 ora_pmon_silent
oracle   22315 18308  0 13:06 pts/2    00:00:00 grep pmon

Okay we have a PMON for databse (Silent).
[oracle@prashant silent]$ . oraenv
ORACLE_SID = [sairam] ? silent
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@prashant silent]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 23 13:07:04 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
SILENT    READ WRITE

 

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced, Basics | Tagged: , , | Leave a Comment »

11.2.0.3.4 (Why so many 1’s, 2’s, 3’s and 4’s)

Posted by FatDBA on May 16, 2013

Release_Number

Why we have so many numbers (1,2,3,4) in 11.2.0.3.4 ?. Alright, today I’ll try to explain significance of these numbers and it’s relevance.

11.X .X. X.X
Major Database Release Number:
Major database release number, major new edition, contains significant new functionality. It represents a major new version of the software that contains significant new functionality.

X. 2. X. X
Database Maintenance Release Number:
Number increases when bug fixes or new features to existing programs become available.

X . X .O. X .X
Middleware Release Number:
Release level of Oracle Middleware. In case of 10g/9i it was Oracle Application Server Release Number.

X. X . X. 3. X
Component-Specific/Patch Release Number:
A Patch release contains fixes for serious bugs that cannot wait until the next maintenance releasefor example, component patch sets or interim releases.

X. X . X. X. 4
Platform-Specific Release Number:
Usually this is a patch set Updates.
Used to identify a particular emergency patch release of a software product on that operating system, it usually fixes or works around a particular, critical problem

Views:
SQL> select banner from v$version;
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

Posted in Basics | Tagged: | Leave a Comment »

LMT vs DMT Tablespaces.

Posted by FatDBA on December 26, 2012

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE etr1 DATAFILE ‘/oradata/etr1_01.dbf’ SIZE 80M
EXTENT MANAGEMENT DICTIONARY
Using LMT, each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.

SQL> CREATE TABLESPACE etr2 DATAFILE ‘/oradata/etr2_01.dbf’ SIZE 80M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces

  • Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
  • Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
  • Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
  • Reduced fragmentation

Posted in Basics | Tagged: | Leave a Comment »

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

Posted in Basics | Tagged: | Leave a Comment »

ORA-00265: Cannot set archivelog (FIX)

Posted by FatDBA on August 28, 2012

Today while turning on Archivelog facility on one of my Oracle Database i recieved an error while reads “instance recovery required, cannot set ARCHIVELOG mode” and i find a very unique way to get rid of this problem.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

 

Resolution Steps:
SHUTDOWN -> STARTUP -> SHUTDOWN IMMEDIATE -> STARTUP MOUNT -> alter database archivelog

 

SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             104859096 bytes
Database Buffers          197132288 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size             109053400 bytes
Database Buffers          192937984 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

Posted in Basics | Tagged: , | Leave a Comment »

What are SYS and SYSTEM users ? — What are SYSDBA & SYSOPER Privileges?

Posted by FatDBA on July 10, 2012

What are SYS and SYSTEM users ?

SYS:

‚ÄĘautomatically created when Oracle database is installed
‚ÄĘautomatically granted the DBA role
‚ÄĘhas a default password: CHANGE_ON_INSTALL
‚ÄĘowns the base tables and views for the database data dictionary

SYSTEM:

‚ÄĘautomatically created when Oracle database is installed
‚ÄĘautomatically granted the DBA role
‚ÄĘhas a default password: MANAGER (make sure you change it)

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.
The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to non-administrative users.

What are SYSDBA & SYSOPER Privileges?

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set.
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

Posted in Basics | Tagged: | Leave a Comment »

 
%d bloggers like this: