Archive

Posts Tagged ‘oracle 10g’

Oracle – Create Physical Standby Database 10g RAC


Creating a Physical Standby Database OASSTBY

Make sure database is in archivelog mode
(If it’s not then shutdown instance 2, shutdown then startup mount instance 1, enable archivelog mode, startup both instances)

node 1$  ssh node2
node 2$  sqlplus ‘/as sysdba’
SQL>  shutdown immediate
SQL>  exit
$  exit
node 1$  sqlplus ‘/as sysdba’
SQL>  shutdown immediate
SQL>  startup mount
SQL>  alter database archivelog;
SQL>  alter database open;
SQL>  exit
node 1$  ssh node2
node 2$  sqlplus ‘/as sysdba’
SQL>  startup open

Backup primary database OASLIVE

(If using ASM then you must use RMAN to create the backup)

$  export ORACLE_SID=OASLIVE1
$  rman
RMAN> connect target /
connected to target database: OASLIVE (DBID=3404061247)
RMAN> backup database plus archivelog format ‘/c04/oracle/oraback/OASLIVE/rman/OASLIVE_%U.rbak’

Copy control file from primary database for standby

RMAN> copy current controlfile for standby to '/home/oracle/OASSTBY.stby';
Copy backup file and control file to standby node (Node11)
$  cd /home/oracle/kerri
$  scp OASSTBY.stby node11:/home/oracle/.
$  cd /c04/oracle/oraback/OASLIVE/rman/
$  scp OASLIVE_*.rbak node11:/c04/oracle/oraback/OASLIVE/rman/.

Whilst waiting for the backup to copy across, create standby redo logs in the primary database OASLIVE

(Amount of standby logs per thread = no. of redo logs +1)

node 1$ sqlplus ‘/as sysdba’
SQL> select * from v$log;
SQL> select group#, thread#, sequence#, status from v$standby_log;
SQL> alter database add standby logfile thread 1 ('+LIVEDATA') size 256M;
SQL> /
SQL>  /
SQL>  /
SQL>  /
SQL>  alter database add standby logfile thread 2 ('+LIVEDATA') size 250M;
SQL>  /
SQL>  /
SQL>  /
SQL>  /
SQL> select group#, thread#, sequence#, status from v$standby_log;

Copy the server details of the standby node to the primary node tnsnames.ora

node 11$  cd $TNS_ADMIN
Copy the following from OASSTBY tnsnames.ora to OASLIVE tnsnames.ora
OASSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgoh-data-11-vip)(PORT = 1511))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = OASSTBY.dudleygoh-tr.wmids.nhs.uk)
)
)

Log into ASM and delete all pre-existing OASSTBY files

Check that both OASLIVE nodes have sysdba rman privileges if they don’t then run the following:

node 1$  sqlplus ‘/as sysdba’
SQLPLUS>  grant sysdba to rman;

Create spfile for physical standby using modified pfile (see end of post for pfile details)

node 11$  sqlplus ‘/as sysdba’
SQLPLUS>  startup nomount pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL> create spfile from pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL>  shutdown immediate
SQL>  startup nomount

Log into RMAN and recover the standby database

node 11$  rman
RMAN>  connect target rman/o45rm4n@OASLIVE
RMAN>  connect auxiliary rman/o45rm4n@OASSTBY
RMAN>  duplicate target database for standby;

Copy across the password file

$  scp orapwOASLIVE node11:/u01/oracle/ora102/dbs/orapwOASSTBY
Recover database

SQLPLUS>  alter database recover managed standby database disconnect from session;

Appendix

Physical Standby spfile details for OASSTBY:

OASSTBY1.__db_cache_size=1191182336
OASSTBY.__java_pool_size=16777216
OASSTBY1.__java_pool_size=16777216
OASSTBY.__large_pool_size=16777216
OASSTBY1.__large_pool_size=16777216
OASSTBY.__shared_pool_size=352321536
OASSTBY1.__shared_pool_size=369098752
OASSTBY.__streams_pool_size=0
OASSTBY1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/OASSTBY/adump'
*.background_dump_dest='/u01/oracle/admin/OASSTBY/bdump'
*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4.0'
*.control_files='+LIVEDATA’
*.core_dump_dest='/u01/oracle/admin/OASSTBY/cdump'
*.db_block_size=8192
*.db_create_file_dest='+LIVEDATA'
*.db_domain='dudleygoh-tr.wmids.nhs.uk'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='OASLIVE','OASSTBY'
*.db_name='OASLIVE'
*.db_unique_name='OASSTBY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OASSTBYXDB)'
*.fal_client='OASSTBY1'
*.fal_server='OASLIVE1,OASLIVE2'
OASSTBY1.instance_number=1
OASSTBY2.instance_number=2
*.job_queue_processes=1
OASSTBY2.local_listener='LISTENER_OASSTBY2'
OASSTBY1.local_listener='LISTENER_OASSTBY1'
*.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)'
*.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OASSTBY'
*.log_archive_dest_2='SERVICE=OASLIVE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASLIVE'
*.log_archive_dest_3='SERVICE=OASREP LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASREP'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.open_cursors=300
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_listener='LISTENERS_OASSTBY'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
OASSTBY2.thread=2
OASSTBY1.thread=1
*.undo_management='AUTO'
OASSTBY1.undo_tablespace='UNDOTBS1'
OASSTBY2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/oracle/admin/OASSTBY/udump'
*.utl_file_dir='*'

Oracle – Archive Log Mode

November 22, 2010 Leave a comment

Archive log mode is necessary for hot backups and point-in-time recovery to take place. By default the database is created in noarchivelog mode, however you would need to shutdown the database in order to perform a backup. Archivelog mode allows for hot backups to be run with the archive logs being included in the backup to allow for consistency.

In archivelog mode the database will make copies of completed redo logs, archive logfiles. The disadvantage of this is that until the archivelog for a redo log has been written, the redo log cannot be reused.

Parameters

The following parameters need to be set in order to use archivelog mode:

log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

Enable Archivelog Mode

You must first shutdown all instances of the database. Then startup one instance, enable archivelog mode, the restart the remaining instances.

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

Check Archivelog Mode

You can easily check if a database is running in archivelog mode by running the following:

SQL> Archive log list;

Disable Archivelog Mode

To disable archivelog mode you once again need to shutdown the database, startup mount, then disable archivelog mode:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;

Oracle – Reconfigure Oracle Enterprise Manager

November 18, 2010 2 comments

If you have already created your database and not configured it with Enterprise Manager, or you have configured your database with Enterprise Manager but for some reason need to reconfigure, you can do this from the command line using EMCA.

If you have already configured Enterprise Manager before and wish to reconfigure then you need to drop a couple of public synonyms, roles and users on the database first.

Drop Users

SQL>  drop user mgmt_view cascade;
User dropped
SQL>  drop user sysman cascade;
User dropped

NB.  If you don’t drop the sysman user you receive the following error in the log file:

CONFIG: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17 oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-20001: SYSMAN already EXISTS..
ORA-06512: at line 17

Drop Public Synonyms

SQL>  drop public synonym setenviewusercontext;
Public synonym dropped
SQL>  drop public synonym mgmt_target_blackouts;
Public synonym dropped

Drop Roles

SQL>  drop role mgmt_user; Role dopped

Create the Enterprise Manager environment

The enterprise manager environment can then be created using EMCA –config

$  emca –config dbcontrol db –repos create

EMCA will prompt for the following information:

Database SID: Listener port number:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notification (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME:
ASM SID:
ASM port:
ASM user role:
ASM username:
ASM user password:

It is possible to use an input file for the EMCA parameters using the –respFile argument to specify an input file. The input file must be in a format similar to:

PORT=1510 SID=OASPRAC DBSNMP_PWD=password SYSMAN_PWD=password

An example of using an input file can be seen below:

./emca -config dbcontrol db -respFile input_file_path

EMCA and RAC Databases

If you have a RAC database then dbconsole needs to be configured on each instance of the cluster. By default Database Control Console is only configured on the local node, however the Enterprise Manager agent is started on all other nodes.

-cluster nodename

arguments should be used for RAC databases

Sample Output for Reconfiguring DBControl for a Single-Node RAC

merlin:oracle@OASPRAC1 > emca -config dbcontrol db -repos create
STARTED EMCA at Nov 18, 2010 4:02:25 PM EM Configuration Assistant, Version 10.2.0.1.0 Production Copyright (c) 2003, 2005, Oracle.
All rights reserved.
Enter the following information:
Database SID:
OASPRAC1 Database Control is already configured for the database OASPRAC1
You have chosen to configure Database Control for managing the database OASPRAC1
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1510
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/oracle/ora102 ]: /u01/oracle/asm102
ASM SID [ +ASM ]:
ASM port [ 1510 ]:
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: -----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/oracle/ora102
Database hostname ................ merlin.smhsct.local
Listener port number ................ 1510
Database SID ................ OASPRAC1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u01/oracle/asm102
ASM SID ................ +ASM
ASM port ................ 1510
ASM user role ................ SYSDBA
ASM username ................ SYS -----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 18, 2010 4:02:59 PM
oracle.sysman.emcp.EMConfig perform INFO:
This operation is being logged at /u01/oracle/ora102/cfgtoollogs/emca/OASPRAC/emca_2010-11-18_04-02-25-PM.log.
Nov 18, 2010 4:02:59 PM
oracle.sysman.emcp.util.DBControlUtil stopOMS INFO:
Stopping Database Control (this may take a while) ...
Nov 18, 2010 4:03:01 PM
oracle.sysman.emcp.EMReposConfig createRepository INFO:
Creating the EM repository (this may take a while) ...
Nov 18, 2010 4:04:33 PM oracle.sysman.emcp.EMReposConfig invoke INFO:
Repository successfully created Nov 18, 2010 4:04:36 PM
oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO:
Securing Database Control (this may take a while) ...
Nov 18, 2010 4:13:14 PM
oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO:
Database Control secured successfully.
Nov 18, 2010 4:13:14 PM
oracle.sysman.emcp.util.DBControlUtil startOMS INFO:
Starting Database Control (this may take a while) ...
Nov 18, 2010 4:14:49 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO:
Database Control started successfully
Nov 18, 2010 4:14:49 PM
oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO:
>>>>>>>>>>>
The Database Control URL is https://merlin.smhsct.local:5501/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 18, 2010 4:14:49 PM

Oracle – Restricted Database Session

November 17, 2010 Leave a comment

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered

Find and disconnect users connected during restricted session

Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
USERNAME LOGON_TIM PROCESS
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS      17-NOV-10 1310796
8 rows selected.

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode

If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;
LOGINS
----------
RESTRICTED

Oracle – Move datafile from single-node into ASM using RMAN and recover (online mode)

November 2, 2010 Leave a comment

If you need to move a datafile from single-node to ASM then it is necessary to use RMAN to copy the datafile before renaming the datafile using SQLPLUS.

  1. Log into RMAN and copy the datafile to the required diskgroup:

    merlin:oracle@OASLIVE1 > rman
    Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 1 11:42:00 2010
    Copyright (c) 1982, 2007, Oracle. All rights reserved.
    RMAN> connect target /
    connected to target database: OASLIVE (DBID=3426612930)

    RMAN> copy datafile 19 to '+LIVEDATA';
    Starting backup at 01-NOV-10
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=122 instance=OASLIVE1 devtype=DISK
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00019 name=/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF
    output filename=+LIVEDATA/oaslive/datafile/repository.288.733923757 tag=TAG20101101T114237 recid=1 stamp=733923758
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
    Finished backup at 01-NOV-10

  2. Take the datafile offline to prevent data from being written to it whilst the renaming is commencing

    merlin:oracle@OASLIVE1 > sqlplus '/as sysdba'
    SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 1 11:46:07 2010
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

    SQL> alter database datafile 19 offline;
    Database altered.

  3. Rename the datafile

    SQL> alter database rename file '/u01/oracle/ora102/dbs/F:ORACLEORADATAOASLIVEOASLIVE_REPOS_01.DBF' to '+LIVEDATA/oaslive/datafile/repository.288.733923757';
    Database altered.

  4. If we try to put the datafile back online we will get an error message saying that the datafile requires recovery. This is because the copy of the datafile was completed whilst the datafile was still online. The following error message will occur:

    SQL> alter database datafile 19 online;
    alter database datafile 19 online
    *
    ERROR at line 1:
    ORA-01113: file 19 needs media recovery
    ORA-01110: data file 19: '+LIVEDATA/oaslive/datafile/repository.288.733923757'

  5. Recover the datafile to restore

    SQL> recover datafile 19;
    Media recovery complete.

  6. Bring the datafile back online:

    SQL> alter database datafile 19 online;
    Database altered.

Oracle – Migrate database from Oracle 9i to Oracle 10g

October 18, 2010 1 comment

If you are migrating from Oracle 9i to Oracle 10g you must use the Oracle export/import tools.  The process for migrating from Oracle 9i to Oracle 10g is to export the data from the Oracle 9i system, the transfer the files across to the 10g system. On the 10g system you must then create a 10g database. Once the 10g database has been created you must then import the data from the 9i system. After the import has completed statistics can then be gathered.

9i Preparation

  1. Check how much disk space is available, either by checking the file system, or checking how much space can be created when backups have been cleared down
  2. Check how big the datafiles are to see how big the export is likely to be, remember the export will be compressed but it is better to have more space than necessary than not enough
  3. So as not to run out of undo tablespace create the following script and store it on the 9i system:

    create table tmp_users as select * from dba_users;
    delete tmp_users;

    Do not close the session or commit the transaction whilst the export is completing. If the database export is large then there is a possibility you will run out of undo tablespace during export. If this happens you will receive the following error:

    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'

  4. For ease of use we can create an export parameter file. This file can be reused for further exports. Create the export parameter file on the 9i system:

    userid=<user>/<password>@<oracle_sid>
    log=exp.log
    full=y
    consistent=y
    filesize=5G
    file=/u02/oracle/oraback/<oracle_sid>/export/exp1.dmp

  5. Create a shell script for running the export, this should include a nohupto allow the script to run in the background. If the script runs in the foreground then the export will fail if the server window times out.

    nohup exp parfile=export.par &

  6. Query tablespaces and sizes. Take a note of the sizes of the tablespaces and datafiles in order to recreate them for use on the 10g system.

10g Preparation

  1. On the 10g system create an import shell script. This will look similar to that of the export on the 9i system:

    nohup imp parfile=import.par &

  2. Create the export parameter file on the 10g system:

    userid=<user>/<password>@<oracle_sid>
    log=import.log
    full=y
    commit=y
    feedback=50000
    buffer=10000000
    file=exp1.dmp

  3. Create a transactional database using DBCA in a VNC session
  4. Create a sql script for the creation of tablespaces and datafiles

Export Data from 9i

  1. Check backup to tape has been successful
  2. Cleardown any old backups if space is required before export
  3. Run the sql script for undo tablespace retention
  4. In a separate session start the export using the previously created export shell script
  5. Tail nohup.out to see when the export has completed

    $  tail -f nohup.out

FTP the files to the 10g server

  1. FTP the files across to the 10g server either using WinSCP (linux to linux) or using mget (windows to linux/windows)

Import to 10g

  1. In a VNC session run the import shell script created previously