Physical Database files: Password file It’s a physical file. “ORAPWD” is a command utility to create the file. To connect the oracle database as DBA, either by password file or OS group which has SYSDBA access. It assists the DBA with granting SYSDBA and SYSOPER privileges to other users. The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used. Sql> show parameter password SQL> select * from v$pwfile_users; The only time the password file is accessed is if someone logs on ‘as sysdba’. orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret Parameter files It’s…
-
-
SQL server Oracle difference concepts architecture-06
SQL oracle difference concepts architecture In this post, I am just referring similar to the concept to understand better, but it is not exactly same as what I have referred. Oracle SQL Comments System Master SYSAUX Resource Model MSDB temporary TempDB More than one & User can have their own There is no TempDB and it doesn’t need to re-create DESCRIBE sp_help dba_views Identity Sequence Auto generate number DBA_SCHEDULER_JOBS Sys.sysjobs DBMS_SCHEDULER / OEM-Oracle Scheduler SQL Agent SMON Recovery PMON User kill/rollback show parameter sp_configure Pfile & Spfile SQL server property you can change system and session level Data file Data file ARCHIVELOG mode Full mode NOARCHIVELOG mode Simple mode Default…
-
Oracle Tablespace-05
Tablespace: A database is divided into one or more logical storage units called tablespaces. All tablespaces, including the SYSTEM tablespace, can be locally managed. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7116328455352 http://www.orafaq.com/node/3. Types: 1.System tablespace 2. Non System tablespace System SysAUX Undo Temporary — A different block size object cannot move between the tablespace. — All the data file name should be created as unique. Since, it might have some issues in cloning a database. TEMPFILEs are not fully initialized (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database…
-
Oracle Database Physical and logical structures-04
Database Physical and logical structures Block Smallest storage unit called a block. Size can be {2k,4k,8k,16k & 32k} – Default 8k. Standard block and Nonstandard block. Default is standard block. Db_block_size =8192. Once it’s configured we cannot change this. Each block has pctused, pctfree & high water mark. PCTused – For insert – 40% PCTfree – For Update – 10% High Water Mark – Separate the used & free space of block Multiple block size (9i onwards) You can configure the size of the block for the database. Db_nk_cache_size = nk Db_16k_cache_size =16k Block utilization parameter: ================ Initran 1 Initial transaction Maxtran 256 – maximum transaction Pctused – inserted…
-
Oracle Architecture-03
Oracle 11g Architecture Oracle – PGA+ Instance + DB files Instance – SGA + BG Instance —SGA and BG SGA — Shared Pool, Database buffer cache, Redo log buffer cache, large pool, Java pool & Streams pool. Shared Pool — Most recently executed statements and definitions. Data Dictionary cache & Library cache Data Dictionary — most recently used definitions, including table, index & privilege. Library cache — most recently used SQL & PL/SQL statements Shared SQL area & Shared PL/SQL area Database buffer pool — It stores a copy of data block. Default, keep & Recycle Redo log buffer pool — it stores all the changes made to the…