STEPS
1. View the file name of your image. Pay special attention to case sensitivity.
e.g. file name IMAGE.gif
2. Copy the image to the $OA_MEDIA directory on ALL middle tiers
3. Edit profile option "Corporate Branding Image for Oracle Applications" at the site level.
Assign the value of your image file
e.g. IMAGE.gif
Make sure the case is exactly as the file is named. If the file name is not entered exactly as it is named the image will be broken and will not appear.
4. Stop / Start Apache to see the change
;;;
Solution Explanation:
---------------------
The Oracle logo on the right hand side of the Oracle Applications
menu is called from the following path.
$ORACLE_HOME/forms60/java/oracle/forms/icons/oracle_logo.gif
So you need to
1.Rename your logo as oracle_logo.gif
2.Put the logo in the following patch
$ORACLE_HOME/forms60/java/oracle/forms/icons/
3.Most importantly ...rgenerate the jar files using adadmin utility
4.Restart the form server again
5.Clear the jinitiator cache
6.Login into the applications again
You should get your logo now
Tuesday, August 31, 2010
Thursday, August 26, 2010
Creating a Physical Standby Database in Windows
-Preparing the Primary Database for Standby Database Creation
_ Creating a Physical Standby Database
_ Verifying the Physical Standby Database
EX:
PRIMARY=PRIME
STANDBY=SECON
Preparing the Primary Database for Standby Database Creation
Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
This statement may take a considerable amount of time to complete, because it
waits for all unlogged direct write I/O operations to finish.
Enable Archiving and Define a Local Archiving Destination
Set the local archive destination using the following SQL statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll
2> MANDATORY’ SCOPE=BOTH;
Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
To check if a password file already exists, run the following command:
If it doesn’t exist, use the following command to create one
select * from v$pwfile_users;
On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
Set Initialization Parameters on a Primary Database
Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile.
Edit pfilePRIM.ora to add the new primary and standby role parameters
#################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/PRIME/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\prime\PRIME\control01.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control02.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='PRIME'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.local_listener='PRIME'
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
*.log_archive_dest_1='C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIME'
PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="secon" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/udump'
#######################################################################
Create spfile from pfile, and restart primary database using the new spfile
Data Guard must use SPFILE. Create the SPFILE and restart database
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Verify that the new parameter is set in the file---using the newly created SP-file)
SQL>sho parameter pfile;
Creating a Physical Standby Database
Identify the Primary Database Datafiles
SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------------
/disk1/oracle/oradata/payroll/system01.dbf
/disk1/oracle/oradata/payroll/undotbs01.dbf
/disk1/oracle/oradata/payroll/cwmlite01.dbf
.
.
.
Make a Copy of the Primary Database
Step 1 Shut down the primary database.
Issue the following SQL*Plus statement to shut down the primary database:
SQL> SHUTDOWN IMMEDIATE;
Step 2 Copy the datafiles from primary to a standby database location.
Copy all the datafiles(.dbf) and redlog
Restart the primary database.
SQL> STARTUP;
Create a Control File for the Standby Database
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2> 'C:\oracle\product\10.2.0\oradata\secon\secon\stabdby.ctl';
(Use the same control file to bring up secondary)
Prepare the Initialization Parameter File to be Copied to the Standby Database from Primary
Issue this command from primary
SQL> CREATE PFILE=’ C:\oracle\product\10.2.0\db_1\database\initsecon.ora’ FROM SPFILE;
Set Initialization Parameters on a Physical Standby Database
Modifying Initialization Parameters for a Physical Standby Database
###################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/secon/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/secon/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\secon\secon\STANDBY.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/secon/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='secon'
*.dg_broker_start=TRUE
*.db_create_file_dest='C:\oracle\product\10.2.0\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
#*.log_archive_dest_1='location=C:\oracle\product\10.2.0\oradata\Arch_stby_arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=secon'
*.LOG_ARCHIVE_DEST_1='LOCATION="C:\oracle\product\10.2.0\oradata\Arch_stby_arch"',
'VALID_FOR=(ALL_LOGFILES,ALL_ROLES)',
'DB_UNIQUE_NAME=secon'
#PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prime)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="prime" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/secon/udump'
####################################################################
Create all required directories for dump directories and archived log destination\
EX:
[vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/
Configure the listener and tnsnames to support the database on both nodes
Configure listener.ora on both servers to hold entries for both databases
Configure tnsnames.ora on both servers to hold entries for both databases
Start the listener and check tnsping on both nodes to both services
[vmractest1.partnergsm.co.il] > tnsping prime
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
{oracle} /oradisk/app01/oracle/product/10gDB/network/admin
[vmractest1.partnergsm.co.il] > tnsping secon
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)
Set Up the Environment to Support the Standby Database on the standby node.
Copy the password file from Primary to Standby, sys password must be identical
And rename as it was in standby
Bringing UP the the STANDBY:
Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=secon
Startup nomount the Standby database and generate an spfile
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup mount the Standby database and perform recovery
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
The alert log of the standby will show the operations taking place
…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 96
Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2
Verify the Physical Standby Database Is Performing Properly
Check archived redo log on Standby
SQL> show parameters db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string blackowl
SQL> l
1* SELECT NAME FROM V$DATABASE
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
96 19-SEP-07 19-SEP-07
97 19-SEP-07 19-SEP-07
98 19-SEP-07 19-SEP-07
99 19-SEP-07 19-SEP-07
100 19-SEP-07 19-SEP-07
Switch logfiles on Primary
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check archived redo log on Standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------- --------------
96 19/09/07 09:35 19/09/07 09:45
97 19/09/07 09:45 19/09/07 15:20
98 19/09/07 15:20 19/09/07 15:48
99 19/09/07 15:48 19/09/07 16:00
100 19/09/07 16:00 19/09/07 16:05
101 19/09/07 16:05 19/09/07 16:08
102 19/09/07 16:08 19/09/07 16:08
7 rows selected.
_ Creating a Physical Standby Database
_ Verifying the Physical Standby Database
EX:
PRIMARY=PRIME
STANDBY=SECON
Preparing the Primary Database for Standby Database Creation
Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
This statement may take a considerable amount of time to complete, because it
waits for all unlogged direct write I/O operations to finish.
Enable Archiving and Define a Local Archiving Destination
Set the local archive destination using the following SQL statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll
2> MANDATORY’ SCOPE=BOTH;
Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
To check if a password file already exists, run the following command:
If it doesn’t exist, use the following command to create one
select * from v$pwfile_users;
On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
Set Initialization Parameters on a Primary Database
Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile.
Edit pfilePRIM.ora to add the new primary and standby role parameters
#################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/PRIME/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\prime\PRIME\control01.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control02.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='PRIME'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.local_listener='PRIME'
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
*.log_archive_dest_1='C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIME'
PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="secon" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/udump'
#######################################################################
Create spfile from pfile, and restart primary database using the new spfile
Data Guard must use SPFILE. Create the SPFILE and restart database
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Verify that the new parameter is set in the file---using the newly created SP-file)
SQL>sho parameter pfile;
Creating a Physical Standby Database
Identify the Primary Database Datafiles
SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------------
/disk1/oracle/oradata/payroll/system01.dbf
/disk1/oracle/oradata/payroll/undotbs01.dbf
/disk1/oracle/oradata/payroll/cwmlite01.dbf
.
.
.
Make a Copy of the Primary Database
Step 1 Shut down the primary database.
Issue the following SQL*Plus statement to shut down the primary database:
SQL> SHUTDOWN IMMEDIATE;
Step 2 Copy the datafiles from primary to a standby database location.
Copy all the datafiles(.dbf) and redlog
Restart the primary database.
SQL> STARTUP;
Create a Control File for the Standby Database
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2> 'C:\oracle\product\10.2.0\oradata\secon\secon\stabdby.ctl';
(Use the same control file to bring up secondary)
Prepare the Initialization Parameter File to be Copied to the Standby Database from Primary
Issue this command from primary
SQL> CREATE PFILE=’ C:\oracle\product\10.2.0\db_1\database\initsecon.ora’ FROM SPFILE;
Set Initialization Parameters on a Physical Standby Database
Modifying Initialization Parameters for a Physical Standby Database
###################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/secon/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/secon/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\secon\secon\STANDBY.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/secon/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='secon'
*.dg_broker_start=TRUE
*.db_create_file_dest='C:\oracle\product\10.2.0\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
#*.log_archive_dest_1='location=C:\oracle\product\10.2.0\oradata\Arch_stby_arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=secon'
*.LOG_ARCHIVE_DEST_1='LOCATION="C:\oracle\product\10.2.0\oradata\Arch_stby_arch"',
'VALID_FOR=(ALL_LOGFILES,ALL_ROLES)',
'DB_UNIQUE_NAME=secon'
#PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prime)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="prime" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/secon/udump'
####################################################################
Create all required directories for dump directories and archived log destination\
EX:
[vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/
Configure the listener and tnsnames to support the database on both nodes
Configure listener.ora on both servers to hold entries for both databases
Configure tnsnames.ora on both servers to hold entries for both databases
Start the listener and check tnsping on both nodes to both services
[vmractest1.partnergsm.co.il] > tnsping prime
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
{oracle} /oradisk/app01/oracle/product/10gDB/network/admin
[vmractest1.partnergsm.co.il] > tnsping secon
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)
Set Up the Environment to Support the Standby Database on the standby node.
Copy the password file from Primary to Standby, sys password must be identical
And rename as it was in standby
Bringing UP the the STANDBY:
Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=secon
Startup nomount the Standby database and generate an spfile
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup mount the Standby database and perform recovery
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
The alert log of the standby will show the operations taking place
…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 96
Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2
Verify the Physical Standby Database Is Performing Properly
Check archived redo log on Standby
SQL> show parameters db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string blackowl
SQL> l
1* SELECT NAME FROM V$DATABASE
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
96 19-SEP-07 19-SEP-07
97 19-SEP-07 19-SEP-07
98 19-SEP-07 19-SEP-07
99 19-SEP-07 19-SEP-07
100 19-SEP-07 19-SEP-07
Switch logfiles on Primary
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check archived redo log on Standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------- --------------
96 19/09/07 09:35 19/09/07 09:45
97 19/09/07 09:45 19/09/07 15:20
98 19/09/07 15:20 19/09/07 15:48
99 19/09/07 15:48 19/09/07 16:00
100 19/09/07 16:00 19/09/07 16:05
101 19/09/07 16:05 19/09/07 16:08
102 19/09/07 16:08 19/09/07 16:08
7 rows selected.
Thursday, August 5, 2010
Formula to calculate approximating size of the SGA(Shared Global Area)
7.X
----
((db_block_buffers * db_block_size) + shared_pool_size + log_buffers) / 0.9
8.0.X
-----
((db_block_buffers*db_block_size) + (shared_pool_size + large_pool_size +
log_buffers) + 1MB
8.1.X
------
((db_block_buffers * db_block_size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB
9.X
---
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS +
+ 1MB
Additional Info
-----------------------
- Redo Buffers in SHOW SGA does not mattch init.ora:log_buffer parameter setting.
- Enforced mininum is set to 4 times thhe maximum database block size.
- Java_pool_size not accounted for in SSHOW SGA or v$sga.
This is a bug that is fixed in 8.1.6.
- Java_pool_size restrictions in 8.1.5..
The default is 20000K.
If specifying in the init.ora, must it must be > 1000K, or will receive
ORA-01078
"failure in processing initialization parameters" error on startup.
- Java_pool_size restrictions in 8.1.6..
The default is 20000K.
This parameter can be set in the init.ora, but the enforced mininum is 32768.
----
((db_block_buffers * db_block_size) + shared_pool_size + log_buffers) / 0.9
8.0.X
-----
((db_block_buffers*db_block_size) + (shared_pool_size + large_pool_size +
log_buffers) + 1MB
8.1.X
------
((db_block_buffers * db_block_size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB
9.X
---
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS +
+ 1MB
Additional Info
-----------------------
- Redo Buffers in SHOW SGA does not mattch init.ora:log_buffer parameter setting.
- Enforced mininum is set to 4 times thhe maximum database block size.
- Java_pool_size not accounted for in SSHOW SGA or v$sga.
This is a bug that is fixed in 8.1.6.
- Java_pool_size restrictions in 8.1.5..
The default is 20000K.
If specifying in the init.ora, must it must be > 1000K, or will receive
ORA-01078
"failure in processing initialization parameters" error on startup.
- Java_pool_size restrictions in 8.1.6..
The default is 20000K.
This parameter can be set in the init.ora, but the enforced mininum is 32768.
Core Database Question:
What is stored in database redo log files???
1. changes to data made by the INSERT, UPDATE and DELETE DML statements
2. All DDL statements entered into the database
What causes a checkpoint to occur?
1. Shutting down the database with IMMEDIATE option
2. Taking a tablespace offline with the OFFLINE NORMAL option
3. Switching redo log files
UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in auto mode
The UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used. This parameter can be set in the initialization file or altered dynamically using the ALTER SYSTEM command.
The UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization file.
REORGANIZE, TRUNCATE, and DROP a table
1. Corresponding indexes are also truncated
2. the table- and row- level triggers are not fired
3. All the rows in the table are deleted, and all of the unused space is released.
ALTER TABLE MOVE command
1. To reorganize a table to eliminate row migration
2. To move a table from one tablespace to another
Which of the following statements about indexes are true?
• Normal index maintenance is performed when the corresponding columns are updated in the underlying table.
• Indexes can be partitioned even if the table is not partitioned.
• Function-based indexes can be created as either B-tree or bitmap indexes.
• Indexes can be created using up to 32 columns, but the combined length of all the columns cannot exceed one third of the space in an index block.
CREATING VARIOUS TYPES OF INDEXES
• Indexes speed up query performance and slow down DML operations.
• If you use the NOLOGGING clause when creating large indexes, then the performance of the database while the index is being created is much better.
When creating an index, you should use a low PCT FREE value for indexes on columns that are monotonically increasing, such as system-generated invoice numbers
ALTER TABLE orders MODIFY CONSTRAINT order_pk PRIMARY KEY (order_id) DEFERERRABLE ENABLE NOVALIDATE command
ENABLE---causes new data to be checked
NOVALIDATE---- skips the checking of existing data
DEFERRABLE---- forces the use of a nonunique index
Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile.
DBMS_OUTPUT package is an Oracle-supplied PL/SQL object used to aid in the debugging of user-developed PL/SQL code objects.
It cannot be used to change the language-dependent behavior settings of be database.
Obtain control file information:
1. V$CONTROLFILE: Lists the name and status of all control files associated with the instance
SELECT name FROM V$CONTROLFILE;
2. V$PARAMETER: Lists status and location of all parameters
SELECT name, value FROM V$PARAMETER
WHERE name= ‘control_files’;
3. V$CONTROLFILE_RECORD_SECTION: Provides information abt the control file record sections
SELECT type, record_size, records_total, records_used
FROM v$controlfile_record_section
WHERE type=’controlfile’;
3. SHOW PARAMETER control_files: Lists the name, status and location of the control files.
Describe the logical structure of the database
Locally managed tablespaces have which of the following advantages over dictionary-managed tablespaces??
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.
A tablespace can belong to only one database at a time and can consist of one or more data files.
Change the status of tablespaces
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.
Difference between read only and offline tablespaces is that the segments of a read only tablespace are available for read operations and can be dropped from the database.
Change the storage settings of tablespaces
To resize a tablespace
1. Use the ALTER DATABASE cmd to change the size of the datafiles used by a tablespace.
2. Use the ALTER DATABASE cmd to turn the autoextend feature on for each of the datafiles within a tablespace.
Situation LGWR performs sequential writes from the redo log buffer
1. When redo log buffer is one-third full.
2. When a transaction is committed.
3. Before DBWn writes modified blocks in the database buffer cache to the datafiles
Monitoring the Use of Diagnostic Files
The ALTER SESSION SET SQL_TRACE = TRUE command causes the server to write a trace file for the session that issued the statement. The trace file is written to the directory specified by the USER_DUMP_DEST parameter.
To let all DML operation finish and then shut down the database, you use the SHUTDOWN TRANSACTIONAL command.
The correct answer is alert log: this is where the database's day-to-day activities are tracked
The BACKGROUND_DUMP_DEST parameter controls where the alert log and background trace file are written.
Prerequisites Necessary for Database Creation
ORAPWD this is the utility you use to create a password file for an Oracle database
SYSDBA privileges are required to create a database. These are granted either using operating system authentication or password file authentication.
Create a Database Using Oracle Database Configuration Assistant
Benefits of using templates
1. Templates allow database options to be changed at database creation
2. Templates can be shared
3. Templates save time when you create a database
The ORACLE_SID operating system environment variable uniquely identifies the Oracle instance on the server.
The Database Configuration Assistant is Oracle's GUI tool for creating a database.
Create a Database Manually
The TIME_ZONE argument of the CREATE DATABASE statement sets the time zone for the database
MAXLOGFILES the maximum number of log file groups can be changed only by re-creating the control file.
DEFAULT TEMPORARY TABLESPACE: this argument of the CREATE DATABASE statement creates a default tablespace for sort operations for users that are not assigned a temporary tablespace.
Explain the Uses of Control File
The control file is opened and read when the database enters the Mount state, and the control file remains open until the database is dismounted.
Allocate Space for Temporary Segments
Tempfiles:
1. Tempfiles are always set to the NOLOGGING mode
2. You cannot make a tempfile read only.
3. You cannot rename a tempfile
4. You cannot create a tempfile with the ALTER DATABASE command.
List the Keywords That Control Block Space Usage
Segments created in a tablespace with automatic space management
1. The tablespace must have locally managed extents.
2. The PCTUSED and FREELISTS parameters are ignored.
Obtain Information about Storage Structures from the Data Dictionary
All the space in a data file (excluding the header block) must be accounted for in either the DBA_FREE_SPACE or DBA_EXTENTS view.
The DBA_FREE_SPACE table shows the amount of free space available in each tablespace
Implement Automatic Undo Management
Multiple undo tablespaces can exist, but only one at a time can be active per instance. Undo tablespaces can be dropped: they are not required for Oracle9i, because you can still use rollback segments. The system can switch to another undo tablespace only when a DBA issues the proper command.
1. UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization parameter file;
2. UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used, and it can be set in the initialization files or altered dynamically using the ALTER SYSTEM command;
3. UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in Auto mode.
Implement Data Integrity Constraints
1. Constraints enforce rules even when application code is bypassed with ad hoc tools.
2. Constraints are stored in a central repository.
3. Constraints are faster than application code
Three primary ways in which data integrity can be maintained within a database
Application code, database triggers, and declarative integrity constraints
Manage Passwords Using Profiles
Which views do you select from to see the password limit settings currently enforced for a particular user?
DBA_USERS and DBA_PROFILES
The RESOURCE_LIMIT parameter can be used to enable and disabled kernel limits in profiles; password limits are always enforced.
Administer Profiles
Each profile can be assigned to one or more users. However, each user must be assigned one and only one profile. The DEFAULT profile is assigned to all users initially until another profile is explicitly assigned.
Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile
Control Use of Resources Using Profiles
Profiles allow database administrators to prevent users from "hogging" resources, by configuring the system so that the user's session is killed when SESSION_IDLE_TIME is exceeded.
When any session resource limit is exceeded, the user receives an error message and is disconnected.
Create New Database Users
No quota should be assigned to the TEMP tablespace. The database kernel internally manages quotas on the designated temporary tablespace that is assigned to a user.
A user created with the IDENTIFIED EXTERNALLY clause can use the same username for both operating system and database access.
Alter and Drop Existing Database Users
If a user is altered to have 0 quota on a particular tablespace, then that user loses access to all of the objects he or she owns which are stored in that tablespace [FALSE]
Such users maintains access to any remaining free space in any objects they own that are currently stored in that tablespace. They cannot create or extend any new objects which require storage space in that tablespace.
The CASCADE clause is used with the DROP USER command in order to remove any database objects currently owned by the user being dropped.
Grant and Revoke Privileges
You must have the References object privilege on any table in another schema to which you want to create a foreign key.
Unlike a system privilege, which remains in force for users who were granted the privilege by a subsequent grantor, object privileges are revoked in a cascade, and therefore removed from subsequent grantees.
Create and Modify Roles
Which of the following statements about roles are true?
1. User privileges obtained through a role are set at login or with the SET ROLE command
2. A role can be assigned to another role.
A role can be granted to a user or another role. Grouping application roles into job function roles is an common way to use roles to support a large number of users.
Control Availability of Roles
An application role is created with the USING keyword and a PL/SQL object to be used for the role authentication.
The GRANT command is used to assign a role to a user. The ALTER ROLE command is used to maintain the contents of a role.
1. changes to data made by the INSERT, UPDATE and DELETE DML statements
2. All DDL statements entered into the database
What causes a checkpoint to occur?
1. Shutting down the database with IMMEDIATE option
2. Taking a tablespace offline with the OFFLINE NORMAL option
3. Switching redo log files
UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in auto mode
The UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used. This parameter can be set in the initialization file or altered dynamically using the ALTER SYSTEM command.
The UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization file.
REORGANIZE, TRUNCATE, and DROP a table
1. Corresponding indexes are also truncated
2. the table- and row- level triggers are not fired
3. All the rows in the table are deleted, and all of the unused space is released.
ALTER TABLE MOVE command
1. To reorganize a table to eliminate row migration
2. To move a table from one tablespace to another
Which of the following statements about indexes are true?
• Normal index maintenance is performed when the corresponding columns are updated in the underlying table.
• Indexes can be partitioned even if the table is not partitioned.
• Function-based indexes can be created as either B-tree or bitmap indexes.
• Indexes can be created using up to 32 columns, but the combined length of all the columns cannot exceed one third of the space in an index block.
CREATING VARIOUS TYPES OF INDEXES
• Indexes speed up query performance and slow down DML operations.
• If you use the NOLOGGING clause when creating large indexes, then the performance of the database while the index is being created is much better.
When creating an index, you should use a low PCT FREE value for indexes on columns that are monotonically increasing, such as system-generated invoice numbers
ALTER TABLE orders MODIFY CONSTRAINT order_pk PRIMARY KEY (order_id) DEFERERRABLE ENABLE NOVALIDATE command
ENABLE---causes new data to be checked
NOVALIDATE---- skips the checking of existing data
DEFERRABLE---- forces the use of a nonunique index
Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile.
DBMS_OUTPUT package is an Oracle-supplied PL/SQL object used to aid in the debugging of user-developed PL/SQL code objects.
It cannot be used to change the language-dependent behavior settings of be database.
Obtain control file information:
1. V$CONTROLFILE: Lists the name and status of all control files associated with the instance
SELECT name FROM V$CONTROLFILE;
2. V$PARAMETER: Lists status and location of all parameters
SELECT name, value FROM V$PARAMETER
WHERE name= ‘control_files’;
3. V$CONTROLFILE_RECORD_SECTION: Provides information abt the control file record sections
SELECT type, record_size, records_total, records_used
FROM v$controlfile_record_section
WHERE type=’controlfile’;
3. SHOW PARAMETER control_files: Lists the name, status and location of the control files.
Describe the logical structure of the database
Locally managed tablespaces have which of the following advantages over dictionary-managed tablespaces??
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.
A tablespace can belong to only one database at a time and can consist of one or more data files.
Change the status of tablespaces
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.
Difference between read only and offline tablespaces is that the segments of a read only tablespace are available for read operations and can be dropped from the database.
Change the storage settings of tablespaces
To resize a tablespace
1. Use the ALTER DATABASE cmd to change the size of the datafiles used by a tablespace.
2. Use the ALTER DATABASE cmd to turn the autoextend feature on for each of the datafiles within a tablespace.
Situation LGWR performs sequential writes from the redo log buffer
1. When redo log buffer is one-third full.
2. When a transaction is committed.
3. Before DBWn writes modified blocks in the database buffer cache to the datafiles
Monitoring the Use of Diagnostic Files
The ALTER SESSION SET SQL_TRACE = TRUE command causes the server to write a trace file for the session that issued the statement. The trace file is written to the directory specified by the USER_DUMP_DEST parameter.
To let all DML operation finish and then shut down the database, you use the SHUTDOWN TRANSACTIONAL command.
The correct answer is alert log: this is where the database's day-to-day activities are tracked
The BACKGROUND_DUMP_DEST parameter controls where the alert log and background trace file are written.
Prerequisites Necessary for Database Creation
ORAPWD this is the utility you use to create a password file for an Oracle database
SYSDBA privileges are required to create a database. These are granted either using operating system authentication or password file authentication.
Create a Database Using Oracle Database Configuration Assistant
Benefits of using templates
1. Templates allow database options to be changed at database creation
2. Templates can be shared
3. Templates save time when you create a database
The ORACLE_SID operating system environment variable uniquely identifies the Oracle instance on the server.
The Database Configuration Assistant is Oracle's GUI tool for creating a database.
Create a Database Manually
The TIME_ZONE argument of the CREATE DATABASE statement sets the time zone for the database
MAXLOGFILES the maximum number of log file groups can be changed only by re-creating the control file.
DEFAULT TEMPORARY TABLESPACE: this argument of the CREATE DATABASE statement creates a default tablespace for sort operations for users that are not assigned a temporary tablespace.
Explain the Uses of Control File
The control file is opened and read when the database enters the Mount state, and the control file remains open until the database is dismounted.
Allocate Space for Temporary Segments
Tempfiles:
1. Tempfiles are always set to the NOLOGGING mode
2. You cannot make a tempfile read only.
3. You cannot rename a tempfile
4. You cannot create a tempfile with the ALTER DATABASE command.
List the Keywords That Control Block Space Usage
Segments created in a tablespace with automatic space management
1. The tablespace must have locally managed extents.
2. The PCTUSED and FREELISTS parameters are ignored.
Obtain Information about Storage Structures from the Data Dictionary
All the space in a data file (excluding the header block) must be accounted for in either the DBA_FREE_SPACE or DBA_EXTENTS view.
The DBA_FREE_SPACE table shows the amount of free space available in each tablespace
Implement Automatic Undo Management
Multiple undo tablespaces can exist, but only one at a time can be active per instance. Undo tablespaces can be dropped: they are not required for Oracle9i, because you can still use rollback segments. The system can switch to another undo tablespace only when a DBA issues the proper command.
1. UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization parameter file;
2. UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used, and it can be set in the initialization files or altered dynamically using the ALTER SYSTEM command;
3. UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in Auto mode.
Implement Data Integrity Constraints
1. Constraints enforce rules even when application code is bypassed with ad hoc tools.
2. Constraints are stored in a central repository.
3. Constraints are faster than application code
Three primary ways in which data integrity can be maintained within a database
Application code, database triggers, and declarative integrity constraints
Manage Passwords Using Profiles
Which views do you select from to see the password limit settings currently enforced for a particular user?
DBA_USERS and DBA_PROFILES
The RESOURCE_LIMIT parameter can be used to enable and disabled kernel limits in profiles; password limits are always enforced.
Administer Profiles
Each profile can be assigned to one or more users. However, each user must be assigned one and only one profile. The DEFAULT profile is assigned to all users initially until another profile is explicitly assigned.
Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile
Control Use of Resources Using Profiles
Profiles allow database administrators to prevent users from "hogging" resources, by configuring the system so that the user's session is killed when SESSION_IDLE_TIME is exceeded.
When any session resource limit is exceeded, the user receives an error message and is disconnected.
Create New Database Users
No quota should be assigned to the TEMP tablespace. The database kernel internally manages quotas on the designated temporary tablespace that is assigned to a user.
A user created with the IDENTIFIED EXTERNALLY clause can use the same username for both operating system and database access.
Alter and Drop Existing Database Users
If a user is altered to have 0 quota on a particular tablespace, then that user loses access to all of the objects he or she owns which are stored in that tablespace [FALSE]
Such users maintains access to any remaining free space in any objects they own that are currently stored in that tablespace. They cannot create or extend any new objects which require storage space in that tablespace.
The CASCADE clause is used with the DROP USER command in order to remove any database objects currently owned by the user being dropped.
Grant and Revoke Privileges
You must have the References object privilege on any table in another schema to which you want to create a foreign key.
Unlike a system privilege, which remains in force for users who were granted the privilege by a subsequent grantor, object privileges are revoked in a cascade, and therefore removed from subsequent grantees.
Create and Modify Roles
Which of the following statements about roles are true?
1. User privileges obtained through a role are set at login or with the SET ROLE command
2. A role can be assigned to another role.
A role can be granted to a user or another role. Grouping application roles into job function roles is an common way to use roles to support a large number of users.
Control Availability of Roles
An application role is created with the USING keyword and a PL/SQL object to be used for the role authentication.
The GRANT command is used to assign a role to a user. The ALTER ROLE command is used to maintain the contents of a role.
Managing Tablespaces and data files:
Tablespaces: Can belong to only one database at a time
Consist of one or more data files
Are further divided into logical units of storage
Data files: Can belong to only one tablespace and one database
Is a repository for schema object data
SYSTEM tablespace
1. Created with the database
2. Required in all databases
3. Contains the data dictionary, including the stored program units
4. Contains the SYSTEM undo segment
5. Should not contain user data, although it is allowed.
Non-SYSTEM tablespaces:
1. Enables more flexibility in database administration
2. Separate undo, temporary, application data, and application index segments
3. Separate data by backup requirements
4. separate dynamic and static data
5. Control the amount of space allocated 2 the users.
Creating tablespace:
CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIMUN EXTENT integer [K|M]
[BLOCK SIZE integer [K]]
[LOGGING|NOLOGGING]--- All tables, indexes, and partitions within the tablespace have the changes written to online redo log files.
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management clause]
[segment_management clause]
Locally managed Tablespaces:
Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Extent_management_clause:
[EXTENT MANAGEMENT [DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]]
DICTIONARY: tbspc is managed using data dictionary tables
LOCAL: locally managed via bitmaps. If you specify LOCAL you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.
AUTOALLOCATE: tbspc is system managed. Users cannot specify an extent size.
UNIFORM: tbspc is managed with uniform extents of SIZE bytes. Default size 1 MB.
Advantages of locally Managed Tablespaces:
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.
Dictionary- Managed Tablespaces
Extents are managed in the data dictionary
Each segment stored in the tablespace can have a different storage clause.
Coalescing is required.
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(Initial 1M NEXT 1M PCTINCREASE 0);
Storage is more flexible than locally managed tablespaces but much less efficient.
Undo Tablespaces
Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses
E.g. CREATE UNDO TABLESPACE undo1
DATAFILE ‘/u01/oradata/undo01.dbf’ SIZE 40M;
An undo tbspc cannot be dropped if it is being used by any instance or contains any undo information to recover transactions.
Temporary Tablespaces
Used for sort operations
Can be shared by multiple users
Cannot contain any permanent objects
Locally managed extents recommended.
E.g. CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘u01/oradata/temp01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Note: Nonstandard block size cannot be specified when creating temporary tbspc.
Tempfiles:
Always set to the NOLOGGING mode.
Cannot make a tempfile read-only
Cannot rename a tempfile
Cannot create a tempfile with the ALTER DATABASE command
Tempfiles are required for read-only databases
Media recovery does not recover tempfiles
To optimize the performance of a sort area in a temporary tbspc, set the UNIFORM SIZE to be a multiple of the parameter SORT_SIZE_SIZE.
Default Temporary Tablespace
Specifies a database-wide default temporary tablespace
Eliminates using SYSTEM tbspc for storing temporary data
Can be created using:
CRETAE DATABASE DBA01
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/$HOME/ORADATA/u03/temp01.dbf’ SIZE 4M
Or
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE default_temp2;
To find the default temp tbspc for the database query DATABASE_PROPERTIES
SELECT * FROM DATABASE_PROPERTIES;
Restrictions on default temporary tablespace:
Default tbspc cannot be:
Dropped until after a new default is made available
Taken offline
Altered to a permanent tablespace
Read Only Tablespaces
ALTER TABLESPACE userdata READ ONLY;
Causes a checkpoint
Data available only for red operations
Objects can be dropped from tbspc (tables and indexes because these commands affect only the data dictionary
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.
Taking a tablespace offline
Reasons:
Make a portion of the database unavailable, while allowing normal access to the remainder of the database
Perform an offline tablespace backed up (although a tbspc can be backed up while online and in use)
Recover a tablespace or data file while the database is open
Move a data file while the database is open
Offline status of a tablespace
Tbspc offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tbspc
When tbspc goes offline or comes back online, the event is recorded in the data dictionary and in the control file.
Taking Tablespace Offline
ALTER TABLSPACE tablespace
{ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVERY]}
NORMAL: flushes all blocks in all data files in the tbspc out of SGA
TEMPORARY: performs a checkpoint for all online datafiles. Any offline file may require media recovery
IMMEDIATE: does not ensure that the tbspc files are available, does not perform a checkpoint, and must perform media recovery before bringing it back online.
FOR RECOVERY: point-in-time recovery
Changing storage Settings
Using ALTER TABLESPACE command to change storage setting
E.g. ALTER TABLESPACE userdata MINIMUN EXTENT 2M;
ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);
Resizing a Tablespace
1. Enabling Automatic Extension of data files
Can be resized automatically with the following commands:
-CREATE DATABASE
-CREATE TABLESPACE
-ALTER TABLESPACE … ADD DATAFILE
E.g. CREATE TABLESPACE user_data
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.
SELECT tablespace_name, file_name, autoextensible
FROM dba_data_files;
2. Manually Resizing a Data File
Adds more space without adding more data files
Reclaims unused space in database
ALTER DATABASE
DATAFILE ‘u03/oradata/userdata02.dbf’
RESIZE 200M;
3. Adding Data Files to a tablespace
ALTER TABLESPACE user_data
ADD DATAFILE ‘u01/oradata/userdata03.dbf’
SIZE 200M;
Moving Data Files
• ALTER TABLESPACE
[Tbspc must be offline
Target data files must exist]
Steps
Take the tbspc offline
Use an OS command to move or copy the files
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u02/oradata/userdata01.dbf’;
Bring tbspc online
Use OS command to delete the file if necessary
• ALTER DATABASE
[Database must be mounted
Target data file must exist]
Steps
Shut down the database
Use OS command to move the file
Mount the database
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u03/oradata/userdata01.dbf’;
Open the database
Dropping Tablespaces
You cannot drop a tbspc if it
- is the SYSTEM tbspc
- has active segments
INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES deletes data files
CASCADE CONSTRAINTS drops all referential integrity constraints.
Note: tbspc dropped, only file pointers in the control file of the associated database is dropped.
Take tbspc offline before dropping it to ensure that no transactions access any of the segments in the tbspc.
Obtaining Tablespace Information
Tbspc info: DBA_TABLESPACE
V$TABLESPACE
Datafile info: DBA_DATA_FILES
V$DATAFILE
Temp file info: DBA_TEMP_FILES
V$TEMPFILE
Consist of one or more data files
Are further divided into logical units of storage
Data files: Can belong to only one tablespace and one database
Is a repository for schema object data
SYSTEM tablespace
1. Created with the database
2. Required in all databases
3. Contains the data dictionary, including the stored program units
4. Contains the SYSTEM undo segment
5. Should not contain user data, although it is allowed.
Non-SYSTEM tablespaces:
1. Enables more flexibility in database administration
2. Separate undo, temporary, application data, and application index segments
3. Separate data by backup requirements
4. separate dynamic and static data
5. Control the amount of space allocated 2 the users.
Creating tablespace:
CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIMUN EXTENT integer [K|M]
[BLOCK SIZE integer [K]]
[LOGGING|NOLOGGING]--- All tables, indexes, and partitions within the tablespace have the changes written to online redo log files.
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management clause]
[segment_management clause]
Locally managed Tablespaces:
Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Extent_management_clause:
[EXTENT MANAGEMENT [DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]]
DICTIONARY: tbspc is managed using data dictionary tables
LOCAL: locally managed via bitmaps. If you specify LOCAL you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.
AUTOALLOCATE: tbspc is system managed. Users cannot specify an extent size.
UNIFORM: tbspc is managed with uniform extents of SIZE bytes. Default size 1 MB.
Advantages of locally Managed Tablespaces:
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.
Dictionary- Managed Tablespaces
Extents are managed in the data dictionary
Each segment stored in the tablespace can have a different storage clause.
Coalescing is required.
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(Initial 1M NEXT 1M PCTINCREASE 0);
Storage is more flexible than locally managed tablespaces but much less efficient.
Undo Tablespaces
Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses
E.g. CREATE UNDO TABLESPACE undo1
DATAFILE ‘/u01/oradata/undo01.dbf’ SIZE 40M;
An undo tbspc cannot be dropped if it is being used by any instance or contains any undo information to recover transactions.
Temporary Tablespaces
Used for sort operations
Can be shared by multiple users
Cannot contain any permanent objects
Locally managed extents recommended.
E.g. CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘u01/oradata/temp01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Note: Nonstandard block size cannot be specified when creating temporary tbspc.
Tempfiles:
Always set to the NOLOGGING mode.
Cannot make a tempfile read-only
Cannot rename a tempfile
Cannot create a tempfile with the ALTER DATABASE command
Tempfiles are required for read-only databases
Media recovery does not recover tempfiles
To optimize the performance of a sort area in a temporary tbspc, set the UNIFORM SIZE to be a multiple of the parameter SORT_SIZE_SIZE.
Default Temporary Tablespace
Specifies a database-wide default temporary tablespace
Eliminates using SYSTEM tbspc for storing temporary data
Can be created using:
CRETAE DATABASE DBA01
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/$HOME/ORADATA/u03/temp01.dbf’ SIZE 4M
Or
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE default_temp2;
To find the default temp tbspc for the database query DATABASE_PROPERTIES
SELECT * FROM DATABASE_PROPERTIES;
Restrictions on default temporary tablespace:
Default tbspc cannot be:
Dropped until after a new default is made available
Taken offline
Altered to a permanent tablespace
Read Only Tablespaces
ALTER TABLESPACE userdata READ ONLY;
Causes a checkpoint
Data available only for red operations
Objects can be dropped from tbspc (tables and indexes because these commands affect only the data dictionary
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.
Taking a tablespace offline
Reasons:
Make a portion of the database unavailable, while allowing normal access to the remainder of the database
Perform an offline tablespace backed up (although a tbspc can be backed up while online and in use)
Recover a tablespace or data file while the database is open
Move a data file while the database is open
Offline status of a tablespace
Tbspc offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tbspc
When tbspc goes offline or comes back online, the event is recorded in the data dictionary and in the control file.
Taking Tablespace Offline
ALTER TABLSPACE tablespace
{ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVERY]}
NORMAL: flushes all blocks in all data files in the tbspc out of SGA
TEMPORARY: performs a checkpoint for all online datafiles. Any offline file may require media recovery
IMMEDIATE: does not ensure that the tbspc files are available, does not perform a checkpoint, and must perform media recovery before bringing it back online.
FOR RECOVERY: point-in-time recovery
Changing storage Settings
Using ALTER TABLESPACE command to change storage setting
E.g. ALTER TABLESPACE userdata MINIMUN EXTENT 2M;
ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);
Resizing a Tablespace
1. Enabling Automatic Extension of data files
Can be resized automatically with the following commands:
-CREATE DATABASE
-CREATE TABLESPACE
-ALTER TABLESPACE … ADD DATAFILE
E.g. CREATE TABLESPACE user_data
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.
SELECT tablespace_name, file_name, autoextensible
FROM dba_data_files;
2. Manually Resizing a Data File
Adds more space without adding more data files
Reclaims unused space in database
ALTER DATABASE
DATAFILE ‘u03/oradata/userdata02.dbf’
RESIZE 200M;
3. Adding Data Files to a tablespace
ALTER TABLESPACE user_data
ADD DATAFILE ‘u01/oradata/userdata03.dbf’
SIZE 200M;
Moving Data Files
• ALTER TABLESPACE
[Tbspc must be offline
Target data files must exist]
Steps
Take the tbspc offline
Use an OS command to move or copy the files
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u02/oradata/userdata01.dbf’;
Bring tbspc online
Use OS command to delete the file if necessary
• ALTER DATABASE
[Database must be mounted
Target data file must exist]
Steps
Shut down the database
Use OS command to move the file
Mount the database
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u03/oradata/userdata01.dbf’;
Open the database
Dropping Tablespaces
You cannot drop a tbspc if it
- is the SYSTEM tbspc
- has active segments
INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES deletes data files
CASCADE CONSTRAINTS drops all referential integrity constraints.
Note: tbspc dropped, only file pointers in the control file of the associated database is dropped.
Take tbspc offline before dropping it to ensure that no transactions access any of the segments in the tbspc.
Obtaining Tablespace Information
Tbspc info: DBA_TABLESPACE
V$TABLESPACE
Datafile info: DBA_DATA_FILES
V$DATAFILE
Temp file info: DBA_TEMP_FILES
V$TEMPFILE
Patching related questions
Patching related questions
Prereq not met while patching.
In this case the patch ultimately fails.
Solution:
========
1) Immediately take a backup of restart files which contains the current patch/failed patch information.
2) Take a backup of AD_DEFFERED_JOBS and FND_INSTALL_PROCESSES tables if they exist, and drop them.
3) Check which pre-requisite you haven't applied.
4) Download and apply the pre-requisite.
5) Once it is successfully applied, restore the failed patch restart files and the tables which are backed up.
6) Run adpatch and continue from the previous session.
NOTE:-This is usually followed if a large patch fails after running for a long time. Say if a patch fails immediately after applying, you can go for reapplying the patch freshly after all the prerequisites are met.
Apply only the database portion of a unified Driver
$ adpatch options=nocopyportion,nogenerateportion
At the prompt for the driver name, specify the unified (u) driver. AutoPatch runs the driver, applying only the database portion of the patch.
Enabling Password Validation
adpatch options=validate
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES (ie OAM) holds information about the "distinct" Oracle Application patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records
the patches on OAM take the values from ad_applied_patches, while it stores the values of bug in the ad_bugs.
REAPPLY PATCH:
AD_BUGS--will give you information about the bug fixes in the system
(AD_PATCH_RUN_BUGS will also do the same)
AD_APPLIED_PATCHES--will give you information about the patches appplied to the system.
Say if you have a ROLLUP patch or a patchset, which will fix many small bug fixes, AD_APPLIED_PATCHES will have only the patch number,
but
AD_BUGS(AD_PATCH_RUN_BUGS) will have all the bug fixes by that particular patch.
Use AD_PATCH_RUN_BUGS.
STATUS OF A PATCH:
ps -ef | grep adpatch and if it returns, the patch is still running
adadmin and adctrl
Applying a main patch, which fails for a pre-req patch, and now you want to apply the pre-req patch to resolve the error but DONOT want to skip the main patch??
1. Using the adctrl utility, shutdown the workers of the main patch.
a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
a. sqlplus applsys/
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
4. Backup the .rf9 files located in $APPL_TOP/admin//restart directory.
At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/
b. mv restart restart_back
c. mkdir restart
5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;
6. Apply the new patch.- The pre-req patch which was missed.
7. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
directory.
a. cd $APPL_TOP/admin/
b. mv restart restart_
c. mv restart_back restart
8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
schema.
a. sqlplus applsys/
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
10. Re-create synonyms
a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
11. Start adpatch, it will resume where it stopped previously.
Prereq not met while patching.
In this case the patch ultimately fails.
Solution:
========
1) Immediately take a backup of restart files which contains the current patch/failed patch information.
2) Take a backup of AD_DEFFERED_JOBS and FND_INSTALL_PROCESSES tables if they exist, and drop them.
3) Check which pre-requisite you haven't applied.
4) Download and apply the pre-requisite.
5) Once it is successfully applied, restore the failed patch restart files and the tables which are backed up.
6) Run adpatch and continue from the previous session.
NOTE:-This is usually followed if a large patch fails after running for a long time. Say if a patch fails immediately after applying, you can go for reapplying the patch freshly after all the prerequisites are met.
Apply only the database portion of a unified Driver
$ adpatch options=nocopyportion,nogenerateportion
At the prompt for the driver name, specify the unified (u) driver. AutoPatch runs the driver, applying only the database portion of the patch.
Enabling Password Validation
adpatch options=validate
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES (ie OAM) holds information about the "distinct" Oracle Application patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records
the patches on OAM take the values from ad_applied_patches, while it stores the values of bug in the ad_bugs.
REAPPLY PATCH:
AD_BUGS--will give you information about the bug fixes in the system
(AD_PATCH_RUN_BUGS will also do the same)
AD_APPLIED_PATCHES--will give you information about the patches appplied to the system.
Say if you have a ROLLUP patch or a patchset, which will fix many small bug fixes, AD_APPLIED_PATCHES will have only the patch number,
but
AD_BUGS(AD_PATCH_RUN_BUGS) will have all the bug fixes by that particular patch.
Use AD_PATCH_RUN_BUGS.
STATUS OF A PATCH:
ps -ef | grep adpatch and if it returns, the patch is still running
adadmin and adctrl
Applying a main patch, which fails for a pre-req patch, and now you want to apply the pre-req patch to resolve the error but DONOT want to skip the main patch??
1. Using the adctrl utility, shutdown the workers of the main patch.
a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
a. sqlplus applsys/
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
4. Backup the .rf9 files located in $APPL_TOP/admin/
At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/
b. mv restart restart_back
c. mkdir restart
5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;
6. Apply the new patch.- The pre-req patch which was missed.
7. Restore the .rf9 files located in $APPL_TOP/admin/
directory.
a. cd $APPL_TOP/admin/
b. mv restart restart_
c. mv restart_back restart
8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
schema.
a. sqlplus applsys/
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
10. Re-create synonyms
a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
11. Start adpatch, it will resume where it stopped previously.
Patching Doubt :
c and g drivers BOTH will go on all those nodes which have APPL_TOP present on them.
So now it depends on our configuration of the "web" node.
If our Web node is a pure web server with only IAS_ORACLE_HOME on it then we donot apply "Apps" patches(c,d or g) on them,we will apply only "oracle" patches there.So neither c or g go should go on a pure Web node.
And if our Web node is also handling a bit of forms(which is normally the case) i.e. if it contains any plls etc.. then BOTH c & g drivers will be applied.
QUESTIONS:
1. What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?
Database connection information, IAS_ORACLE_HOME/Apache/modplsql/conf
2. adident utility is used for what ?
to find file version
3. How can you license a product after installation ?
adlicmgr.sh or via OAM (Oracle Application Manager)
4. What are main configuration files in Web Server (Apache) ?
httpd.conf, jserv.conf, jserv.properties, zone.properties....
5. How to check if Apps 11i System is Autoconfig enabled ?
Check in any configuration file and if on top of file it is written that created by autoconfig
6. How to check if Oracle Apps 11i System is Rapid Clone enabled ?
If rapid clone patches are applied
7. What is the difference between apps listener & web listener ?
Apps listener are FNDFS and FNDSM listener and web listener is for web requests (HTTP/HTTPS)
8. What is difference between adpatch & opatch ? Can you use both adpatch & opatch in Apps ?
adpatch is used for applying apps patch
opatch is used for applying database patch
Yes both are used in apps , adpatch for apps patch and opatch for database patch
Significance of _ALL tables
_all tables store data for all organizations if you have multi-org enabled
So now it depends on our configuration of the "web" node.
If our Web node is a pure web server with only IAS_ORACLE_HOME on it then we donot apply "Apps" patches(c,d or g) on them,we will apply only "oracle" patches there.So neither c or g go should go on a pure Web node.
And if our Web node is also handling a bit of forms(which is normally the case) i.e. if it contains any plls etc.. then BOTH c & g drivers will be applied.
QUESTIONS:
1. What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?
Database connection information, IAS_ORACLE_HOME/Apache/modplsql/conf
2. adident utility is used for what ?
to find file version
3. How can you license a product after installation ?
adlicmgr.sh or via OAM (Oracle Application Manager)
4. What are main configuration files in Web Server (Apache) ?
httpd.conf, jserv.conf, jserv.properties, zone.properties....
5. How to check if Apps 11i System is Autoconfig enabled ?
Check in any configuration file and if on top of file it is written that created by autoconfig
6. How to check if Oracle Apps 11i System is Rapid Clone enabled ?
If rapid clone patches are applied
7. What is the difference between apps listener & web listener ?
Apps listener are FNDFS and FNDSM listener and web listener is for web requests (HTTP/HTTPS)
8. What is difference between adpatch & opatch ? Can you use both adpatch & opatch in Apps ?
adpatch is used for applying apps patch
opatch is used for applying database patch
Yes both are used in apps , adpatch for apps patch and opatch for database patch
Significance of _ALL tables
_all tables store data for all organizations if you have multi-org enabled
Wednesday, August 4, 2010
Patch Metadata files
These contain a list of files included in the patch and the relationships between this patch and other patches. The metadata files are normally called b.ldt and/or f.ldt. The information in the patch metadata files are used by the AD utilities to determine prerequisite patches, analyze the impact of the patch on the system and compare versions of files in the patch with those of files in the system
Tuesday, August 3, 2010
Oracle Application-- Maintainance mode
Here is the main reason for maintenance mode and how it minimizes downtime:
There is a new Workflow feature called Cache Invalidation, which is built on the Business Events System. This feature generates Java cache invalidation messages for changes to seed data and, as a result, hampers the performance of FNDLOAD. As some patch sessions update a significant amount of seed data and also require running FNDLOAD in the background,
Having this feature enabled negatively impacts the performance of Auto Patch sessions. Maintenance mode shuts down the Workflow Business Events System.
There is a new Workflow feature called Cache Invalidation, which is built on the Business Events System. This feature generates Java cache invalidation messages for changes to seed data and, as a result, hampers the performance of FNDLOAD. As some patch sessions update a significant amount of seed data and also require running FNDLOAD in the background,
Having this feature enabled negatively impacts the performance of Auto Patch sessions. Maintenance mode shuts down the Workflow Business Events System.
GUEST, APPLSYS, APPLSYSPUB, APPS users:
GUEST is like a Dummy Account. By default it has ORACLE as password.
GUEST account used by JDBC Drivers and Oracle Self Service Web Applications like istores, irecrutiment, iprocurement, ipayables, ireceivables etc to make initial Connection.
APPLSUSPUB Account is used at Initital Client Connectiviey to Oracle Applications.
The Name tells that it as a PUBLIC Account.
When User Login in to Applications using his username and password. These credentials (Username and Passwords) are validated by login into APPLSYSPUB Account. APPLSYSPUB account is Gateway User ID / GWYUID.
APPLSYS is same as other oracle users like AP, AR, GL etc whose hold their set of tables, views etc. Same way APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
APPS is central Schema which holds synonyms for all other Users Database Objects.
One main thing is APPLSYS and APPS should have same password. Why these contains same password.
Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc.).
Applsys schema has applications technology layer products like FND and AD etc.
Both need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.
Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login
GUEST account used by JDBC Drivers and Oracle Self Service Web Applications like istores, irecrutiment, iprocurement, ipayables, ireceivables etc to make initial Connection.
APPLSUSPUB Account is used at Initital Client Connectiviey to Oracle Applications.
The Name tells that it as a PUBLIC Account.
When User Login in to Applications using his username and password. These credentials (Username and Passwords) are validated by login into APPLSYSPUB Account. APPLSYSPUB account is Gateway User ID / GWYUID.
APPLSYS is same as other oracle users like AP, AR, GL etc whose hold their set of tables, views etc. Same way APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
APPS is central Schema which holds synonyms for all other Users Database Objects.
One main thing is APPLSYS and APPS should have same password. Why these contains same password.
Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc.).
Applsys schema has applications technology layer products like FND and AD etc.
Both need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.
Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login
Subscribe to:
Posts (Atom)