Creating physical standby using RMAN duplicate

Creating physical standby using RMAN duplicate without shutting down the primary


Database Name :- orcl
Primary db_unique_name :- orcl
standby db_unique_name :- stdby


1. Check if archiving enabled on primary.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

(OR)

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/oracle/product/10.2.0/oradata/orcl/archive/
Oldest online log sequence 1
Current log sequence 2

If NoArchivelog Mode,Enable Archive log Mode.

Enable Archive Log Mode.

SQL> shut immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

Now Check Archive Log,

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

(OR)

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/oracle/product/10.2.0/oradata/orcl/archive/
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2


2. Create password file if not present on primary.

3. Enable Force Logging on primary.

SQL> select FORCE_LOGGING from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select FORCE_LOGGING from v$database;

FOR
---
YES

4. Configure a Standby Redo Log on primary.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log4a.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log5a.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/home/oracle/oracle/product/10.2.0/oradata/orcl/log6a.log' size 50m;

Database altered.


5. Modify the primary initialization parameter for dataguard on primary.

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stdby)';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

System altered.

Note : Create Archive folder

SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';

System altered.

SQL>alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=stdby;

System altered.

SQL> alter system set FAL_CLIENT=orcl;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/stdby/','/home/oracle/oracle/product/10.2.0/oradata/orcl/' scope=spfile;

System altered.

6. Run the backup job at the primary by connecting to target and catalog DB(if any)

Connect to target(i.e. prim via connect string) and catalog database(if any)

$rman target / catalog RMAN/RMAN@catdb

if no catalog database,

$rman target /


run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup archivelog all format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
backup current controlfile for standby format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
}




RMAN> run
2> {
allocate channel c1 type disk;
3> 4> allocate channel c2 type disk;
5> backup database format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
6> backup archivelog all format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
7> backup current controlfile for standby format '/home/oracle/oracle/product/10.2.0/oradata/backup/%U';
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=133 devtype=DISK

allocated channel: c2
channel c2: sid=134 devtype=DISK

.....
....
.....

channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including standby control file in backupset
channel c1: starting piece 1 at 16-NOV-09
channel c1: finished piece 1 at 16-NOV-09
piece handle=/home/oracle/oracle/product/10.2.0/oradata/backup/06kugriq_1_1 tag=TAG20091116T170738 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-NOV-09
released channel: c1
released channel: c2


7. Create parameter file on primary copy it to standby and make the necessary changes,

SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstdby.ora' from spfile;

File created.


Make the necessary changes on the initstdby.ora file on standby.

db_name=orcl
db_unique_name=stdby
log_archive_config='DG_CONFIG=(orcl,stdby)'
log_archive_dest_1='LOCATION=/home/oracle/oracle/product/10.2.0/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
log_archive_dest_2='SERVICE=orcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
log_archive_dest_state_1=ENABLE
db_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
log_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/orcl/','/home/oracle/oracle/product/10.2.0/oradata/stdby/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_archive_dest='/home/oracle/oracle/product/10.2.0/oradata/stdby/arch/'
standby_file_management=AUTO
fal_client=stdby
fal_server=orcl


8. Create Password file on standby.

create the same password as that of primary. Alternatively we can copy the password file from primary on standby.

For example from primary copry the password file,

$cp orapworcl orapwstdby

9. Create necessary directories and start the auxiliary instance on standby

$mkdir udump bdump cdump adump

$export ORACLE_SID=stdby

$sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 16 17:30:41 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 176164676 bytes
Database Buffers 419430400 bytes
Redo Buffers 7163904 bytes
SQL>exit;

10. Create net services on both primary and standby,

In listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby.oracle.com)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_2)
(SID_NAME = stdby)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
)

In tnsnames.ora

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.oracle.com)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testing.xxx.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.oracle.com)
)
)

11. Use RMAN duplicate for standby on standby,

on primary,

sql> alter system archive log current;

on standby,

Connect to target(i.e. prim via connect string) and catalog database(if any) and this auxiliary database by,

$rman target /@orcl catalog RMAN/RMAN@catdb auxiliary /

if no catalog database,

$rman target sys/oracle@orcl auxiliary /


[oracle@sys109 stdby]$ rman target sys/oracle@orcl auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 16 17:33:09 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1230876740)
connected to auxiliary database: STDBY (not mounted)

RMAN> duplicate target database for standby dorecover;

Starting Duplicate Db at 16-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
set until scn 510230;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

....
....
....

media recovery complete, elapsed time: 00:00:05
Finished recover at 16-NOV-09
Finished Duplicate Db at 16-NOV-09

RMAN>exit

12 .Start the MRP(managed recovery process) on standby,

SQL> select name,db_unique_name,database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ---------------- --------------
ORCL stdby PHYSICAL STANDBY


SQL> alter database recover managed standby database disconnect;

Database altered.

13. Enable the log_archive_dest_2 on primary, which is to send the logs to standby server.

SQL>alter system set log_archive_dest_state_2=enable;

System altered.

14 . Check the standby whether it is in SYNC with primary,

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

select sequence#,applied from v$archived_log order by sequence#;

Integration Oracle Apex With Weblogic Server

Integration Oracle Apex With Weblogic Server

1. Download Oracle WebLogic

2. Goto the Admin console.

3. Choose "Deployments"

4. Click Install

5. load apex.war (available for download on the APEX Listener Download Page)

6. Choose all the defaults

7. visit : http://host:port/apex/Config and setup the db
(host:port Your weblogic server host:port example: http://localhost:7001/)

8. Recursively copy the apex/images directory to the webserver's /i

Note : I have copy apex/images folder to (/home/oracle/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/mainWebApp/i)

ORA-29540: class oracle/CDC/PublishApi does not exist

ORA-29540: class oracle/CDC/PublishApi does not exist

SQL> BEGIN
2 DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3 owner => 'cdcpub',
4 change_table_name => 'products_ct',
5 change_set_name => 'PRODUCTS_CS',
6 source_schema => 'MFCAMS',
7 source_table => 'PRODUCTS',
8 column_type_list => 'PROD_ID NUMBER(6), PROD_NAME VARCHAR2(50),PROD_LIST_PRICE NUMBER(8,2),JOB_ID VARCHAR2(10), DEPARTMENT_ID NUMBER(4)',
9 capture_values => 'both',
10 rs_id => 'y',
11 row_id => 'y',
12 user_id => 'y',
13 timestamp => 'y',
14 object_id => 'n',
15 source_colmap => 'n',
16 target_colmap => 'y',
17 options_string => 'TABLESPACE USER_DATA');
18 END;
19 /
BEGIN
*
ERROR at line 1:
ORA-29540: class oracle/CDC/PublishApi does not exist
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions

1.Run the sql rmcdc.sql

SQL>$ORACLE_HOMErdbms/admin/rmcdc.sql

2. Run the sql initcdc.sql

SQL>$ORACLE_HOME/rdbms/admin/initcdc.sql

ORA-29538: Java not installed

ORA-29538: Java not installed

ERROR at line 1:
ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Solutions


$ORACLE_HOME/javavm/install

To load this package into the database, go to the directory location and login to SQL*Plus as the "SYS" user. Then, type the following:

SQL> spool initjvm.log
SQL> @initjvm.sql

Install and configure Java Development Kit 6 (jdk 6) on Oracle Enterprise Linux.

Download Java Development Kit 6

You can download JDK 6 versions from e.g. Sun's web site.

Select "Accept License Agreement".
Then select "Linux self-extracting file" for Linux Platform.
Save the file "jdk-6--linux-i586.bin" to e.g . /usr/local or

Install JDK or JRE

# cd /usr/local
# chmod +x jdk-6-linux-i586.bin
# ./jdk-6-linux-i586.bin
... ...
Do you agree to the above license terms? [yes or no]
yes

Press the spacebar to read Sun Microsystems, Inc. Binary Code License Agreement, then input "yes" and press Enter.
... ...
... ...
Done.

Set the environment variables

You can add the following lines to /etc/profile or .bash_profile file in your home directory. And then run "source /etc/profile" or ". .bash_profile" so that the environment variables can take effect.


export JAVA_HOME=/usr/local/jdk1.6.0
export PATH=$JAVA_HOME/bin:$PATH

Check the JDK version

# java -version
java version "1.6.0"
Java(TM) SE Runtime Environment (build 1.6.0-b105)
Java HotSpot(TM) Client VM (build 1.6.0-b105, mixed mode, sharing)

Transprotable Tablespace using export/import

Transprotable Tablespace using export/import

Source

1. Create tablespace.

SQL> CREATE TABLESPACE TT_DATA
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA' SIZE 50M;

SQL> CREATE TABLESPACE TT_INDEX
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA' SIZE 50M;


2. Create User.

SQL> create user ttbs identified by ttbs default tablespace tt_data temporary tablespace temp;

SQL> grant connect, resource to ttbs;

SQL> alter user ttbs quota unlimited on tt_index;


SQL> select name from v$tablespace where name like 'TT_%';

NAME
------------------------------
TT_DATA
TT_INDEX


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';


TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA NO ONLINE
TT_INDEX NO ONLINE


3. Create table and index

SQL> create table ttbs.testtable (sno number, name varchar2(10)) tablespace tt_data;

SQL> insert into ttbs.testtable values (1001, 'Doyensys');

SQL> commit;

SQL> create index ttbs.i_testtable on ttbs.testtabale(sno) tablespace tt_index;



SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1


4.Source tablespace change read-only mode.

SQL>alter tablespace TT_DATA read only;

SQL>alter tablespace TT_INDEX only;


SQL> execute dbms_tts.transport_set_check('TT_DATA,TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected


select file_name from dba_data_files where tablespace_name in ('TT_DATA','TT_INDEX');


SQL> select file_name from dba_data_files
where tablespace_name in ('TT_DATA','TT_INDEX');

FILE_NAME
--------------------------------------------------------------------------------

F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA
F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA


5. Export the metadata using transportable tablespace

exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=TT_DATA,TT_INDEX statistics=none


6. Copy TT_DATA01.ORA,TT_INDEX01.ORA datafiles and exp_tts.dmp to target server.

7 . Once copy is complete,Put source tablespace read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;



Target


7. create user.

create user ttbs identified by ttbs default tablespace user_data temporary tablespace temp;

grant connect, resource to ttbs;

8. import metadata


imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=ttbs touser=ttbs transport_tablespace=y datafiles=F:\oracle\oradata\orcl\TT_DATA01.ORA,F:\oracle\oradata\orcl\TT_INDEX01.ORA


SQL>select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES READ ONLY
TT_INDEX YES READ ONLY

9. target tablespace put read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
2 where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES ONLINE
TT_INDEX YES ONLINE


SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1

Create Recovery catalog using RMAN

Create Recovery catalog using RMAN


1. Create tablespace for Catalog

CREATE TABLESPACE rman_data
DATAFILE '/u01/oracle/product/oradata/orcl/rman_data01.dbf' SIZE 50M;

2. Create user for RMAN

CREATE USER rman
IDENTIFIED BY rman
DEFAULT TABLESPACE rman_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON rman_data;

3. Grant user.

GRANT create session TO rman;
GRANT recovery_catalog_owner TO rman;

4. create catalog

rman catalog rman/rman

RMAN> create catalog tablespace rman_data;


5. Register target database.

rman target / catalog rman/rman@rman_cat_db

RMAN> register database;