Archive
Author Archive
ORACLE: CTAS
July 5, 2011
Leave a comment
If you need to create a table with millions of rows lightning fast, here is how.
SQL>CREATE TABLE emp_new
AS SELECT * FROM employees
PARALLEL DEGREE 4
NOLOGGING;
TABLE CREATED.
Now, depending on the number of CPU’s on your machine, select the parallel degree for your environment.
Categories: HOME
ORACLE:RMAN> REPORT SCHEMA
June 30, 2011
Leave a comment
1. Schema is a user that owns DB Objects, then why is Report Schema giving me tablespace and Datafile names?
RMAN> REPORT SCHEMA;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 500 SYSTEM YES C:\HARRY\HARRY\SYSTEM01.DBF
2 50 UNDOTBS1 YES C:\HARRY\HARRY\UNDOTBS01.DBF
3 310 SYSAUX NO C:\HARRY\HARRY\SYSAUX01.DBF
4 5 USERS NO C:\HARRY\HARRY\USERS01.DBF
5 20 HARRY NO C:\HARRY\HARRY\HARRY01.DBF
6 5 HARRY NO C:\HARRY\HARRY\HARRY02.DBF
7 10 AKSHAY NO C:\HARRY\HARRY\AKSHAY.DBF
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 15 TEMP 32767 C:\HARRY\HARRY\TEMP01.DBF
ANSWER:
When using REPORT SCHEMA is an RMAN command, don’t co-relate this SCHEMA with SCHEMA containing data inside database. You can say REPORT SCHEMA reports/shows things related to RMAN repository.
Categories: HOME
ORACLE: DB Incarnation
June 30, 2011
Leave a comment
What is Incarnation POINT and where is it used?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.
Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
Categories: HOME
ORACLE: INIT.ORA GUIDE
June 29, 2011
Leave a comment
# # Copyright (c) 1991, 1997, 1998 by Oracle Corporation # ############################################################################## # Example INIT.ORA file # # This file is provided by Oracle Corporation to help you customize # your RDBMS installation for your site. Important system parameters # are discussed, and example settings given. # # Some parameter settings are generic to any size installation. # For parameters that require different values in different size # installations, three scenarios have been provided: SMALL, MEDIUM # and LARGE. Any parameter that needs to be tuned according to # installation size will have three settings, each one commented # according to installation size. # # Use the following table to approximate the SGA size needed for the # three scenarious provided in this file: # # -------Installation/Database Size------ # SMALL MEDIUM LARGE # Block 2K 4500K 6800K 17000K # Size 4K 5500K 8800K 21000K # # To set up a database that multiple instances will be using, place # all instance-specific parameters in one file, and then have all # of these files point to a master file using the IFILE command. # This way, when you change a public # parameter, it will automatically change on all instances. This is # necessary, since all instances must run with the same value for many # parameters. For example, if you choose to use private rollback segments, # these must be specified in different files, but since all gc_* # parameters must be the same on all instances, they should be in one file. # # INSTRUCTIONS: Edit this file and the other INIT files it calls for # your site, either by using the values provided here or by providing # your own. Then place an IFILE= line into each instance-specific # INIT file that points at this file. # # NOTE: Parameter values suggested in this file are based on conservative # estimates for computer memory availability. You should adjust values upward # for modern machines. # # You may also consider using Database Configuration Assistant tool (DBCA) # to create INIT file and to size your initial set of tablespaces based # on the user input. # ############################################################################### # replace "%AVAILABLE_SID%" with your database name db_name=%AVAILABLE_SID% db_files = 80 # INITIAL # db_files = 80 # SMALL # db_files = 400 # MEDIUM # db_files = 1500 # LARGE %seed_control% db_file_multiblock_read_count = 8 # INITIAL # db_file_multiblock_read_count = 8 # SMALL # db_file_multiblock_read_count = 16 # MEDIUM # db_file_multiblock_read_count = 32 # LARGE db_block_buffers = 1000 # INITIAL # db_block_buffers = 100 # SMALL # db_block_buffers = 550 # MEDIUM # db_block_buffers = 3200 # LARGE shared_pool_size = 10000000 # INITIAL # shared_pool_size = 3500000 # SMALL # shared_pool_size = 5000000 # MEDIUM # shared_pool_size = 9000000 # LARGE log_checkpoint_interval = 10000 log_checkpoint_timeout = 0 processes = 59 # INITIAL # processes = 50 # SMALL # processes = 100 # MEDIUM # processes = 200 # LARGE parallel_max_servers = 5 # SMALL # parallel_max_servers = 4 x (number of CPUs) # MEDIUM # parallel_max_servers = 4 x (number of CPUs) # LARGE dml_locks = 200 log_buffer = 8192 # INITIAL # log_buffer = 32768 # SMALL # log_buffer = 32768 # MEDIUM # log_buffer = 163840 # LARGE # sequence_cache_entries made obsolete # sequence_cache_entries = 10 # INITIAL # sequence_cache_entries = 10 # SMALL # sequence_cache_entries = 30 # MEDIUM # sequence_cache_entries = 100 # LARGE # sequence_cache_hash_buckets made obsolete # sequence_cache_hash_buckets = 10 # INITIAL # sequence_cache_hash_buckets = 10 # SMALL # sequence_cache_hash_buckets = 23 # MEDIUM # sequence_cache_hash_buckets = 89 # LARGE # audit_trail = true # if you want auditing # timed_statistics = true # if you want timed statistics max_dump_file_size = 10240 # limit trace file size to 5 Meg each # Uncommenting the line below will cause automatic archiving if archiving has # been enabled using ALTER DATABASE ARCHIVELOG. # log_archive_start = true # log_archive_dest = %ORACLE_HOME%\database\%%ORACLE_SID%%\archive # log_archive_format = "%%%ORACLE_SID%%%T%TS%S.ARC" # If using private rollback segments, place lines of the following # form in each of your instance-specific init.ora files: # rollback_segments = (r01, r02, r03, r04) # If using public rollback segments, define how many # rollback segments each instance will pick up, using the formula # # of rollback segments = transactions / transactions_per_rollback_segment # In this example each instance will grab 40/5 = 8: # transactions = 40 # transactions_per_rollback_segment = 5 # Global Naming -- enforce that a dblink has same name as the db it connects to global_names = TRUE # Edit and uncomment the following line to provide the suffix that will be # appended to the db_name parameter (separated with a dot) and stored as the # global database name when a database is created. If your site uses # Internet Domain names for e-mail, then the part of your e-mail address after # the '@' is a good candidate for this parameter value. # db_domain = us.acme.com # global database name is db_name.db_domain # Uncomment the following line if you wish to enable the Oracle Trace product # to trace server activity. This enables scheduling of server collections # from the Oracle Enterprise Manager Console. # Also, if the oracle_trace_collection_name parameter is non-null, # every session will write to the named collection, as well as enabling you # to schedule future collections from the console. # oracle_trace_enable = TRUE # define directories to store trace and alert files background_dump_dest=%ORACLE_HOME%\RDBMS\trace user_dump_dest=%ORACLE_HOME%\RDBMS\trace db_block_size = 2048 remote_login_passwordfile = shared text_enable = TRUE # The following parameters are needed for the Advanced Replication Option job_queue_processes = 2 job_queue_interval = 10 # The following is obsolete on 8.1.x and above # job_queue_keep_connections = false # DISTRIBUTED_LOCK_TIMEOUT parameter has been made obsolete # distributed_lock_timeout = 300 distributed_transactions = 5 open_links = 4 # The following parameter is set to use some of the new 8.1 features. # Please remember that using them may require some downgrade # actions if you later decide to move back to 8.0. compatible = 8.1.5.0.0
Categories: HOME
ORACLE: RMAN-03009 ORA-19715 ORA-27302
June 29, 2011
Leave a comment
VALUABLE REFERENCE: http://ss64.com/ora/rman_format_string.html Performing a backup using RMAN was throwing errors earlier today. Searches on the web for errors related to RMAN-03009 ORA-19715 ORA-27302 did not provide solve why I was getting the error. RMAN> run { 2> # backup complete database to disk 3> allocate channel c1 type disk; 4> backup full 5> tag full_db 6> format 'c:\harry\backup\%d_%U_%T.bk' 7> (database); 8> release channel c1; 9> } allocated channel: c1 channel c1: sid=136 devtype=DISK Starting backup at 28-JUN-11 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\HARRY\HARRY\SYSTEM01.DBF input datafile fno=00003 name=C:\HARRY\HARRY\SYSAUX01.DBF input datafile fno=00002 name=C:\HARRY\HARRY\UNDOTBS01.DBF input datafile fno=00005 name=C:\HARRY\HARRY\HARRY01.DBF input datafile fno=00007 name=C:\HARRY\HARRY\AKSHAY.DBF input datafile fno=00004 name=C:\HARRY\HARRY\USERS01.DBF input datafile fno=00006 name=C:\HARRY\HARRY\HARRY02.DBF channel c1: starting piece 1 at 28-JUN-11 channel c1: finished piece 1 at 28-JUN-11 piece handle=C:\HARRY\BACKUP\HARRY_0NMG1NEA_1_1_20110628.BK tag=FULL_DB comment=NONE channel c1: backup set complete, elapsed time: 00:01:15 Finished backup at 28-JUN-11 Starting Control File and SPFILE Autobackup at 28-JUN-11 released channel: c1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 06/28/2011 18:59:38 ORA-19715: invalid format c for generated name ORA-27302: failure occurred at: slgpn All I gathered from Oracle forums was that this was related to a formatting error. http://forums.oracle.com/forums/thread.jspa?messageID=3722239 http://forums.oracle.com/forums/thread.jspa?messageID=1456885 http://www.dba-oracle.com/t_rman_19715_invalid_format_string.htm On analysis of the set parameter, I found the root cause of the error. RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; CONFIGURE BACKUP OPTIMIZATION OFF; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\harry\backup\%F_%U.cf'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; CONFIGURE MAXSETSIZE TO UNLIMITED; CONFIGURE ENCRYPTION FOR DATABASE OFF; CONFIGURE ENCRYPTION ALGORITHM 'AES128'; CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFHARRY.ORA'; A simple change to the parameters did the trick. RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\harry\backup\cf_%F'; RMAN> run { 2> allocate channel d1 type disk 3> format 'c:\harry\backup\rman%U.bk'; 4> backup current controlfile; 5> backup database; 6> backup archivelog all delete input; 7> } allocated channel: d1 channel d1: sid=135 devtype=DISK Starting backup at 29-JUN-11 channel d1: starting full datafile backupset channel d1: specifying datafile(s) in backupset including current control file in backupset channel d1: starting piece 1 at 29-JUN-11 channel d1: finished piece 1 at 29-JUN-11 piece handle=C:\HARRY\BACKUP\RMAN1IMG2ISL_1_1.BK tag=TAG20110629T024645 comment=NONE channel d1: backup set complete, elapsed time: 00:00:02 Finished backup at 29-JUN-11 Starting backup at 29-JUN-11 channel d1: starting full datafile backupset channel d1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\HARRY\HARRY\SYSTEM01.DBF input datafile fno=00003 name=C:\HARRY\HARRY\SYSAUX01.DBF input datafile fno=00002 name=C:\HARRY\HARRY\UNDOTBS01.DBF input datafile fno=00005 name=C:\HARRY\HARRY\HARRY01.DBF input datafile fno=00007 name=C:\HARRY\HARRY\AKSHAY.DBF input datafile fno=00004 name=C:\HARRY\HARRY\USERS01.DBF input datafile fno=00006 name=C:\HARRY\HARRY\HARRY02.DBF channel d1: starting piece 1 at 29-JUN-11 channel d1: finished piece 1 at 29-JUN-11 piece handle=C:\HARRY\BACKUP\RMAN1JMG2ISP_1_1.BK tag=TAG20110629T024649 comment=NONE channel d1: backup set complete, elapsed time: 00:01:26 Finished backup at 29-JUN-11 Starting backup at 29-JUN-11 current log archived channel d1: starting archive log backupset channel d1: specifying archive log(s) in backup set input archive log thread=1 sequence=41 recid=38 stamp=755059701 channel d1: starting piece 1 at 29-JUN-11 channel d1: finished piece 1 at 29-JUN-11 piece handle=C:\HARRY\BACKUP\RMAN1KMG2IVN_1_1.BK tag=TAG20110629T024822 comment=NONE channel d1: backup set complete, elapsed time: 00:00:02 channel d1: deleting archive log(s) archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\HARRY\ARCHIVELOG\2011_06_29\O1_MF_1_41_70OP6542_.ARC r ecid=38 stamp=755059701 Finished backup at 29-JUN-11 Starting Control File and SPFILE Autobackup at 29-JUN-11 piece handle=C:\HARRY\BACKUP\CF_C-2249710025-20110629-06 comment=NONE Finished Control File and SPFILE Autobackup at 29-JUN-11 released channel: d1 Backups successfull....good day all! CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'c:\harry\backup\cf_%F.cf'; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> run { 2> allocate channel c1 type disk; 3> backup full 4> tag full_db 5> format 'c:\harry\backup\%d_%U_%T.bk' 6> (database); 7> release channel c1; 8> } allocated channel: c1 channel c1: sid=125 devtype=DISK Starting backup at 29-JUN-11 channel c1: starting full datafile backupset channel c1: specifying datafile(s) in backupset input datafile fno=00001 name=C:\HARRY\HARRY\SYSTEM01.DBF input datafile fno=00003 name=C:\HARRY\HARRY\SYSAUX01.DBF input datafile fno=00002 name=C:\HARRY\HARRY\UNDOTBS01.DBF input datafile fno=00005 name=C:\HARRY\HARRY\HARRY01.DBF input datafile fno=00007 name=C:\HARRY\HARRY\AKSHAY.DBF input datafile fno=00004 name=C:\HARRY\HARRY\USERS01.DBF input datafile fno=00006 name=C:\HARRY\HARRY\HARRY02.DBF channel c1: starting piece 1 at 29-JUN-11 channel c1: finished piece 1 at 29-JUN-11 piece handle=C:\HARRY\BACKUP\HARRY_1SMG2OPL_1_1_20110629.BK tag=FULL_DB comment=NONE channel c1: backup set complete, elapsed time: 00:01:16 Finished backup at 29-JUN-11 Starting Control File and SPFILE Autobackup at 29-JUN-11 piece handle=C:\HARRY\BACKUP\CF_C-2249710025-20110629-09.CF comment=NONE Finished Control File and SPFILE Autobackup at 29-JUN-11 released channel: c1
Categories: HOME
ORACLE: DUMP DESTINATION
June 28, 2011
Leave a comment
In 11G,
background_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\HARRY\BDUMP
user_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\HARRY\UDUMP
are replaced by DIAGNOSTIC_DEST
IN 10g
SQL> column name for a20
SQL> column value for a45
SQL> select name,value from v$parameter where name like ('%dump%');
NAME VALUE
-------------------- ---------------------------------------------
shadow_core_dump partial
background_core_dump partial
background_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\HARRY\BDUMP
user_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\HARRY\UDUMP
max_dump_file_size UNLIMITED
core_dump_dest C:\ORACLE\PRODUCT\10.2.0\ADMIN\HARRY\CDUMP
6 rows selected.
In 11g
SQL> show parameter diag
Categories: HOME
ORACLE: RMAN AND RECOVERY DYNAMIC VIEWS
June 27, 2011
Leave a comment
RMAN RELATED VIEWS
SQL> column comments format a45
SQL> column table_name for a30
SQL> select * from dictionary where table_name like upper('%rman%') order by table_name desc;
TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------
V$RMAN_STATUS Synonym for V_$RMAN_STATUS
V$RMAN_OUTPUT Synonym for V_$RMAN_OUTPUT
V$RMAN_CONFIGURATION Synonym for V_$RMAN_CONFIGURATION
V$RMAN_BACKUP_TYPE Synonym for V_$RMAN_BACKUP_TYPE
V$RMAN_BACKUP_SUBJOB_DETAILS Synonym for V_$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS Synonym for V_$RMAN_BACKUP_JOB_DETAILS
GV$RMAN_OUTPUT Synonym for GV_$RMAN_OUTPUT
GV$RMAN_CONFIGURATION Synonym for GV_$RMAN_CONFIGURATION
8 rows selected.
RECOVERY RELATED VIEWS
SQL> COLUMN COMMENTS FOR A51
SQL> select * from dictionary where table_name like upper('%RECOVER%') order by table_name desc;
TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------------
V$RECOVER_FILE Synonym for V_$RECOVER_FILE
V$RECOVERY_STATUS Synonym for V_$RECOVERY_STATUS
V$RECOVERY_PROGRESS Synonym for V_$RECOVERY_PROGRESS
V$RECOVERY_LOG Synonym for V_$RECOVERY_LOG
V$RECOVERY_FILE_STATUS Synonym for V_$RECOVERY_FILE_STATUS
V$RECOVERY_FILE_DEST Synonym for V_$RECOVERY_FILE_DEST
V$INSTANCE_RECOVERY Synonym for V_$INSTANCE_RECOVERY
V$FLASH_RECOVERY_AREA_USAGE Synonym for V_$FLASH_RECOVERY_AREA_USAGE
GV$RECOVER_FILE Synonym for GV_$RECOVER_FILE
GV$RECOVERY_STATUS Synonym for GV_$RECOVERY_STATUS
GV$RECOVERY_PROGRESS Synonym for GV_$RECOVERY_PROGRESS
GV$RECOVERY_LOG Synonym for GV_$RECOVERY_LOG
GV$RECOVERY_FILE_STATUS Synonym for GV_$RECOVERY_FILE_STATUS
GV$INSTANCE_RECOVERY Synonym for GV_$INSTANCE_RECOVERY
DBA_RECOVERABLE_SCRIPT_PARAMS Details about the recoverable operation parameters
DBA_RECOVERABLE_SCRIPT_ERRORS Details showing errors during script execution
DBA_RECOVERABLE_SCRIPT_BLOCKS Details about the recoverable script blocks
DBA_RECOVERABLE_SCRIPT Details about recoverable operations
DBA_HIST_INSTANCE_RECOVERY Instance Recovery Historical Statistics Information
19 rows selected.
N.B. SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1118969
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1119127
ORA_ROWSCN IS A PSEUDOCOLUMN OF ANY TABLE THAT IS NOT FIXED OR EXTERNAL.
SQL> SELECT ORA_ROWSCN, NAME FROM XYZ.ABC;
ORA_ROWSCN NAME
---------- ------------------------------
907813 SYSTEM
907813 UNDOTBS1
907813 SYSAUX
907813 USERS
907813 TEMP
907813 HARRY
6 rows selected.
Categories: HOME
ORACLE: NOMOUNT, MOUNT And OPEN MODES in Oracle
June 25, 2011
Leave a comment
Lets take a look at the different OPEN_MODES in Oracle and Analyse what happens at every step.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT PFILE=C:\oracle\product\10.2.0\admin\HARRY\pfile\init.ora.5182011155654
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1291916 bytes
Variable Size 167774580 bytes
Database Buffers 415236096 bytes
Redo Buffers 7094272 bytes
In NOMOUNT MODE we create 1. DB's and 2. CONTROLFILES.
SQL> DESC V$INSTANCE
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)
SQL> SELECT HOST_NAME,INSTANCE_NAME,VERSION FROM V$INSTANCE;
HOST_NAME INSTANCE_NAME VERSION
---------------------------------------------------------------- ---------------- -----------------
TEST-PC harry 10.2.0.3.0
SQL> DESC V$CONTROLFILE;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER
SQL> DESC V$database
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
SQL> SELECT * FROM V$CONTROLFILE;
no rows selected
SQL> SELECT * FROM V$DATABASE;
SELECT * FROM V$DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted
So, now in nomount mode, the database SGA and Background process have been initialized only. Only the Instance is created, however, the database can not be queried as it is not initialized, as well as, the CONTROL FILES HAVE YET NOT BEEN READ, although they are present in the INIT.ORA file. They will be read in the Next Mode.
SQL> ALTER DATABASE MOUNT;
Database altered.
SQL> SELECT NAME, OPEN_MODE, LOG_MODE FROM V$DATABASE;
NAME OPEN_MODE LOG_MODE
-------------------- ---------- ------------
HARRY MOUNTED ARCHIVELOG
SQL> SELECT * FROM V$CONTROLFILE;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------- --- ---------- --------------
C:\HARRY\HARRY\CONTR NO 16384 430
OL01.CTL
C:\HARRY\HARRY\CONTR NO 16384 430
OL02.CTL
C:\HARRY\HARRY\CONTR NO 16384 430
OL03.CTL
Now that the Database is MOUNTED, Oracle can read the Control files and knows where all the datafiles are present.
Control file has pointers that provide connection b/w Datafiles, Redo Logs, Archived Logs, Memory and Last Updated SCN.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 23 52428800 1 YES INACTIVE 959048 23-JUN-11
3 1 25 52428800 1 NO CURRENT 1033570 25-JUN-11
2 1 24 52428800 1 YES INACTIVE 996350 24-JUN-11
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- ------------------------- ---------------------
3 ONLINE C:\HARRY\HARRY\REDO03.LOG NO
2 STALE ONLINE C:\HARRY\HARRY\REDO02.LOG NO
1 ONLINE C:\HARRY\HARRY\REDO01.LOG NO
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
So from here, we can see that the Database in Mount Mode has all the required parameters initialized,
however the database itself is still not open to users.
MOUNT MODE IS WHERE MAINTENANCE WORK IS DONE.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry OPEN
SQL> SELECT NAME, OPEN_MODE FROM V$DATABASE;
NAME OPEN_MODE
-------------------- ----------
HARRY READ WRITE
Now the database is open for use.
IN A NUTSHELL:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 1291916 bytes
Variable Size 289409396 bytes
Database Buffers 293601280 bytes
Redo Buffers 7094272 bytes
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry STARTED
SQL> alter database mount
2 ;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry MOUNTED
SQL> alter database open;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
harry OPEN
Categories: HOME
ORACLE: PFILE AND SPFILE
June 25, 2011
Leave a comment
When Oracle starts up, it first searches for SPFILE (Server Parameter File, a Binary File) . If it does not find a SPFILE, it then searches for PFILE ( a Text Parameter File, also know as the init.ora file).
SPFILE PFILE
BINARY FILE TEXT FILE
CHANGES TAKE PLACE IMMEDIATELY(FOR MOST) CHANGES TAKE PLACE ON THE NEXT STARTUP
To force database to startup with pfile,
CMD> EXPORT $ORACLE_HOME=DBNAME
CMD> SQLPLUS /AS SYSDBA
SQL> STARTUP PFILE=’ORACLE_HOME/DBS/INITDBNAME.ORA’
To make changes to the SPFILE,
SQL> CREATE PFILE FROM SPFILE;
To make changes to the pfile,
SQL> CREATE SPFILE FROM PFILE;
==============================================
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT PFILE=C:\oracle\product\10.2.0\admin\HARRY\pfile\init.ora.5182011155654 ORACLE instance started. Total System Global Area 591396864 bytes Fixed Size 1291916 bytes Variable Size 167774580 bytes Database Buffers 415236096 bytes Redo Buffers 7094272 bytes
Categories: HOME
ORACLE: SPOOL
June 25, 2011
Leave a comment
If you working with dynamic sql or need to write your results to a spool file, do the following.
SQL> SPOOL C:\TEXT.TXT
SQL> SELECT * FROM V$PARAMETER
SQL> SPOOL OFF;
Categories: HOME
Sharma
Over 10 years of rich and diversified experience in IT Support and Infrastructure Management and Support encompassing various process streams of Oracle Database Administration/Support Performance Tuning. In depth expertise on performing gap assessment, identifying areas of improvements and Designing and implementing the delivery for global and local clients and operational excellence.
>
Visitors to this site
oracleEdge 