The validate command can be used to detect corrupt blocks and missing files, also to determine whether backup set can be restored. The validate command can check for physical and logical corruptions in database files. The following command will validate and check all datafiles along with archivelogs, current controlfile and spfile for any kind of corruption.
Monday, January 30, 2012
Oracle 11g RMAN Validate command.
Oracle Database 11g Workload Replay.
Database replay feature can be used to capture workload from the production system, and replay it on a test system, with exact timing and concurrency. Its like you can emulate the transaction workload on the test system as it was happening on the production system. Main benefit of this is that you can test new settings on the test system before implementing them on the production system.
Oracle 11g Health Checks.
Oracle 11g now has a feature called health monitor which can be used
to run diagnostic checks on the database.
Health check can be run in two ways.
1) Automatic - The fault diagnosable infrastructure runs the checks
when a critical error is reported.
2) Manual - Can be done manually either through enterprise manager
or through DBMS_HM PL/SQL package.
Oracle 11g Data Recovery Advisor.
The data recovery advisor automatically detects corruption or loss of data on disk and also provides advisory and execution functions for repair or recovery of lost data. Example: I lost my Users Tablespace datafile for some reason.
Tuesday, January 24, 2012
Oracle 10g Using RMAN to Roll Forward a Physical Standby Database.
On Standby
SQL>alter database recover managed standby database cancel;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
485739
Oracle 11gR2 Table level compression.
Earlier in oracle 10g we had compression at the table level but it was only for bulk load operations. But with Oracle 11gR2 compression is also available at the OLTP level.
Oracle Segment Advisor 10g.
Segment advisor which is available from oracle 10g onwards helps in detecting segments that have space issues. For example if a table had lots of rows earlier but now it has few rows then it may be hording up space because of its high water mark. Segment advisor helps in finding following types of segments.
Oracle Database Row Chaining Solution.
Move the affected table 1) alter table hr.employees move; Rebuild the indexes on that table because they will become invalid because of move operation.
Oracle 10g Free up Unused Table Space.
The following operations will shrink the table and reset the high water mark.
Excessive Redo Generation During Begin Backup Mode.
Excessive Redo Generation During Hot/Online backup. During hot backup there is no excessive redo generation, only some additional information is generated. The process is something like following. 1) Before backup, tablespace or database is put into begin backup mode.
Oracle 10g Enable Read Write On Physical Standby Database.
On Standby Enable Flashback Database. SQL> show parameter db_recovery NAME TYPE VALUE ---------------------- ----------- ----------------------------------- db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big integer 2G
Oracle Database Manual Backup and Recovery.
COLD BACKUP AND RECOVERY SCENARIOS. Cold backup of Database in Noarchivelog Mode. 1) analyze the space requirements. SQL> select sum(sum_bytes)/1024/11024 m_bytes 2 from ( 3 select sum(bytes) sum_bytes from v$datafile 4 union 5 select sum(bytes) sum_bytes from v$tempfile 6 union 7 select (sum(bytes) * members) sum_bytes from v$log 8 group by members);
Monday, January 23, 2012
Wednesday, January 18, 2012
Oracle Database User Management.
USER CREATION AND DELETION. SQL> create user tom identified by jerry 2 default tablespace users 3 temporary tablespace temp 4 quota 20m on users; User created.
Oracle Fuzzy Bits.
If there are FUZZY BITS in the datafile header then that means there may have been writes on the datafile after the last checkpoint. So, whenever a datafile is opened it is checked for fuzzy bits. If it is fuzzy then the database checks the checkpoint_change$ column to search the SCN from which the redo logs are to be applied.
Opening a Standby Database For Read Only Access.
Open a standby database for read-only access when
it is currently performing redo apply. SQL> alter database recover managed standby database cancel; Database altered.
Gap Detection on Standby Database.
If the primary and standby databases are configured with
FAL_SERVER and FAL_CLIENT
parameters then gaps should not occur.
But still if the gaps occur do the following.
On the standby database.
SQL> select * from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
------- -------------- ---------------
1 30 40
Oracle 10g Dataguard Failover.
On standby database. Defer the remote archival destination, since we are not going to use it. SQL> alter system set log_archive_dest_state_2 = 'DEFER'; System altered. Before performing going any furthur transfer all the unapplied redo data from the primary to standby to reduce data loss.
Oracle 10g Dataguard Switchover.
This article is based on the dataguard tutorial written earlier. Configure the follwoing parameters on both the databases. SQL> show parameter fal_server
Friday, January 13, 2012
Oracle Database Online Redo Log Files Management.
Lets check the online redo log configuration.
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;
Thursday, January 12, 2012
A Brief Introduction to Materialized Views.
The Following tutorial is prepared on Oracle Database 11gR2. Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database. After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.
Wednesday, January 11, 2012
Oracle Database Undo Tablespace Mangement.
Creating undo tablespace.
SQL> create undo tablespace undotbs2
2 datafile '/u01/app/oracle/oradata/orcl
/undotbs201.dbf' size 200m
3 autoextend on
4 extent management local;
Oracle Database Temporary Tablespace Management.
To check for all the default tablespaces in your database execute the following query. SQL> column property_name format a30 SQL> column property_value format a30 SQL> column description format a50
Tuesday, January 10, 2012
Oracle 11g Feature Read Only Tables.
In oracle database 11g you can alter individual tables to be read only. When in read-only mode, you can’t issue any DML statements against the table. SQL>alter table emp read only; Table altered.
Sunday, January 8, 2012
Oracle 10g Dataguard Physical Standby.
This tutorial is for configuring physical standby database. The dataguard type which we are configuring here is "Maximum Performance Mode". Primary Machine IP: 192.168.1.160 Database name:orcl
Monday, January 2, 2012
Oracle 11gR2 Compression feature.
In Oracle 10g we had basic compression in RMAN. But Oracle 11gR2 has added some more compression features. There are basically four compression levels. HIGH = unmodified BZIP2 MEDIUM = ZLIB LOW = LZO BASIC = BZIP2
Oracle 11g Feature Multisection Backup.
With this new feature RMAN backups can be split into multiple pieces. Suppose you want to backup your system tablespace and spread the backups to three different backup locations. You can do so through the following query.
Subscribe to:
Posts (Atom)