Locking DML locking: It acquire locks at both the table level and the row level. Row_locking Always – Low level locking – Row lock – Keyword: TX Intent – High level locking – table lock – Keyword: TM Row level lock- DML operations Table level lock – when this a DDL & DML. Table locks do not affect concurrency of DML. Table lock modes Row share lock (RS) — ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. Row exclusive lock (RX) — ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode…
-
-
Oracle Data Guard standby-27
Data Guard It’s a standby database. A copy of primary database. Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Primary and standby will be in sync mode. Standby is used for high availability. It will reduce the recovery and downtime and used for D/R. Technologies in standby data guard Physical standby (Redo apply technologies P–Archive–S) Logical Standby (SQL statement will run) Switchover (Changing) Failover (For server shutdowns) Data guard broker (Third server for witness) Use of broker: Data guard broker will be managed as centralized. The switchover and failover will be automated. We need separate listener for data guard broker. A primary can have…
-
Oracle ASM Automatic Storage Management-26
ASM – Automatic Storage Management It’s an oracle 10g feature. ASM is same as normal database instead of storing a DB files on normal filesystem. It will store a file an ASM managed file system, which will give disk redundancy. It only prevents a disk failure not the instance failure. ASM uses a disk group to store the oracle files. A disk group is a collection of disks. It supports single-instance and (Oracle RAC) configurations. ASM only supports for external disk. External disk should be a raw disk, mostly a RAID. “ ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle…
-
Oracle database Migration and upgrade-25
Migration Migration is a major release change. Ex: 9i to 10g or 11g (OR) 10g to 11g. Migration will be going from lower to higher version. The windows to Linux will be cross platform migration. Migrate from 9i Windows to Linux Pre-requesting Note down the following. Sql> select username,default_tablespace from dba_users; Sql> select * from dba_ts_quotas; Sql> select tablespace_name sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; Step 1: Export Schema D:\> exp system/manager file D:\muthu.dump owner =Muthu log=D:\muthu.log Step 2: Move the dump file to Linux D:\> ftp 192.168.9.1 Login: ftp> cd /opt ftp> lcd D:\ ftp> bin ftp> put Muthu.dump ftp> bye Step 3: Create a same…
-
Oracle database Cloning-24
Cloning Cloning is creating a same database as in different name and the DBID will be different. Use It mostly used to test the application functionality and new implementation. Methods of cloning Cold backup Hot Backup RMAN Backup (duplicated database / active database) Pre- Requirement Create a folder according to the versions. 11g: /opt/oracle (ORACLE_BASE ) –> (Admin , Oradata & diag) Admin –>DBname (adump, create, ddump & pfile) Oradata –> DBnames Diag –> RDMBS–>DBname–>DBname–>Trace (bdump,cdump & udump) All in under trace, but without folders Clod backup Cloning It’s an offline cloning. Go to source database. Pre- requesting: Note down the following. Sql> select name from v$datafile; Sql> select member…