create Standby Database
Architecture
Data Guard architecture incorporates the following items:
Primary Database - A production database that is used to create standby databases. The archive logs from the primary database are transferred and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
Standby Database - A replica of the primary database.
Log Transport Services - Control the automatic transfer of archive redo log files from the primary database to one or more standby destinations.
Network Configuration - The primary database is connected to one or more standby databases using Oracle Net.
Log Apply Services - Apply the archived redo logs to the standby database. The Managed Recovery Process (MRP) actually does the work of maintaining and applying the archived redo logs.
Role Management Services - Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
Data Guard Broker - Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.
The services required on the primary database are:
Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.
Archiver Process (ARCn) - One or more Archiver processes make copies of online redo logs either locally or remotely for standby databases.
Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .
The services required on the standby database are:
Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.
Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.
Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).
Managed Recovery Process (MRP) - Applies archive redo log information to the standby database
Data Guard Operational Prerequisites :
• Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• The hardware and Operating system architecture on primary and standby location must be same.
• Each primary and standby database must have its own control file.
• If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
• Primary database must be FORCE LOGGING mode.
Preparing Primary Database for Standby Database creation
Ensure the primary database in ARCHIVELOG mode
Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:/oracle/ora92/database/archive/PRIM
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
Enable database in FORCE LOGGING mode
Place primary database in FORCE LOGGING mode using following SQL statement:
SQL> alter database force logging;
Database altered.
Identify the primary database Datafiles
SQL> select name from v$datafile;
NAME
-------------------------------
/U01/ORACLE/PRIM/SYSTEM01.DBF
/U01/ORACLE/PRIM/UNDOTBS01.DBF
/U01/ORACLE/PRIM/CWMLITE01.DBF
/U01/ORACLE/PRIM/DRSYS01.DBF
/U01/ORACLE/PRIM/EXAMPLE01.DBF
/U01/ORACLE/PRIM/INDX01.DBF
/U01/ORACLE/PRIM/ODM01.DBF
/U01/ORACLE/PRIM/TOOLS01.DBF
/U01/ORACLE/PRIM/USERS01.DBF
/U01/ORACLE/PRIM/XDB01.DBF
10 rows selected.
Make Backup of Primary Database
Make a closed backup copy of primary database by performing following steps:
Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;
Copy the Data files to standby location
Copy the redo log files and Data files to standby location.
Note: Primary Database must be shutdown while coping the files.
Restart the Primary Database
Execute following command to restart the Primary Database.
SQL> startup;
Create Control file for Standby Database
Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2 ‘/u02/oracle/oradata/stby/control_sb01.ctl’;
Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.
Create pfile from for standby database from the primary database
Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.
SQL> CREATE PFILE=‘/u01/oracle/ora92/database/initstby.ora’ from spfile;
File created.
Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.
db_name - Not modified. The same name as the primary database.
compatible - Not modified. The same as the primary database, 9.2.0.0.0.
control_files - Specify the path name and filename for the standby control file.
log_archive_start - Not modified. The same as the setting for the primary database, TRUE
standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.
db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.
log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.
log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)
standby_file_management - Set to AUTO.
remote_archive_enable - Set to TRUE.
instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.
lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.
Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.
(Refer Annexure for initialization parameter settings for primary and standby database.)
Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:/>oradim -new -sid stby -intpwd stby -startmode manual
Configure listeners & tnsnames for standby and primary databases
Configure listeners in listeners.ora as follows
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u02/oracle/ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = Prim)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = Prim)
)
(SID_DESC =
(GLOBAL_DBNAME = stby)
(ORACLE_HOME = /u02/oracle/ora92)
(SID_NAME = stby)
)
)
Restart the listeners using LSNRCTL utility.
% lsnrctl stop
% lsnrctl start
Also make an entry into tnsnames.ora for standby database.
stby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)
Start Physical standby database
Start up the stand by database using following commands
C:/>set oracle_sid=stby
C:/>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile=‘/u01/oracle/ora92/database/initstby.ora’ nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
Enabling archiving to Physical Standby Database
To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.
Issue following commands from primary database session:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=‘SERVICE=stby’ SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
System altered.
Initiate Log apply services
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
Now go to primary database prompt
SQL> alter system switch logfile;
Go to stand by database prompt
SQL> alter database open read only;
Database altered.
Verifying the Standby Database
On standby database query the V$ARCHIVED_LOG view to verify that redo log received.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
Archive the current log on the primary database using following statement.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On standby database query the V$ARCHIVED_LOG view
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
-------------- ------------------- ----------------------
14 25-APR-05 16:50:34 25-APR-02 16:50:42
15 25-APR-05 16:50:42 25-APR-02 16:50:47
16 25-APR-05 16:50:47 25-APR-02 16:51:52
17 25-APR-05 16:51:52 25-APR-02 17:34:00
Now connect scott/tiger@prim on primary database and create table or insert row in any table.
Now connect as sys on primary database and execute following SQL statement
SQL> alter system switch logfile;
On standby database execute following SQL statements
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
And check whether the changes applied on the standby database or not.
Parameter file for Primary Database
*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/Prim/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u01/oracle/prim/CONTROL01.CTL’,
‘/u01/oracle/prim/CONTROL02.CTL’,’/u01/oracle/prim/CONTROL03.CTL’
*.core_dump_dest=‘/u02/oracle/admin/Prim/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘Prim’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=C:/oracle/ora92/database/archive/PRIM MANDATORY’
*.log_archive_dest_2=‘SERVICE=stby LGWR SYNC AFFIRM’
The LGWR SYNC AFFIRM keywords indicate that the Log writer should synchronously write updates to the online redo logs to this location and wait for confirmation of the write before proceeding. The remote site will process and archive these standby redo logs to keep the databases synchronized. This whole process can impact performance greatly but provides maximum data security.
*.log_archive_dest_state_2=‘ENABLE’
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘EXCLUSIVE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/Prim/udump’
Parameter file for Standby Database
*.aq_tm_processes=1
*.background_dump_dest=‘/u02/oracle/admin/stby/bdump’
*.compatible=‘9.2.0.0.0’
*.control_files=‘/u02/oracle/oradata/stby/CONTROL_SB01.CTL’
*.core_dump_dest=‘/u02/oracle/admin/stby/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=‘‘
*.db_file_multiblock_read_count=16
*.db_name=‘Prim’
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=PrimXDB)’
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name=‘stby’
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1=‘LOCATION=/u02/oracle/admin/stby/archive’
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled=‘FALSE’
*.remote_login_passwordfile=‘NONE’
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled=‘FALSE’
*.timed_statistics=TRUE
*.undo_management=‘AUTO’
*.undo_retention=10800
*.undo_tablespace=‘UNDOTBS1’
*.user_dump_dest=‘/u02/oracle/admin/stby/udump’
*.standby_archive_dest=‘/u01/oracle/ora92/database/archive/PRIM’
*.db_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.log_file_name_convert=‘/u01/oracle/prim’,’/u02/oracle/oradata/stby’
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby
Protect Primary Database
Now that Data Guard is configured and running the primary database can be prevented from applying updates unless the update has been sent to at least one standby location. Connect to the primary database and execute:
ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Cancel Managed Standby Recovery
To stop managed standby recovery:
SQL> -- Cancel protected mode on primary
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;
SQL>
SQL> -- Cancel recovery if necessary
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
The database can subsequently be switched back to recovery mode as follows:
SQL> -- Startup managed recovery
SQL> CONNECT sys/password@stby1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> -- Protect primary database
SQL> CONNECT sys/password@prim AS SYSDBA
SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;
Activating A Standby Database
If the primary database is not available the standby database can be activated as a primary database using the following statements:
SQL> -- Cancel recovery if necessary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately. The previous primary database can then be configured as a standby.
Backup Standby Database
Backups of the standby database can only be performed if the database is shut down or in read only mode. Read only mode is best for managed recovery systems as archive logs will still be transfered during the backup process, thus preventing gap sequences. Once the server is in the desired mode simply copy the appropriate database files.
Database Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements:
-- Convert primary database to standby
CONNECT sys/change_on_install@prim1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
-- Shutdown primary database
SHUTDOWN IMMEDIATE;
-- Mount old primary database as standby database
STARTUP NOMOUNT PFILE==‘/u01/oracle/ora92/database/initstby.ora’
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
-- Convert standby database to primary
CONNECT sys/change_on_install@stby1 AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- Shutdown standby database
SHUTDOWN IMMEDIATE;
-- Open old standby database as primary
STARTUP PFILE==‘/u01/oracle/ora92/database/initstby.ora’
This process has no affect on alternative standby locations. The process of converting the instances back to their original roles is known as a Switchback. The switchback is accomplished by performing another switchover.
Database Failover
Graceful Database Failover occurs when database failover causes a standby database to be converted to a primary database:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
This process will recovery all or some of the application data using the standby redo logs, therefore avoiding reinstantiation of other standby databases. If completed successfully, only the primary database will need to be reinstatiated as a standby database.
Forced Database Failover changes one standby database to a primary database. Application data may be lost neccessitating the reinstantiation of the primary and all standby databases.
Wednesday, July 30, 2008
Sunday, July 27, 2008
Clone the database using RMAN
Clone an Oracle database using RMAN duplicate (same server)
This procedure will clone a database onto the same server using RMAN duplicate.
1. Backup the source database.
To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:
rman target sys@targetdb
backup database plus archivelog format '/u01/ora_backup/rman/%d_%u_%s';
This will backup the database and archive logs. The format string defines the location of the backup files. Alter it to a suitable location.
2. Produce a pfile for the new database
This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile.
Connect to the source database as sysdba and run the following:
create pfile='init
This will create a new pfile in the $ORACLE_HOME/dbs directory.
The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary.
Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:
db_file_name_convert=(
log_file_name_convert=(
Here is an example where the source database scr9 is being cloned to dg9a. Note the trailing slashes and lack of quotes:
db_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)
log_file_name_convert=(/u01/oradata/scr9/,/u03/oradata/dg9a/)
3. Create bdump, udump & cdump directories
Create bdump, udump & cdump directories as specified in the pfile from the previous step.
4. Add a new entry to oratab, and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate why not.
5. Create a password file
Use the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=
6. Duplicate the database
From sqlplus, start the instance up in nomount mode:
startup nomount
Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it.
rman target sys@targetdb
duplicate target database to clonedb
This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'.
If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.
RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database
Once the duplicate has finished RMAN will display a message similar to this:
database opened
Finished Duplicate Db at 26-FEB-05
RMAN>
Exit RMAN.
7. Create an spfile
From sqlplus:
create spfile from pfile;
shutdown immediate
startup
Now that the clone is built, we no longer need the file_name_convert settings:
alter system reset db_file_name_convert scope=spfile sid='*'
/
alter system reset log_file_name_convert scope=spfile sid='*'
/
8. Optionally take the clone database out of archive log mode
RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
Wednesday, July 23, 2008
RMAN
Recovery Manager (RMAN)
Recovery manager is a platform independent utility for coordinating your backup and restoration procedures across multiple servers. In my opinion it's value is limited if you only have on or two instances, but it comes into it's own where large numbers of instances on multiple platforms are used. The reporting features alone mean that you should never find yourself in a position where your data is in danger due to failed backups.
CONNECT sys/password@rmandb AS SYSDBA
-- Create tablepsace to hold repository
CREATE TABLESPACE "RMAN" DATAFILE '/u01/oracle/oradata/rmandb/RMAN01.DBF' SIZE 6208K EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;
-- Create rman schema owner
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
GRANT connect, resource, recovery_catalog_owner TO rman;
Then create the recovery catalog:
C:>rman
catalog=rman/rman@rmandb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to recovery catalog databaserecovery catalog is not installed
RMAN> create catalog tablespace "RMAN";
recovery catalog created
RMAN> exit
Recovery Manager complete.
Register DatabaseEach database to be backed up by RMAN must be registered:
$rman
catalog=rman/rman@rmandb target=sys/password@test
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: test (DBID=1371963417)connected to recovery catalog database
RMAN> register database;
database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
RMAN>Existing user-created backups can be added to the catalog using:
RMAN> catalog datafilecopy '/u01/oracle/oradata/TSH1.dbf';RMAN> catalog archivelog 'log1', 'log2', 'log3', ... 'logN';
Full BackupFirst we configure several persistant parameters for this instance:
RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure channel device type disk format '/uo1\test\Backup%d_DB_%u_%s_%p';
Next we perform a complete database backup using a single command:
RMAN> run
{
2> backup database plus archivelog;
3> delete noprompt obsolete;
4> }
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the follwoing command:
RMAN> resync catalog;
Restore & Recover The Whole DatabaseIf the controlfiles and online redo logs are still present a whole database recovery can be achieved by running the following script:
run
{
shutdown immediate;
# use abort if this fails
startup mount;
restore database;
recover database;
alter database open;
}
This will result in all datafiles being restored then recovered.
RMAN will apply archive logs as necessary until the recovery is complete.
At that point the database is opened. If the tempfiles are still present you can issue a command like like the following for each of them:
sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' REUSE";
If the tempfiles are missing they must be recreated as follows:
sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";
*******************************************************************************************************************************
Backup script For NON RAC
run
{
allocate channel c1 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/rman_%d_%u_%s_%T';
release channel c1;
allocate channel c1 type disk;
backup as compressed backupset archivelog all FORMAT '/u01/rman_arch_%d_%u_%s_%T';
release channel c1;
}
Backup script For RAC
run
{
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '+DATA/rman_%d_%u_%s_%T';
release channel c1;
release channel c2;
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
backup as compressed backupset archivelog all FORMAT '+DATA/rman_arch_%d_%u_%s_%T';
release channel c1;
release channel c2;
}
Recovery manager is a platform independent utility for coordinating your backup and restoration procedures across multiple servers. In my opinion it's value is limited if you only have on or two instances, but it comes into it's own where large numbers of instances on multiple platforms are used. The reporting features alone mean that you should never find yourself in a position where your data is in danger due to failed backups.
CONNECT sys/password@rmandb AS SYSDBA
-- Create tablepsace to hold repository
CREATE TABLESPACE "RMAN" DATAFILE '/u01/oracle/oradata/rmandb/RMAN01.DBF' SIZE 6208K EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;
-- Create rman schema owner
CREATE USER rman IDENTIFIED BY rman
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;
GRANT connect, resource, recovery_catalog_owner TO rman;
Then create the recovery catalog:
C:>rman
catalog=rman/rman@rmandb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to recovery catalog databaserecovery catalog is not installed
RMAN> create catalog tablespace "RMAN";
recovery catalog created
RMAN> exit
Recovery Manager complete.
Register DatabaseEach database to be backed up by RMAN must be registered:
$rman
catalog=rman/rman@rmandb target=sys/password@test
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: test (DBID=1371963417)connected to recovery catalog database
RMAN> register database;
database registered in recovery catalogstarting full resync of recovery catalogfull resync complete
RMAN>Existing user-created backups can be added to the catalog using:
RMAN> catalog datafilecopy '/u01/oracle/oradata/TSH1.dbf';RMAN> catalog archivelog 'log1', 'log2', 'log3', ... 'logN';
Full BackupFirst we configure several persistant parameters for this instance:
RMAN> configure retention policy to recovery window of 7 days;
RMAN> configure default device type to disk;
RMAN> configure controlfile autobackup on;
RMAN> configure channel device type disk format '/uo1\test\Backup%d_DB_%u_%s_%p';
Next we perform a complete database backup using a single command:
RMAN> run
{
2> backup database plus archivelog;
3> delete noprompt obsolete;
4> }
The recovery catalog should be resyncronized on a regular basis so that changes to the database structure and presence of new archive logs is recorded. Some commands perform partial and full resyncs implicitly, but if you are in doubt you can perform a full resync using the follwoing command:
RMAN> resync catalog;
Restore & Recover The Whole DatabaseIf the controlfiles and online redo logs are still present a whole database recovery can be achieved by running the following script:
run
{
shutdown immediate;
# use abort if this fails
startup mount;
restore database;
recover database;
alter database open;
}
This will result in all datafiles being restored then recovered.
RMAN will apply archive logs as necessary until the recovery is complete.
At that point the database is opened. If the tempfiles are still present you can issue a command like like the following for each of them:
sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' REUSE";
If the tempfiles are missing they must be recreated as follows:
sql "ALTER TABLESPACE temp ADD TEMPFILE ''/u01/oracle/oradata/test/temp01.dbf'' SIZE 100M AUTOEXTEND ON NEXT 64K";
*******************************************************************************************************************************
Backup script For NON RAC
run
{
allocate channel c1 type disk;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/u01/rman_%d_%u_%s_%T';
release channel c1;
allocate channel c1 type disk;
backup as compressed backupset archivelog all FORMAT '/u01/rman_arch_%d_%u_%s_%T';
release channel c1;
}
Backup script For RAC
run
{
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '+DATA/rman_%d_%u_%s_%T';
release channel c1;
release channel c2;
allocate channel c1 type disk connect 'syst/syst@car1';
allocate channel c2 type disk connect 'syst/syst@car2';
backup as compressed backupset archivelog all FORMAT '+DATA/rman_arch_%d_%u_%s_%T';
release channel c1;
release channel c2;
}
Tuesday, July 22, 2008
Errors
How to enable autotrace?
SQL> set autotrace on
Cannot find the Session Identifier. Check PLUSTRACE role is enabled
Error enabling STATISTICS report
Action:
A PLAN_TABLE table must exist in schema and PLUSTRACE role granted beforeAUTOTRACE usage.
1. Login as system
sqlplus system/system
2. Then run following script
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
3. grant plustrace to <>;
4.Then issue following commad
grant select any dictionary to <>;
SQL> set autotrace on
Cannot find the Session Identifier. Check PLUSTRACE role is enabled
Error enabling STATISTICS report
Action:
A PLAN_TABLE table must exist in schema and PLUSTRACE role granted beforeAUTOTRACE usage.
1. Login as system
sqlplus system/system
2. Then run following script
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
3. grant plustrace to <>
4.Then issue following commad
grant select any dictionary to <>
About Crontab
Crontab(Cron Table) :
Crontab is is a file which contains the schedule of cron entries to be run and at specified times.
crontab -l : Display crontab file
crontab -e : Edit existing cron file or create if it doesn't already exist.
crontab -r : Remove crontab file
Syntax:
* * * * * command to be executed
First * -------->minutes(0-59)
Second * -------->Hour(0-23)
Third * -------->Day of Month(1-31)
Fourth * -------->Month (1-12)
Fifth * --------->Weeks (0-6) 0=sunday
Ex:
10 20 * * * ls -lrt /home/test ------------------> This command will be executed at 8.10 pm daily
Generate Log file:
10 20 * * * ls -lrt /home/test 2>&1 >> /home/test/list.log
Crontab is is a file which contains the schedule of cron entries to be run and at specified times.
crontab -l : Display crontab file
crontab -e : Edit existing cron file or create if it doesn't already exist.
crontab -r : Remove crontab file
Syntax:
* * * * * command to be executed
First * -------->minutes(0-59)
Second * -------->Hour(0-23)
Third * -------->Day of Month(1-31)
Fourth * -------->Month (1-12)
Fifth * --------->Weeks (0-6) 0=sunday
Ex:
10 20 * * * ls -lrt /home/test ------------------> This command will be executed at 8.10 pm daily
Generate Log file:
10 20 * * * ls -lrt /home/test 2>&1 >> /home/test/list.log
Friday, July 18, 2008
Cloning
Clone an Oracle database using a cold backup
This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.
Step 1. Identify and copy the database files
With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/
Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.
Stop the source database with:
shutdown immediate
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
Start the source database up again
startup
Step 2. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
create pfile='init.ora' from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.
Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.
Note. Pay particular attention to the control locations.
Step 3. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with –
Remove any lines that start with a #
Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "Test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/test/temp01.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;
Step 4. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate.
Step 5. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password =
Step 5. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
sqlplus "/ as sysdba"
@/home/oracle/cr_
It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
ORA-01113: file 1 needs media recovery
You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
ORA-27038: skgfrcre: file exists
Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.
Step 6. Perform a few checks
If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:
Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'
Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.
Step 7. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to <>
Step 8. Create a spfile
From sqlplus:
create spfile from pfile;
Step 9. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs
/
Step 10. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.
Clone an Oracle database using an online/hot backup
This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:
When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups. While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance. Ok, lets get started...
1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:
select max(first_change#) chngfrom v$archived_log
/
2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:
set lines 999
pages 999
set verify off
set feedback off
set heading off
spool begin_backup.sql
select 'alter tablespace ' tablespace_name ' begin backup;'
from dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
spool end_backup.sql
select 'alter tablespace ' tablespace_name ' end backup;' from dba_tablespaces
where contents != 'TEMPORARY' order by tablespace_name
/
spool off
3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:
@begin_backup
This will put all of the databases tablespaces into backup mode.
4. Copy the files to the new locationCopy, scp or ftp the files from the source database/machine to the target.
Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
5. Take the source database out of backup modeOnce the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2.
From sqlplus:
@end_backup
6. Copy archive logsIt is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:
alter system archive log current;
Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.
select namefrom v$archived_logwhere first_change# >= &change_noorder by name
/
Create an archive directory in the clone database.s file system and copy all of the identified logs into it.
7. Produce a pfile for the new databaseThis step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
create pfile='init.ora' from spfile;
This will create a new pfile in the $ORACLE_HOME/dbs directory.
Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.
Ensure that the archive log destination is pointing to the directory created in step 6.
8. Create the clone controlfileCreate a control file for the new database.
To do this, connect to the source database and request a dump of the current control file.
From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with --Remove any lines that start with a #Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
Make sure that you hang onto the command, it will be used later.
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called test which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE '/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1;
9. Add a new entry to oratab and source the environmentEdit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate.
10. Create the a password fileUse the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=
11. Create the new control file(s)Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
sqlplus "/ as sysdba"
@/home/oracle/cr_
If all goes to plan you will see the instance start and then the message 'Control file created'.
12. Recover and open the databaseThe archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:
recover database using backup controlfile until cancel
When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs: alter database open resetlogs;
13. Create temp filesUsing the 'ALTER TABLESPACE TEMP...' command from step 8,
create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.
14. Perform a few checksIf the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:
Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'
Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.
15. Set the databases global nameThe new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to
16. Create a spfileFrom sqlplus:
create spfile from pfile;
17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed.
If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID.
Respond with 'Y'. Once it has finished,
start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs/
18. Configure TNSAdd entries for new database in the listener.ora and tnsnames.ora as necessary.
This procedure will clone a database using a cold copy of the source database files. If a cold backup of the database is available, restore it to the new location and jump to step 2.
Step 1. Identify and copy the database files
With the source database started, identify all of the database's files. The following query will display all datafiles, tempfiles and redo logs:
set lines 100 pages 999
col name format a50
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
, (select sum(bytes) as poo
from dba_free_space) free
/
Make sure that the clone databases file-system is large enough and has all necessary directories. If the source database has a complex file structure, you might want to consider modifying the above sql to produce a file copy script.
Stop the source database with:
shutdown immediate
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
Start the source database up again
startup
Step 2. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
create pfile='init
This will create a new pfile in the $ORACLE_HOME/dbs directory.
Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.
Note. Pay particular attention to the control locations.
Step 3. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_
/
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with –
Remove any lines that start with a #
Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "Test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u03/oradata/test/temp01.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;
Step 4. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate.
Step 5. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password
Step 5. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
sqlplus "/ as sysdba"
@/home/oracle/cr_
It is quite common to run into problems at this stage. Here are a couple of common errors and solutions:
ORA-01113: file 1 needs media recovery
You probably forgot to stop the source database before copying the files. Go back to step 1 and recopy the files.
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl'
ORA-27038: skgfrcre: file exists
Double check the pfile created in step 2. Make sure the control_files setting is pointing at the correct location. If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.
Step 6. Perform a few checks
If the last step went smoothly, the database should be open. It is advisable to perform a few checks at this point:
Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'
Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.
Step 7. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to <>
Step 8. Create a spfile
From sqlplus:
create spfile from pfile;
Step 9. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs
/
Step 10. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.
Clone an Oracle database using an online/hot backup
This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:
When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups. While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance. Ok, lets get started...
1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:
select max(first_change#) chngfrom v$archived_log
/
2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:
set lines 999
pages 999
set verify off
set feedback off
set heading off
spool begin_backup.sql
select 'alter tablespace ' tablespace_name ' begin backup;'
from dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
spool end_backup.sql
select 'alter tablespace ' tablespace_name ' end backup;' from dba_tablespaces
where contents != 'TEMPORARY' order by tablespace_name
/
spool off
3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:
@begin_backup
This will put all of the databases tablespaces into backup mode.
4. Copy the files to the new locationCopy, scp or ftp the files from the source database/machine to the target.
Do not copy the control files across. Make sure that the files have the correct permissions and ownership.
5. Take the source database out of backup modeOnce the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2.
From sqlplus:
@end_backup
6. Copy archive logsIt is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:
alter system archive log current;
Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.
select namefrom v$archived_logwhere first_change# >= &change_noorder by name
/
Create an archive directory in the clone database.s file system and copy all of the identified logs into it.
7. Produce a pfile for the new databaseThis step assumes that you are using a spfile. If you are not, just copy the existing pfile.
From sqlplus:
create pfile='init
This will create a new pfile in the $ORACLE_HOME/dbs directory.
Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.
Ensure that the archive log destination is pointing to the directory created in step 6.
8. Create the clone controlfileCreate a control file for the new database.
To do this, connect to the source database and request a dump of the current control file.
From sqlplus:
alter database backup controlfile to trace as '/home/oracle/cr_
/
The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:
Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).
Remove any lines that start with --Remove any lines that start with a #Remove any blank lines in the 'CREATE CONTROLFILE' section.
Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'
Remove the line 'ALTER DATABASE OPEN RESETLOGS;'
Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file.
Make sure that you hang onto the command, it will be used later.
Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.
If the file paths are being changed, alter the file to reflect the changes.
Here is an example of how the file would look for a small database called test which isn't in archivelog mode:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "test" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE GROUP 1 '/u03/oradata/test/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/test/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/test/redo03.log' SIZE 100M
DATAFILE '/u03/oradata/test/system01.dbf',
'/u03/oradata/test/undotbs01.dbf',
'/u03/oradata/test/cwmlite01.dbf',
'/u03/oradata/test/drsys01.dbf',
'/u03/oradata/test/example01.dbf',
'/u03/oradata/test/indx01.dbf',
'/u03/oradata/test/odm01.dbf',
'/u03/oradata/test/tools01.dbf',
'/u03/oradata/test/users01.dbf',
'/u03/oradata/test/xdb01.dbf',
'/u03/oradata/test/andy01.dbf',
'/u03/oradata/test/psstats01.dbf',
'/u03/oradata/test/planner01.dbf'
CHARACTER SET WE8ISO8859P1;
9. Add a new entry to oratab and source the environmentEdit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate.
10. Create the a password fileUse the following command to create a password file (add an appropriate password to the end of it):
orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=
11. Create the new control file(s)Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:
sqlplus "/ as sysdba"
@/home/oracle/cr_
If all goes to plan you will see the instance start and then the message 'Control file created'.
12. Recover and open the databaseThe archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:
recover database using backup controlfile until cancel
When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs: alter database open resetlogs;
13. Create temp filesUsing the 'ALTER TABLESPACE TEMP...' command from step 8,
create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.
14. Perform a few checksIf the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:
Check that the database has opened with:
select status from v$instance;
The status should be 'OPEN'
Make sure that the datafiles are all ok:
select distinct status from v$datafile;
It should return only ONLINE and SYSTEM.
Take a quick look at the alert log too.
15. Set the databases global nameThe new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to
16. Create a spfileFrom sqlplus:
create spfile from pfile;
17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed.
If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.
From sqlplus:
shutdown immediate
startup mount
exit
From unix:
nid target=/
NID will ask if you want to change the ID.
Respond with 'Y'. Once it has finished,
start the database up again in sqlplus:
shutdown immediate
startup mount
alter database open resetlogs/
18. Configure TNSAdd entries for new database in the listener.ora and tnsnames.ora as necessary.
Useful Unix Commands and Scripts for DBA's
Find/Zip/Move/Delete files older than x days
find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;
find ./ -name "*.ARC" -mtime +1 -exec rm {} \;
find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;
find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/andy/;
Delete the 500 oldest files
rm -f `ls -trhead -500`
Find and grep
find ./ -exec grep -l "string" {} \;
Check all logs for ORA- errors
grep ^ORA- *log cut -f2 -d"-"cut -f1 -d:awk '{print "ORA-" $1}'sort -u
INODE:
ls -latri
find . -inum-print;
find . inum \ -exec -exec rm -i {} \;
How to remove whitespace in file from vi editor
:%s/[ctrl+v ctrl+m]//g
Get Oracle HOME from oratab:
cat /etc/oratabegrep ':N:Y'grep -v \*cut -f2 -d':'
Resync Command:
rsync -e ssh -Pazv /u01/app/oracle/admin/slice/arch/ oracle@test1:/u01/app/oracle/admin/slice/arch/
Error:
***An error occured during the file system check
***Dropping you to a shell, the system will reboot
***When you leave the shell
Give root password for maintenance
Or type Control-D to continue:
Solution:
#Give root password
#mount -o remount,rw /
#comment in /etc/fstab file
Create New Filesystem:
find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;
find ./ -name "*.ARC" -mtime +1 -exec rm {} \;
find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;
find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/andy/;
Delete the 500 oldest files
rm -f `ls -trhead -500`
Find and grep
find ./ -exec grep -l "string" {} \;
Check all logs for ORA- errors
grep ^ORA- *log cut -f2 -d"-"cut -f1 -d:awk '{print "ORA-" $1}'sort -u
INODE:
ls -latri
find . -inum
find . inum \
How to remove whitespace in file from vi editor
:%s/[ctrl+v ctrl+m]//g
Resync Command:
rsync -e ssh -Pazv /u01/app/oracle/admin/slice/arch/ oracle@test1:/u01/app/oracle/admin/slice/arch/
Error:
***An error occured during the file system check
***Dropping you to a shell, the system will reboot
***When you leave the shell
Give root password for maintenance
Or type Control-D to continue:
Solution:
#Give root password
#mount -o remount,rw /
#comment in /etc/fstab file
Create New Filesystem:
mkfs -t ext3 /dev/sde1mkdir /softwaremount -t ext3 /dev/sdb1 /softwareadd following line on /etc/fstab/dev/sdb1 /software ext3 defaults 1 1 Useful scripts for DBA
Database size script:
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum <>11
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
Show locked objects
set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username ' (' s.osuser ')' username
, s.sid ',' s.serial# sess_id
, owner '.' object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
Show which row is locked
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
/
Tablespaces >80% full
set pages 999 lines 100
col tablespace_name format a40
col "size MB" format 999999999
col "% Used" format 999
col "80%" format 999999
col "75%" format 999999
select tsu.tablespace_name
, ceil(tsu.used_mb) "size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "% Used"
, (tsu.used_mb / 100) * (20 - floor(tsf.free_mb/tsu.used_mb*100)) "80%"
, (tsu.used_mb / 100) * (25 - floor(tsf.free_mb/tsu.used_mb*100)) "75%"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4,5
/
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum <>11
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
Show locked objects
set lines 100 pages 999
col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username ' (' s.osuser ')' username
, s.sid ',' s.serial# sess_id
, owner '.' object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/
Show which row is locked
select do.object_name
, row_wait_obj#
, row_wait_file#
, row_wait_block#
, row_wait_row#
, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s
, dba_objects do
where sid=&sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
/
Tablespaces >80% full
set pages 999 lines 100
col tablespace_name format a40
col "size MB" format 999999999
col "% Used" format 999
col "80%" format 999999
col "75%" format 999999
select tsu.tablespace_name
, ceil(tsu.used_mb) "size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "% Used"
, (tsu.used_mb / 100) * (20 - floor(tsf.free_mb/tsu.used_mb*100)) "80%"
, (tsu.used_mb / 100) * (25 - floor(tsf.free_mb/tsu.used_mb*100)) "75%"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4,5
/
Thursday, July 17, 2008
Function of Oracle Server
SQL> UPDATE emp SET sal = 1000 WHERE empno = 100;
SQL> COMMIT;
Let us see what would be happen when oracle process it.
Step 1:
The user will type the above SQL statement and press enter key. This user either is connect to the database by dedicated server or shared server (MTS). If the user is using multi-threaded servers then her request will be given to a dispatcher and the dispatcher will give the request to shared server. If the user is using dedicated server then the dedicated server will be all hers. Now, her user process is talking to shared or dedicated server.
Step 2:
Now, the user’s SQL statement will be parsed and assigned an executed plan to be compiled in the Library Cache in the Shared Pool. In order the SQL statement be compiled, Oracle need to make sure its table and columns are valid and the user did not violated any security information. It goes to the Dictionary Cache known as Raw Cache to get all necessary information about the table. If there was no syntax problem and its table and columns were valid, then the SQL statement will be parsed successfully and the execution plan will be perform.
Step 3:
Now, there is no problem. The Server process fetches the record. If the data or record is in the Buffer Cache then an update process will be applied to it and the block will be marked as dirty block. Notice that before the user save the update, the before block images are in the UNDO segment. When the user executes commit statement or more than one third of the Redo Log buffer have filled out, then LGWR writes the user’s entries from the redo log buffer to the Online Redo Log files. Still the block may not been stored in the database.
In the case that the record is not in the buffer cache, the server process read the block containing the record from the datafile (disk) and places it into the buffer cache.
Step 4:
Now, the checkpoint process will be activated based on the LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This action will force DBWR or CKPT to write all dirty block in the database (datafile).
SQL> COMMIT;
Let us see what would be happen when oracle process it.
Step 1:
The user will type the above SQL statement and press enter key. This user either is connect to the database by dedicated server or shared server (MTS). If the user is using multi-threaded servers then her request will be given to a dispatcher and the dispatcher will give the request to shared server. If the user is using dedicated server then the dedicated server will be all hers. Now, her user process is talking to shared or dedicated server.
Step 2:
Now, the user’s SQL statement will be parsed and assigned an executed plan to be compiled in the Library Cache in the Shared Pool. In order the SQL statement be compiled, Oracle need to make sure its table and columns are valid and the user did not violated any security information. It goes to the Dictionary Cache known as Raw Cache to get all necessary information about the table. If there was no syntax problem and its table and columns were valid, then the SQL statement will be parsed successfully and the execution plan will be perform.
Step 3:
Now, there is no problem. The Server process fetches the record. If the data or record is in the Buffer Cache then an update process will be applied to it and the block will be marked as dirty block. Notice that before the user save the update, the before block images are in the UNDO segment. When the user executes commit statement or more than one third of the Redo Log buffer have filled out, then LGWR writes the user’s entries from the redo log buffer to the Online Redo Log files. Still the block may not been stored in the database.
In the case that the record is not in the buffer cache, the server process read the block containing the record from the datafile (disk) and places it into the buffer cache.
Step 4:
Now, the checkpoint process will be activated based on the LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This action will force DBWR or CKPT to write all dirty block in the database (datafile).
Subscribe to:
Posts (Atom)