Memory UsageV$DB_OBJECT_CACHEThis view provides object level statistics for objects in the library cache (shared pool). This view provides more details than V$LIBRARYCACHE and is useful for finding activeobjects in the shared pool. Useful Columns for V$DB_OBJECT_CACHEMost of the columns of this table provide current state information.
QuickSql: --generate sql to pin objects in the shared_pool which are not currently pinned. select 'exec DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run from V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50; SQL_TO_RUN --------------exec dbms_shared_pool.keep('SYS.DBMS_JAVA','P'); exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT','P'); exec dbms_shared_pool.keep('SYS.DBMS_PIPE','P'); exec dbms_shared_pool.keep('SYS.DBMS_REGISTRY','P'); exec dbms_shared_pool.keep('SYS.DBMS_RLS','P'); exec dbms_shared_pool.keep('SYS.OWA_MATCH','P'); exec dbms_shared_pool.keep('SYS.OWA_SEC','P'); exec dbms_shared_pool.keep('SYS.OWA_UTIL','P'); exec dbms_shared_pool.keep('SYS.PLITBLM','P'); exec dbms_shared_pool.keep('SYS.STANDARD','P'); exec dbms_shared_pool.keep('SYS.SYSEVENT','P'); --show distribution of shared pool memory across different types of objects.--show if any of the objects have been pinned using the procedure DBMS_SHARED_POOL.KEEP(). col type for a20 col kept for a4 select type,count(*),kept,round(SUM(sharable_mem)/1024,0) share_mem_kilo from V$DB_OBJECT_CACHE where sharable_mem != 0 GROUP BY type, kept order by 3,4; TYPE COUNT(*) KEPT SHARE_MEM_KILO -------------------- ---------- ---- -------------- APP CONTEXT 1 NO 1 SEQUENCE 2 NO 3 NON-EXISTENT 3 NO 3 PIPE 5 NO 6 PUB_SUB 5 NO 8 TRIGGER 4 NO 14 FUNCTION 4 NO 21 SYNONYM 12 NO 56 VIEW 29 NO 66 TABLE 78 NO 161 PACKAGE BODY 11 NO 166 PACKAGE 12 NO 623 CURSOR 42270 NO 332424 INDEX 4 YES 5 CLUSTER 6 YES 12 TABLE 20 YES 43 --find objects with large number of loads col name for a80 trunc SELECT owner,sharable_mem,kept,loads,name from V$DB_OBJECT_CACHE WHERE loads > 2 ORDER BY loads DESC; OWNER SHARABLE_MEM KEP LOADS NAME -------------------- ------------ --- ---------- ---------------------------------------- SYS 29304 NO 89 DBMS_SESSION GENERAL 2567 NO 84 GJBPRUN BANSECR 22471 NO 78 G$_SECURITY_PKG BANINST1 27690 NO 66 GB_COMMON BANINST1 27005 NO 61 GB_MESSAGING SYS 16496 NO 61 DUAL GENERAL 2127 NO 52 GUBINST BANSECR 22464 NO 48 G$_VPDI_SECURITY--find objects using large amounts of memory. pin using DBMS_SHARED_POOL.KEEP( ). --sharable memory in shared pool consumed by the object col name for a40 col type for a30 select OWNER,NAME,TYPE,SHARABLE_MEM from V$DB_OBJECT_CACHE where SHARABLE_MEM > 10000 and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') order by SHARABLE_MEM desc; OWNER NAME TYPE SHARABLE_MEM ---------- ---------------------------------------- ------------------------------ ------------ SYS STANDARD PACKAGE 499812 SYS DBMS_JAVA PACKAGE 82685 SYS OWA_UTIL PACKAGE BODY 66732 BANINST1 BWCKFRMT PACKAGE BODY 62009 BANINST1 RB_AWARD_DISBURSEMENT PACKAGE BODY 60606 WTAILOR TWBKBSSF PACKAGE BODY 35152 --determine which objects to pin execute when database is in steady state. set linesize 150 col Oname for a40 col owner for a15 col Type for a20 SELECT owner||'.'||name Oname,substr(type,1,12) "Type", sharable_mem "Size",executions,loads, kept FROM V$DB_OBJECT_CACHE WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') AND executions > 0 ORDER BY executions desc,loads desc, sharable_mem desc; ONAME Type Size EXECUTIONS LOADS KEP ---------------------------------------- -------------------- ---------- ---------- ---------- --- BANINST1.DML_COMMON PACKAGE BODY 7651 1361387 1 NO SYS.STANDARD PACKAGE BODY 32684 994931 1 NO SYS.PLITBLM PACKAGE 7971 742622 6 NO BANSECR.G$_VPDI_SECURITY PACKAGE BODY 9472 339687 1 NO BANINST1.ROKLOGS PACKAGE BODY 10568 78872 1 NO BANINST1.GB_COMMON PACKAGE BODY 15978 45413 8 NO SYS.DBMS_STANDARD PACKAGE 41969 40198 44 NO BANSECR.G$_SECURITY_PKG PACKAGE BODY 26807 37695 11 NO BANINST1.GB_MESSAGING PACKAGE BODY 14053 24138 8 NO SYS.DBMS_SESSION PACKAGE BODY 10472 14484 8 NO SYS.DBMS_PIPE PACKAGE BODY 8229 11156 1 NO BANINST1.ROKPVAL PACKAGE BODY 95696 10021 1 NO SYS.DBMS_APPLICATION_INFO PACKAGE BODY 4641 9568 10 NO WTAILOR.TWBKBSSF PACKAGE BODY 35152 7820 1 NO SYS.HTP PACKAGE BODY 24679 7108 1 NO BANINST1.RB_AWARD_DISBURSEMENT PACKAGE BODY 60606 6122 1 NO --list large, un-pinned objects. set linesize 150 col sz for a10 col name for a100 col keeped for a6 select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes ','') keeped,owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,null extra, 0 iscur from v$db_object_cache v where sharable_mem > 1024 * 1000; --list large, un-pinned procedures, packages, functions. col type for a25 col name for a40 col owner for a25 select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from v$db_object_cache where kept = 'NO' and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')group by owner,name,type order by 4;OWNER NAME TYPE SHARABLE_MEM_K ------------------------- ---------------------------------------- ------------------------- -------------- SYS DICTIONARY_OBJ_NAME FUNCTION 16.1 SYS DICTIONARY_OBJ_TYPE FUNCTION 16.2 SYS SYSEVENT FUNCTION 16.6 SYS DBMS_APPLICATION_INFO PACKAGE 20.5 SYS DBMS_OUTPUT PACKAGE 21.2 SYS DBMS_STANDARD PACKAGE 36.8 SYS STANDARD PACKAGE 428.2 |
Thursday, 25 August 2011
SHARED POOL MEMORY USAGE in bytes
Saturday, 6 August 2011
DBMS_REPAIR example
Refrence from Metelink Doc [ID 68013.1]
Checked for relevance on 12-SEP-2010 PURPOSE This document provides an example of DBMS_REPAIR as introduced in Oracle 8i. Oracle provides different methods for detecting and correcting data block corruption - DBMS_REPAIR is one option. WARNING: Any corruption that involves the loss of data requires analysis to understand how that data fits into the overall database system. Depending on the nature of the repair, you may lose data and logical inconsistencies can be introduced; therefore you need to carefully weigh the gains and losses associated with using DBMS_REPAIR. SCOPE & APPLICATION This article is intended to assist an experienced DBA working with an Oracle Worldwide Support analyst only. This article does not contain general information regarding the DBMS_REPAIR package, rather it is designed to provide sample code that can be customized by the user (with the assistance of an Oracle support analyst) to address database corruption. The "Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i Administrator's Guide should be read and risk assessment analyzed prior to proceeding. RELATED DOCUMENTS Oracle 8i Administrator's Guide, DBMS_REPAIR Chapter Introduction============= Note: The DBMS_REPAIR package is used to work with corruption in thetransaction layer and the data layer only (software corrupt blocks).Blocks with physical corruption (ex. fractured block) are marked asthe block is read into the buffer cache and DBMS_REPAIR ignores allblocks marked corrupt. The only block repair in the initial release of DBMS_REPAIR is to *** mark the block software corrupt ***. A backup of the file(s) with corruption should be made before using package. Database Summary=============== A corrupt block exists in table T1. SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 NOT NULL NUMBER(38) COL2 CHAR(512) SQL> analyze table t1 validate structure;analyze table t1 validate structure*ERROR at line 1:ORA-01498: block check failure - see trace file ---> Note: In the trace file produced from the ANALYZE, it can be determined--- that the corrupt block contains 3 rows of data (nrows = 3).--- The leading lines of the trace file follows: Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trcOracle8 Enterprise With the Partitioning option *** 1998.12.16.15.53.02.000*** SESSION ID:(7.6) 1998.12.16.15.53.02.000kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=32 ktbbhitc=1Block header dump: 0x01800003 Object id on Block? Y seg/obj: 0xb6d csc: 0x00.1cf5f itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01 Itl Xid Uba Flag Lck Scn/Fsc0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc 0x0000.0001cf60 data_block_dump===============tsiz: 0x7b8hsiz: 0x18pbl: 0x28088044bdba: 0x01800003flag=-----------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x19davsp=0x185tosp=0x1850xe:pti[0] nrow=3 offs=00x12:pri[0] offs=0x5ff0x14:pri[1] offs=0x3a60x16:pri[2] offs=0x19dblock_row_dump: [... remainder of file not included] end_of_block_dump Recovering Datafiles in ARCHIVELOG Mode
Recovering Database when the database is running in ARCHIVELOG Mode.
Recovering from the lost of Damaged Datafile.
If you have lost one datafile. Then follow the steps shown below.
STEP 1. Shutdown the Database if it is running.
STEP 2. Restore the datafile from most recent backup.
STEP 3. Then Start sqlplus and connect as SYSDBA.
$sqlplus
Enter User:/ as sysdba
SQL>Startup mount;
SQL>Set autorecovery on;
SQL>alter database recover;
STEP 4. Now open the database
SQL>alter database open;
Upon Startup DB Instance one of Datafile is missing or corrupted
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state
S tep 1 Copy the missing Datafile from last taken Backup.
Note : Here you need all the archive log files after last taken backup
Step 2 sql> recover datafile 4.
When Media recovery completed messages shown you can open the database
Step 3 sql> alter database open.
Time Based Recovery (INCOMPLETE RECOVERY).
Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.
You have taken a full backup of the database on Monday night and the table was created on Tuesday and thousands of rows were inserted into it. Some user accidentally drop the table on Thursday and nobody notice this until Saturday.
Now to recover the table follow these steps.
STEP 1. Shutdown the database and take a full offline backup.
STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.
STEP 3. Start SQLPLUS and start and mount the database.
STEP 4. Then give the following command to recover database until specified time.
SQL> recover database until time '2011:08:16:13:55:00'
STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this
SQL> alter database open resetlogs;
STEP 6. After database is open. Export the table to a dump file using Export Utility.
STEP 7. Restore from the full database backup which you have taken before this activity.
STEP 8. Open the database and Import the table.
RECOVERING THE DATABASE IN NOARCHIVELOG MODE.
Option 1: When you don’t have a backup.
If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.
The following are the steps to drop a damaged datafile and open the database.
(UNIX)
STEP 1: First take full backup of database for safety.
STEP 2: Start the sqlplus and give the following commands.
$sqlplus
Enter User:/ as sysdba
SQL> STARTUP MOUNT
SQL> ALTER DATABASE DATAFILE '/u01/ica
SQL>alter database open;
Option 2: When you have the Backup.
If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.
1. Either you can drop the damaged datafile, if it does not contain important information which you can afford to loose.
2 . Or you can restore from full cold backup. You will loose all the changes made to the database since last full backup.
STEP 1: Take a full backup of current database.
STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.
(UNIX)
Suppose the backup is in "/u2/oracle/backup" directory. Then do the following.
sqlplus>Shutdown Abort
$cp /u02/backup/* /u01/ica
Note: This will copy all the files from backup directory to original destination. Also remember to copy the control files and redologfiles to all the mirrored locations.
sqlplus> startup
How to take user managed Database Backups
TAKING OFFLINE BACKUPS. ( UNIX )
Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.
$sqlplus
SQL> connect / as sysdba
SQL> Shutdown immediate
SQL> Exit
After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.
TIP:
To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
Lets suppose all the files are in "/u01/ica
$cd /u01/ica
$cp * /u02/backup/
Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.
TAKING ONLINE (HOT) BACKUPS.(UNIX)
To take online backups the database should be running in Archivelog mode. To check whether the database is running in Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.
After connecting give the command "archive log list" this will show you the status of archiving.
$sqlplus
Enter User:/ as sysdba
SQL> ARCHIVE LOG LIST
If the database is running in archive log mode then you can take online backups.
Let us suppose we want to take online backup of "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is
"/u01/ica/usr1.dbf ".
Give the following series of commands to take online backup of USERS tablespace.
$sqlplus
Enter User:/ as sysdba
SQL> alter tablespace users begin backup;
SQL> host cp /u01/ica/usr1.dbf /u02/backup
SQL> alter tablespace users end backup;
SQL> exit;
ALTER DATABASE BEGIN BACKUP ON OPEN MODE
AS SYSDBA
sql>alter database begin backup;
Database altered.
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
NOTE: All the datafiles are in backup mode, now you can copy your all datafiles to backup location.
sql>alter database end backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
NOTE: All the datafiles are in backup mode, now you can copy your all datafiles to backup location.
sql>alter database end backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
Bringing the database in Archivelog /No Archive Mode
Opening or Bringing the database in Archivelog mode.
To open the database in Archive log mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Take a full offline backup.
STEP 3: Set the following parameters in parameter file.
LOG_ARCHIVE_FORMAT=ica
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
If you want you can specify second destination also
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”
Step 3: Start and mount the database.
SQL> STARTUP MOUNT
STEP 4: Give the following command
SQL> ALTER DATABASE ARCHIVELOG;
STEP 5: Then type the following to confirm.
SQL> ARCHIVE LOG LIST;
STEP 6: Now open the database
SQL>alter database open;
Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.
To again bring back the database in NOARCHIVELOG mode.
STEP 1: Shutdown the database if it is running.
STEP 2: Comment the following parameters in parameter file by putting " # " .
# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
# LOG_ARCHIVE_FORMAT=ica
STEP 4: Give the following Commands
SQL> ALTER DATABASE NOARCHIVELOG;
STEP 5: Shutdown the database and take full offline backup.
Flashback Of DATABASE/TABLE with Normal Restore Point
Flashback Of DATABASE with Normal Restore Point
Flashback Database enables you to rewind your entire database backward in time, reversing the effects of unwanted database
changes within a given time window. The effects are similar to database point-in-time recovery.
Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus
(by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.
About Normal Restore Points
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
Before performing any operation that you may have to reverse, you can create a normal restore point. The name of the restore point and the SCN are recorded in the control file. Then, if you later need to use Flashback Database, Flashback Table, or point-in-time recovery,
you can refer to the target time using the name of the restore point instead of a time expression or SCN. Defining a normal restore point before an operation to be reversed later eliminates the need to manually record an SCN in advance, or investigate the correct SCN after the fact using features such as Flashback Query.
Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.
Commands Supporting the Use of Restore Points
Restore points can be used to specify the target SCN in the following contexts:
The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN
The FLASHBACK TABLE statement in SQL*Plus
==================== PRACTICAL EXAMPLE ========================
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> alter database open;
Database altered.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\product\10.2.0\db_1\RDBMS
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;
System altered.
SQL> alter system set db_recovery_file_dest_size=2000M;
System altered.
SQL> alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
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 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL> alter system switch logfile;
System altered.
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
SQL>
SQL> create table scott.sales as select * from sh.sales;
Table created.
SQL>
SQL> create restore point b4_change;
Restore point created.
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------ ----------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM 2 NO 0
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> drop table emp;
Table dropped.
SQL> truncate table sales;
Table truncated.
SQL> select count(*) from sales;
COUNT(*)
----------
0
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> flashback database to restore point b4_change;
flashback database to restore point b4_change
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL>
SQL>
SQL> flashback database to restore point b4_change;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from sales;
COUNT(*)
----------
918843
SQL> conn / as sysdba
Connected.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
NAME SCN TIME GUARANTEE_FLASHBACK_DATABASE
---------- -------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM NO
SQL> drop restore point b4_change;
Restore point dropped.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
no rows selected
Flashback Of DATABASE with Normal Restore Point via RMAN
SQL> create restore point b4_change ;
Restore point created.
SQL> truncate table scott.sales;
Table truncated.
Now open new command prompt window
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 5 23:47:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1285180341)
RMAN>
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
RMAN> flashback database to restore point b4_change;
Starting flashback at 05-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 05-AUG-11
RMAN> alter database open resetlogs;
database opened
RMAN>exit
Flashback Of TABLE with Normal Restore Point
SQL> create restore point b4_delrec;
Restore point created.
SQL> alter table scott.sales enable row movement;
Table altered.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL> delete from scott.sales where rownum < 100;
99 rows deleted.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918744
SQL> commit;
Commit complete.
SQL> flashback table scott.sales to restore point b4_delrec;
Flashback complete.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL>
Flashback Database enables you to rewind your entire database backward in time, reversing the effects of unwanted database
changes within a given time window. The effects are similar to database point-in-time recovery.
Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus
(by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.
About Normal Restore Points
Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.
Before performing any operation that you may have to reverse, you can create a normal restore point. The name of the restore point and the SCN are recorded in the control file. Then, if you later need to use Flashback Database, Flashback Table, or point-in-time recovery,
you can refer to the target time using the name of the restore point instead of a time expression or SCN. Defining a normal restore point before an operation to be reversed later eliminates the need to manually record an SCN in advance, or investigate the correct SCN after the fact using features such as Flashback Query.
Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.
Commands Supporting the Use of Restore Points
Restore points can be used to specify the target SCN in the following contexts:
The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN
The FLASHBACK TABLE statement in SQL*Plus
==================== PRACTICAL EXAMPLE ========================
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> alter database open;
Database altered.
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\product\10.2.0\db_1\RDBMS
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' ;
System altered.
SQL> alter system set db_recovery_file_dest_size=2000M;
System altered.
SQL> alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
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 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL> alter system switch logfile;
System altered.
SQL> show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
SQL>
SQL> create table scott.sales as select * from sh.sales;
Table created.
SQL>
SQL> create restore point b4_change;
Restore point created.
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE
--------------------- --- ------------ ----------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM 2 NO 0
SQL> alter user scott identified by tiger account unlock;
User altered.
SQL> conn scott/tiger
Connected.
SQL> drop table emp;
Table dropped.
SQL> truncate table sales;
Table truncated.
SQL> select count(*) from sales;
COUNT(*)
----------
0
SQL> select count(*) from emp;
select count(*) from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> flashback database to restore point b4_change;
flashback database to restore point b4_change
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
Database mounted.
SQL>
SQL>
SQL> flashback database to restore point b4_change;
Flashback complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from sales;
COUNT(*)
----------
918843
SQL> conn / as sysdba
Connected.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
NAME SCN TIME GUARANTEE_FLASHBACK_DATABASE
---------- -------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM NO
SQL> drop restore point b4_change;
Restore point dropped.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
no rows selected
Flashback Of DATABASE with Normal Restore Point via RMAN
SQL> create restore point b4_change ;
Restore point created.
SQL> truncate table scott.sales;
Table truncated.
Now open new command prompt window
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 5 23:47:08 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1285180341)
RMAN>
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1367343104 bytes
Fixed Size 1302492 bytes
Variable Size 335544356 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7086080 bytes
RMAN> flashback database to restore point b4_change;
Starting flashback at 05-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 05-AUG-11
RMAN> alter database open resetlogs;
database opened
RMAN>exit
Flashback Of TABLE with Normal Restore Point
SQL> create restore point b4_delrec;
Restore point created.
SQL> alter table scott.sales enable row movement;
Table altered.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL> delete from scott.sales where rownum < 100;
99 rows deleted.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918744
SQL> commit;
Commit complete.
SQL> flashback table scott.sales to restore point b4_delrec;
Flashback complete.
SQL> select count(*) from scott.sales;
COUNT(*)
----------
918843
SQL>
Tuesday, 2 August 2011
How To Drop/Create EM dbconsole of Single Instance Database
Monday, 1 August 2011
EM Database Control -Recovering From Errors Due to CA Expiry on Oracle DB 10.2.0.4
Purpose What is the Issue? Scope and Application Who is Affected? Enterprise Manager Database Control Configuration - Recovering From Errors Due to CA Expiry on Oracle Database 10.2.0.4 or 10.2.0.5 [Video] What Happens During Database Control Configuration Failure? Recovering from Configuration Errors on a Single Instance Database Recovering from Configuration Errors in an Oracle Real Application Clusters (RAC) Environment References Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 10.2.0.5 - Release: 10.2 to 10.2Oracle Database Configuration Assistant - Version: 10.2.0.4 to 10.2.0.5 [Release: 10.2 to 10.2] Information in this document applies to any platform. Enterprise Manager Database Control 10.2.0.4 and 10.2.0.5 PurposeWhat is the Issue?In Enterprise Manager Database Control with Oracle Database 10.2.0.4 and 10.2.0.5, the root certificate used to secure communications via the Secure Socket Layer (SSL) protocol will expire on 31-Dec-2010 00:00:00. The certificate expiration will cause errors if you attempt to configure Database Control on or after 31-Dec-2010. Existing Database Control configurations are not impacted by this issue.If you plan to configure Database Control with either of these Oracle Database releases, Oracle strongly recommends that you apply Patch 8350262 to your Oracle Home installations before you configure Database Control. Configuration of Database Control is typically done when you create or upgrade Oracle Database, or if you run Enterprise Manager Configuration Assistant (EMCA) in standalone mode. Note the following:
Note: If you apply Patch 8350262 to your Oracle Home installations before you configure Database Control, you will not need to follow the recovery steps outlined in this document. Scope and ApplicationWho is Affected?If you did not apply Patch 8350262 before configuring Database Control, you will encounter errors during the Database Control configuration process on or after 31-Dec-2010 under the following conditions:
Enterprise Manager Database Control Configuration - Recovering From Errors Due to CA Expiry onOracle Database 10.2.0.4 or 10.2.0.5 [Video] PATCH:8350262 -CREATE DBCONSOLE CERT WITH 10YEAR VALIDITY NOTE:1217493.1 -ATTENTION - Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5 - Patch Required from 31-Dec-2010 onwardsWhat Happens During Database Control Configuration Failure?Database Configuration Assistant (DBCA) and Database Upgrade Assistant (DBUA) ErrorsDatabase Configuration Assistant (DBCA) and Database Upgrade Assistant (DBUA) will report the following error in the console: Could not complete the Enterprise Manager configuration.Enterprise Manager Configuration Assistant (EMCA) ErrorsEnterprise Manager Configuration Assistant (EMCA) will write errors similar to those below to the emca.log file:The EMCA console will display output similar to the following: aime@myhost09 db_1]$ bin/emca -config dbcontrol db -repos recreate -clusterChecking the ORACLE_HOME\<hostname>_<SID>\sysman\log\emagent.trc, one can see also: 2011-01-09 09:36:56 Thread-51125136 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://myhost:1158/em/upload/: retStatus=-1Also, the following errors has been reported in some cases: 2011-01-06 18:50:54 Thread-3393 ERROR ssl: nzos_Initialize failed, ret = 43061At the end of the database installation on non-Windows platforms, both Database Control and the Management Agent will be up and running, even though the status of both components will be shown as not running, because EMCTL will be unable to connect to the dbconsole process. In addition, Database Control will fail to connect to the Agent. Note for Windows Platform Only: On Windows, the dbconsole process will be stopped after the failed configuration attempt. Note that the tool used to perform Database Control configuration (DBUA, DBCA or EMCA) will also wait for 15 minutes for Database Control to start, then time out. The output of the "emctl status dbconsole" command incorrectly returns the status of Database Control, as shown below (note that this command may take a while to complete, especially in a RAC environment) : $ ./emctl status dbconsoleThe output of the "emctl status agent" command incorrectly returns the status of the Agent, as shown below: $ ./emctl status agentRecovering from Configuration Errors on a Single Instance Database1. Ignore any errors and continue with the installation or upgrade. The database will be created without errors.2. Apply Patch 8350262 to your Oracle Home installation using OPatch. NOTE: The database instance and the listener DO NOT have to be stopped for applying this patch, but ensure that all java processes sourced from the Oracle Home being patached are stopped in this case (i.e., all Oracle Home-related java.exe on Windows, for instance). opatch apply3. After applying the patch, force stop the Database Control (dbconsole) process using the killDBConsole script bundled with the patch. Note that the dbconsole process cannot be stopped using the emctl stop dbconsole command, as EMCTL is unable to connect to the process. To execute the killDBConsole script:
Note for Windows Platform Only: It is not necessary to force stop the dbconsole process on the Windows platform, because the process will already be in a stopped state at the end of the failed configuration attempt. The killDBConsole script output is shown below: $ <PATCH_HOME>/killDBConsole4. Re-secure Database Control with the following command: <ORACLE_HOME>/bin/emctl secure dbconsole -reset You will be prompted twice to confirm that the Root key must be overwritten. In both cases, enter upper-case "Y" as the response. Any other response (including lower-case "y") will cause the command to terminate without completing. If this happens, the command can be re-invoked. $ ./emctl secure dbconsole -reset5. Re-start Database Control with the following command: <ORACLE_HOME>/bin/emctl start dbconsole Recovering from Configuration Errors in an Oracle Real Application Clusters (RAC) Environment1. Ignore any errors and continue with the upgrade, so that the database is upgraded without errors.2. Apply Patch 8350262 to your Oracle Home installation. Note that the OPatch utility will apply the patch to all nodes in the cluster, as shown below: ../OPatch/opatch apply3. After applying the patch, force stop the Database Control (dbconsole) process by executing the killDBConsole script bundled with the patch on each node in the cluster. Note that the dbconsole process cannot be stopped using the emctl stop dbconsole command, as EMCTL is unable to connect to the process. To execute the killDBConsole script:
Note for Windows Platform Only: It is not necessary to force stop the dbconsole process on the Windows platform, because the process will already be in a stopped state at the end of the failed configuration attempt. The killDBConsole script output is shown below: $ <PATCH_HOME>/killDBConsoleNOTE: The following is a REQUIRED STEP! 4. Re-secure Database Control on the first cluster node with the following command: <ORACLE_HOME>/bin/emctl secure dbconsole -reset You will be prompted twice to confirm that the Root key must be overwritten. In both cases, enter upper-case "Y" as the response. Any other response (including lower-case "y") will cause the command to terminate without completing. If this happens, the command can be re-invoked. $ ./emctl secure dbconsole -reset5. Re-secure Database Control on the remaining cluster nodes with the following command. Note that the -reset switch is not included with this command: <ORACLE_HOME>/bin/emctl secure dbconsole (Note: the "Enter Enterprise Manager Root Password :" value is that for sysman) [myhost bin]$ ./emctl secure dbconsole6. Re-start Database Control by executing the following command on each node in the cluster: <ORACLE_HOME>/bin/emctl start dbconsol |
Subscribe to:
Posts (Atom)
The Best AI Apps for Android That Make Your Smartphone Smarter
The Best AI Apps for Android That Make Your Smartphone Smarter Introduction: In today's digital age, artificial intelligence (AI) ...
-
Future of Metaverse For context, we currently have three major metaverses: Web 3.0; Decentraland; and Fortnite. If you don’t know what the... -
If you have setup the script to perform auto backup with media manager like Netbackup then you must need to setup the RMAN backup log file a... -
With Redundant Interconnect Usage, you can identify multiple interfaces to use for the cluster private network, without the need of usin...
