Resize a datafile and tempfile:
SQL> alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' resize 50M;
SQL> alter database tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.01.dbf' resize 50M;
Add a datafile:
SQL> alter tablespace tst_tbspace1 add datafile '/u10/oradata/TEST/test.tst_tbspace.data.02.dbf' size 2000M;
SQL> alter tablespace tmp_tbspace add tempfile '/u09/oradata/TEST/test.tmp_tbspace.data.02.dbf' size 2000M;
Steps to move and/or rename datafile in oracle:
1. Shutdown the database and exit out of the SQL prompt - Database level step
Ex: SQL> shutdown
2. At the unix prompt copy or move the datafile from current location to new location using the OS copy (cp) command - OS level step
Ex: $ mv /u10/oradata/TEST/test.tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.data.01.dbf
you change the location and also rename the file at a time if you need to.
Ex: $ mv /u10/oradata/TEST/test.tst_tbspace.data.01.dbf /u01/oradata/TEST/test.tst_tbspace.datafile.02.dbf
3. Open a SQL session and startup mount the database - Database level step
Ex: SQL> startup mount
4. Rename the datafile using oracle rename command - Database level step
Ex: SQL> alter database rename file '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf'
to '/u01/oradata/TEST/test.tbspace.data.01.dbf';
5. Open the database - Database level step
Ex: SQL> alter database open;
(or)
SQL> shutdown
SQL> startup
Drop datafile:
SQL> alter database datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf' offline drop;
(or)
SQL> alter tablespace tst_tbspace drop datafile '/u10/oradata/TEST/test.tst_tbspace.data.01.dbf';
Monday, April 12, 2010
Rename/Copy a Table
A table in oracle can be renamed using the following syntax:
alter table table_name rename to new_table_name;
Ex: SQL> alter table dept rename to hist_dept;
Renaming a table doesn't update the oracle objects such as HTML DB, PL/SQL, Views some of which might be invalid based on their dependancy on the renamed object.
A copy of the original table can be created using the following syntax:
create table new_table_name as select * from table_name;
Ex: SQL> create hist_dept as select * from dept;
we need not necessarily need to include all the columns from the original table we can create a copy based on our choice of columns that we want to be included in the new table. The new table can be created either from one single table or multiple tables. The advantage of using CREATE TABLE....AS SELECT..(CTAS) it will create the new table with datatypes similar to old table(s).
A copy of the original table created including only the columns of our choice from a single table:
create table new_table_name as select column1, column2 from table_name;
Ex: SQL> create hist_dept as select dept_id, dept_name from dept;
A copy of the original table created including only the columns of our choice from a two tables:
create table new_table_name as select t1.column1, t2.column1, t2.column2 from table_name1 t1, table_name2 t2 where t1.column1 = t2.column1;
Ex: SQL> create hist_dept as select d.dept_id, e.emp_id, e.emp_anem from dept d, employee e where
d.dept_id = e.dept_id;
alter table table_name rename to new_table_name;
Ex: SQL> alter table dept rename to hist_dept;
Renaming a table doesn't update the oracle objects such as HTML DB, PL/SQL, Views some of which might be invalid based on their dependancy on the renamed object.
A copy of the original table can be created using the following syntax:
create table new_table_name as select * from table_name;
Ex: SQL> create hist_dept as select * from dept;
we need not necessarily need to include all the columns from the original table we can create a copy based on our choice of columns that we want to be included in the new table. The new table can be created either from one single table or multiple tables. The advantage of using CREATE TABLE....AS SELECT..(CTAS) it will create the new table with datatypes similar to old table(s).
A copy of the original table created including only the columns of our choice from a single table:
create table new_table_name as select column1, column2 from table_name;
Ex: SQL> create hist_dept as select dept_id, dept_name from dept;
A copy of the original table created including only the columns of our choice from a two tables:
create table new_table_name as select t1.column1, t2.column1, t2.column2 from table_name1 t1, table_name2 t2 where t1.column1 = t2.column1;
Ex: SQL> create hist_dept as select d.dept_id, e.emp_id, e.emp_anem from dept d, employee e where
d.dept_id = e.dept_id;
Thursday, April 08, 2010
Oracle Background Processes
Oracle Background Porcesses: To maximize performance and accommodate many users, a multiprocess Oracle Database system uses background processes. Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
Basic Oracle Background Processes:
Basic Oracle Background Processes:
- Database writer (DBWn): The database writer writes modified blocks from the database buffer cache to the datafiles. Oracle Database allows a maximum of 20 database writer processes (DBW0-DBW9 and DBWa-DBWj). The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes. The database selects an appropriate default setting for this initialization parameter or adjusts a user-specified setting based on the number of CPUs and the number of processor groups.
- Log writer (LGWR): The log writer process writes redo log entries to disk. Redo log entries are generated in the redo log buffer of the system global area (SGA). LGWR writes the redo log entries sequentially into a redo log file. If the database has a multiplexed redo log, then LGWR writes the redo log entries to a group of redo log files.
- Checkpoint (CKPT): At specific times, all modified database buffers in the system global area are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
- System monitor (SMON): The system monitor performs recovery when a failed instance starts up again. In a Real Application Clusters database, the SMON process of one instance can perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during system failure and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online.
- Process monitor (PMON): The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on the dispatcher processes (described later in this table) and server processes and restarts them if they have failed.
- Archiver (ARCn): One or more archiver processes copy the redo log files to archival storage when they are full or a log switch occurs.
- Recoverer (RECO): The recoverer process is used to resolve distributed transactions that are pending because of a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
- Dispatcher (Dnnn): Dispatchers are optional background processes, present only when the shared server configuration is used.
- Global Cache Service (LMS): In a Real Application Clusters environment, this process manages resources and provides inter-instance resource control.
Oracle Dedicated and Shared Server Processes
Oracle creates server processes whenever a user tries to connect to a database for handling all user requestss. Below are the two ways a server process can be:
Oracle databases are by default configured for dedicated server process and if you need to enable Shared server process you need to configure it manually by setting one or more initialization parameters. However a Dedicated server process is always required for:
Figure 1: Oracle Database Dedicated Server Processes
Shared Server Process:
Figure 2: Oracle Database Shared Server Processes
In Shared server configuration a client user process connect a dispatcher and the dispatcher then communicates with the oracle database for client request processing once the processing is done oracle then sends back the output or solution back to the dispatcher which in turn communicates it to the client user process. One dispatcher can serve multiple client user processes by connection pooling which can be enabled for shared server process. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.
Initialization Parameters for Shared Server:
The following initialization parameters control shared server operation:
Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured.
Determining a Value for SHARED_SERVERS and others:
The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
You can limit the number of shared servers that can be created by setting MAX_SHARED_SERVERS parameter specifies the maximum number of shared servers that can be automatically created by PMON. After you set the SHARED_SERVERS parameter oracle decides the number of shares servers that needs to be created based on how busy the system is.
The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers.
Configuring Dispatchers:
Ex: This is a typical example of setting the DISPATCHERS initialization parameter.
DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"
You can use multiple different protocols for different dispatchers within the same initialization parameter.
Ex: DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
Monitor the following views to determine the load on the dispatcher processes:
To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.
SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;
Each dispatcher is uniquely identified by a name of the form Dnnn.To shut down dispatcher D002, issue the following statement:
SQL>ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';
Monitoring Shared Server:
The following views are useful for obtaining information about your shared server configuration and for monitoring performance.
- A Dedicated Server Process, which serves only one user or one user connection
- A Shared Server Process, which server multiple users or multiple user connections
Oracle databases are by default configured for dedicated server process and if you need to enable Shared server process you need to configure it manually by setting one or more initialization parameters. However a Dedicated server process is always required for:
- To submit a batch job (for example, when a job can allow little or no idle time for the server process)
- To use Recovery Manager (RMAN) to back up, restore, or recover a database
Figure 1: Oracle Database Dedicated Server Processes
Shared Server Process:
Figure 2: Oracle Database Shared Server Processes
In Shared server configuration a client user process connect a dispatcher and the dispatcher then communicates with the oracle database for client request processing once the processing is done oracle then sends back the output or solution back to the dispatcher which in turn communicates it to the client user process. One dispatcher can serve multiple client user processes by connection pooling which can be enabled for shared server process. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.
Initialization Parameters for Shared Server:
The following initialization parameters control shared server operation:
- SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
- MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
- SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
- DISPATCHERS: Configures dispatcher processes in the shared server architecture.
- MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
- CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.
Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured.
Determining a Value for SHARED_SERVERS and others:
The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.
You can limit the number of shared servers that can be created by setting MAX_SHARED_SERVERS parameter specifies the maximum number of shared servers that can be automatically created by PMON. After you set the SHARED_SERVERS parameter oracle decides the number of shares servers that needs to be created based on how busy the system is.
The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers.
Configuring Dispatchers:
Ex: This is a typical example of setting the DISPATCHERS initialization parameter.
DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"
You can use multiple different protocols for different dispatchers within the same initialization parameter.
Ex: DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
Monitor the following views to determine the load on the dispatcher processes:
- V$QUEUE
- V$DISPATCHER
- V$DISPATCHER_RATE
To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.
SQL> SELECT NAME, NETWORK FROM V$DISPATCHER;
Each dispatcher is uniquely identified by a name of the form Dnnn.To shut down dispatcher D002, issue the following statement:
SQL>ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';
Monitoring Shared Server:
The following views are useful for obtaining information about your shared server configuration and for monitoring performance.
- V$DISPATCHER: Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.
- V$DISPATCHER_CONFIG: Provides configuration information about the dispatchers.
- V$DISPATCHER_RATE: Provides rate statistics for the dispatcher processes.
- V$QUEUE: Contains information on the shared server message queues.
- V$SHARED_SERVER: Contains information on the shared servers.
- V$CIRCUIT: Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
- V$SHARED_SERVER_MONITOR: Contains information for tuning shared server.
- V$SGA: Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.
- V$SGASTAT: Contains detailed statistical information about the SGA, useful for tuning.
- V$SHARED_POOL_RESERVED: Lists statistics to help tune the reserved pool and space within the shared pool.
All about startup migrate and startup upgrade
startup migrate:
=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472
=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958
=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415
startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC
=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472
=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958
=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415
startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC
Subscribe to:
Posts (Atom)