Recently I was running a RMAN restore from a NFS mounted filesystem (/d06) on a Sun Solaris server and hitting the following error --
channel t3: reading from backup piece /d06/BACKUP/PWTEST/FULL0_0djgp08h_1_1.19May2008.1151
ORA-19870: error reading backup piece /d06/BACKUP/PWTEST/FULL0_0cjgovuv_1_1.19May2008.1151
ORA-19505: failed to identify file "/d06/BACKUP/PWTEST/FULL0_0cjgovuv_1_1.19May2008.1151"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 2
channel t4: starting datafile backupset restore
channel t4: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +PWTESTRP_DATA
restoring datafile 00010 to +PWTESTRP_DATA
channel t4: reading from backup piece /d06/BACKUP/PWTEST/FULL0_0ejgp0ho_1_1.19May2008.1151
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
Remounting the filesystem with the correct options fixed the issue -
# umount /d06
# mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 db01-pwprd.ctn:/d06 /d06
Thursday, August 7, 2008
Monday, May 19, 2008
Creating a RAC database using standard Sql*plus
I'm writing this to avoid the hassle of using DBCA for creating a new RAC database. I started off by creating a non-RAC database (on one of the nodes in the RAC cluster, preferably node 1) using the conventional "create database" command. I then converted that to a RAC instance by setting RAC specific settings and finally registered the instance w/ the cluster.
Create a non-RAC database
Create a pfile
1) Create a pfile for a non-RAC database on node 1 (you may keep RAC specific parameters also in it since it will be later converted to a RAC database)
Here's a sample pfile (notice that I commented out the cluster_database and undo_tablespace parameters but left other RAC specific parameter on) --
PLINKTST2.__db_cache_size=4395630592
PLINKTST1.__db_cache_size=4395630592
PLINKTST2.__java_pool_size=16777216
PLINKTST1.__java_pool_size=16777216
PLINKTST2.__large_pool_size=16777216
PLINKTST1.__large_pool_size=16777216
PLINKTST2.__shared_pool_size=905969664
PLINKTST1.__shared_pool_size=905969664
PLINKTST2.__streams_pool_size=0
PLINKTST1.__streams_pool_size=0
##*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4'
*.db_block_size=8192
*.db_create_online_log_dest_1='+PLINKTST_DATA'
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='PLINKTST'
*.db_unique_name='PLINKTST'
*.db_create_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest_size=53687091200
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=+PWTEST_DATA'
*.log_buffer=20971520
*.open_cursors=300
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=500M
*.processes=100
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sga_max_size=1G
*.sga_target=1G
*.star_transformation_enabled='TRUE'
*.undo_management='AUTO'
*.undo_retention=3600
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
#PLINKTST1.undo_tablespace='UNDOTBS1'
#PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
*.user_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/udump' *.control_files='+PWTEST_DATA/plinktst/controlfile/plinktst.ctl' *.audit_file_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/adump' *.background_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/bdump' *.core_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/cdump' *.fal_server=PLINKTST
*.fal_client=PLINKTST
*.standby_file_management=AUTO
[Note: Use a node# in the instance name parameter (e.g. PLINKTST1) since it would later be converted into a RAC instance]
Startup nomount
PLINKTST1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_PLINKTST1'
PLINKTST1 >
Well, this needs a TNS entry for the local listener listed above (we can worry about the actual listener later - all it needs now is resolving the name LISTENER_PLINKTST1.
Open up the tnsnames.ora and add the following lines --
LISTENER_PLINKTST1 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)) )
and start the instance.
PLINKTST1 > startup nomount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
PLINKTST1 >
Create the database
If we forgot to remove the "cluster_database" parameter earlier, we'll see an error while creating database as shown below.
PLINKTST1 > create database;
create database
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
PLINKTST1 > show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
PLINKTST1 >
Remove the parameter from the pfile; startup nomount and execute the create database again.
Once the database is created, execute all the usual good stuff - catalog, catproc, pupbld scripts (all under/rdbms/admin)
Converting into RAC instance
Add separate Undo Tablespaces
First, Create 2 UNDO tablespaces for the 2 instances.
PLINKTST1 > create undo tablespace UNDOTBS1 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
PLINKTST1 > create undo tablespace UNDOTBS2 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
Add new REDO thread and enable it
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)
PLINKTST1 > alter database add logfile thread 2 group 10 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database add logfile thread 2 group 11 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database enable public thread 2;
Database altered.
RAC Specific parameters
Add all RAC specific parameters to the pfile (I simply uncommented the undo_tablespaces and the cluster_data parms as I already included those). If the RAC specific were not included earlier in the pfile, add those now. Here are the main ones -- *.cluster_database=true
*.cluster_database_instances=2
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
PLINKTST1.undo_tablespace='UNDOTBS1'
PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
Start the additional Listeners
We added a TNS entry for the LOCAL_LISTENER earlier. Lets start a new listener at that port (1522 in this case).
Update the following files::
listener.ora
----------------
LISTENER_PLINKTST_RPTDB01-PWPRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_PLINKTST_DB01-PWPRD =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /d01/app/pwprd/product/10.2.0/pwprddb)
(PROGRAM = extproc)
)
Start the above listener. Also, add the TNS entry for the new database.
tnsnames.ora
--------------------
PLINKTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb02-pwprd)(PORT = 1522))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLINKTST)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Shared pfile
Move the pfile to a shared location in ASM; restart instance 1; create all the appropriate directories on node2 and start the instance 2.
If you get the following error, make sure you've created the additional thread and enabled it (see the section "Add new REDO thread and enable it") --
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
ORA-01620: no public threads are available for mounting
..
..
..
From node 1:
PLINKTST1 > alter database enable public thread 2;
Database altered.
..
The database should come up ok now.
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
Database mounted.
Database opened.
PLINKTST2 >
This completes create a RAC database using SQL*Plus. Next we should register all the resources with CRS.
Registering with CRS
$ srvctl add database -d PLINKTST -o /d01/app/pwprd/product/10.2.0/pwprddb
$ srvctl add instance -d PLINKTST -i PLINKTST1 -n rptdb01-pwprd
$ srvctl add instance -d PLINKTST -i PLINKTST2 -n rptdb02-pwprd
$ srvctl add service -d PLINKTST -s PLINKTSTSVC -r "PLINKTST1,PLINKTST2"
Check for all the services just registered.
$ . oraenv
ORACLE_SID = [PLINKTST1] ? CRS
$ crs_stat |grep PLINK
NAME=ora.PLINKTST.PLINKTST1.inst
NAME=ora.PLINKTST.PLINKTST2.inst
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST1.srv
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST2.srv
NAME=ora.PLINKTST.PLINKTSTSVC.cs
NAME=ora.PLINKTST.db
All the new resources have been registered with CRS.
Create a non-RAC database
Create a pfile
1) Create a pfile for a non-RAC database on node 1 (you may keep RAC specific parameters also in it since it will be later converted to a RAC database)
Here's a sample pfile (notice that I commented out the cluster_database and undo_tablespace parameters but left other RAC specific parameter on) --
PLINKTST2.__db_cache_size=4395630592
PLINKTST1.__db_cache_size=4395630592
PLINKTST2.__java_pool_size=16777216
PLINKTST1.__java_pool_size=16777216
PLINKTST2.__large_pool_size=16777216
PLINKTST1.__large_pool_size=16777216
PLINKTST2.__shared_pool_size=905969664
PLINKTST1.__shared_pool_size=905969664
PLINKTST2.__streams_pool_size=0
PLINKTST1.__streams_pool_size=0
##*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4'
*.db_block_size=8192
*.db_create_online_log_dest_1='+PLINKTST_DATA'
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='PLINKTST'
*.db_unique_name='PLINKTST'
*.db_create_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest_size=53687091200
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=+PWTEST_DATA'
*.log_buffer=20971520
*.open_cursors=300
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=500M
*.processes=100
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sga_max_size=1G
*.sga_target=1G
*.star_transformation_enabled='TRUE'
*.undo_management='AUTO'
*.undo_retention=3600
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
#PLINKTST1.undo_tablespace='UNDOTBS1'
#PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
*.user_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/udump' *.control_files='+PWTEST_DATA/plinktst/controlfile/plinktst.ctl' *.audit_file_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/adump' *.background_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/bdump' *.core_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/cdump' *.fal_server=PLINKTST
*.fal_client=PLINKTST
*.standby_file_management=AUTO
[Note: Use a node# in the instance name parameter (e.g. PLINKTST1) since it would later be converted into a RAC instance]
Startup nomount
PLINKTST1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_PLINKTST1'
PLINKTST1 >
Well, this needs a TNS entry for the local listener listed above (we can worry about the actual listener later - all it needs now is resolving the name LISTENER_PLINKTST1.
Open up the tnsnames.ora and add the following lines --
LISTENER_PLINKTST1 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)) )
and start the instance.
PLINKTST1 > startup nomount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
PLINKTST1 >
Create the database
If we forgot to remove the "cluster_database" parameter earlier, we'll see an error while creating database as shown below.
PLINKTST1 > create database;
create database
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
PLINKTST1 > show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
PLINKTST1 >
Remove the parameter from the pfile; startup nomount and execute the create database again.
Once the database is created, execute all the usual good stuff - catalog, catproc, pupbld scripts (all under
Add separate Undo Tablespaces
First, Create 2 UNDO tablespaces for the 2 instances.
PLINKTST1 > create undo tablespace UNDOTBS1 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
PLINKTST1 > create undo tablespace UNDOTBS2 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
Add new REDO thread and enable it
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)
PLINKTST1 > alter database add logfile thread 2 group 10 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database add logfile thread 2 group 11 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database enable public thread 2;
Database altered.
RAC Specific parameters
Add all RAC specific parameters to the pfile (I simply uncommented the undo_tablespaces and the cluster_data parms as I already included those). If the RAC specific were not included earlier in the pfile, add those now. Here are the main ones -- *.cluster_database=true
*.cluster_database_instances=2
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
PLINKTST1.undo_tablespace='UNDOTBS1'
PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
Start the additional Listeners
We added a TNS entry for the LOCAL_LISTENER earlier. Lets start a new listener at that port (1522 in this case).
Update the following files::
listener.ora
----------------
LISTENER_PLINKTST_RPTDB01-PWPRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_PLINKTST_DB01-PWPRD =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /d01/app/pwprd/product/10.2.0/pwprddb)
(PROGRAM = extproc)
)
Start the above listener. Also, add the TNS entry for the new database.
tnsnames.ora
--------------------
PLINKTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb02-pwprd)(PORT = 1522))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLINKTST)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Shared pfile
Move the pfile to a shared location in ASM; restart instance 1; create all the appropriate directories on node2 and start the instance 2.
If you get the following error, make sure you've created the additional thread and enabled it (see the section "Add new REDO thread and enable it") --
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
ORA-01620: no public threads are available for mounting
..
..
..
From node 1:
PLINKTST1 > alter database enable public thread 2;
Database altered.
..
The database should come up ok now.
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
Database mounted.
Database opened.
PLINKTST2 >
This completes create a RAC database using SQL*Plus. Next we should register all the resources with CRS.
Registering with CRS
$ srvctl add database -d PLINKTST -o /d01/app/pwprd/product/10.2.0/pwprddb
$ srvctl add instance -d PLINKTST -i PLINKTST1 -n rptdb01-pwprd
$ srvctl add instance -d PLINKTST -i PLINKTST2 -n rptdb02-pwprd
$ srvctl add service -d PLINKTST -s PLINKTSTSVC -r "PLINKTST1,PLINKTST2"
Check for all the services just registered.
$ . oraenv
ORACLE_SID = [PLINKTST1] ? CRS
$ crs_stat |grep PLINK
NAME=ora.PLINKTST.PLINKTST1.inst
NAME=ora.PLINKTST.PLINKTST2.inst
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST1.srv
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST2.srv
NAME=ora.PLINKTST.PLINKTSTSVC.cs
NAME=ora.PLINKTST.db
All the new resources have been registered with CRS.
Tuesday, May 13, 2008
Moving SPFILE and CONTROLFILE from cooked FileSystem to ASM
You have a standby database (or any database for that matter) and it uses a pfile sitting under a non-standard directory (like your home directory). Ditto for controlfile. Now you plan to move the pfile to an spfile (under ASM) and the controlfile from your cooked filesystem to ASM. It seems rather easy, but I found something rather interesting while doing this exercise.
First, I used the standard syntax for creating the spfile --
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile;
File created.
All is well, and I could see a new spfile created under ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR] > ls -lt PARAMETERFILE/
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAY 13 13:00:00 Y spfile.325.654606981
I also created an alias for this spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.65460698 spfilePWPRDDR.ora
And reference this alias in the init file under dbs directory.
echo "spfile='+PWPRD_DATA/PWPRDDR/spfilePWPRDDR.ora'" > $ORACLE_HOME/dbs/initPWPRDDR1.ora
(notice I'm doing this for a RAC standby)
Finally, I tried starting the database in nomount mode --
PWPRDDR1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887'
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-15012: ASM file '+PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887' does not exist
ORA-06512: at line 4
Oops! what went wrong? I spent some time redoing the steps but every time it resulted in the same error.
Well, the mistake I was doing was that the database was started using a pfile sitting under non-standard location (my work area folder). The syntax for spfile creation needs to know that.
The simple fix therefore was:
1) I restarted the database (mount mode).
PWPRDDR1 > startup nomount pfile=/home/pwprd/ravi/initPWPRDDR.ora
2) Created an spfile using the following syntax.
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile='/home/pwprd/ravi/initPWPRDDR.ora';
File created.
3) Removed and (re)created the alias in ASM (asmcmd) pointing to the new spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > rmalias spfilePWPRDDR.ora
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.654614871 spfilePWPRDDR.ora
4) Finally started the instance (without using the pfile this time).
PWPRDDR1 > startup nomount
The next task is to move the controlfile into ASM. This was rather straight -- My current controlfile is sitting under /home/pwprd/ravi.
PWPRDDR1 > show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/pwprd/ravi/stby_PWPRD.ctl
From a different session, while the database (standby) is still mounted, I create a backup of the controlfile using RMAN.
RMAN> copy current controlfile to '+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl';
Starting backup at 13-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+PWPRD_DATA/pwprddr/controlfile/control01.ctl tag=TAG20080513T140358 recid=138 stamp=654617041
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-MAY-08
[Note: You may also use DBMS_FILE_TRANSFER.COPY_FILE procedure to copy files. For doing that, you'd need to create 2 directory objects and copy the file. For example, to copy the above controlfile -
SQL > create directory D1 as '/home/pwprd/ravi';
SQL > create directory D2 as '+PWPRD_DATA/PWPRDDR/CONTROLFILE';
SQL > exec dbms_file_transfer.copy_file('D1','stby_PWPRD.ctl','D2', 'control01.ctl');
]
I can now see the backup and an alias created to that inside ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] > ls -lt
Type Redund Striped Time Sys Name
N control01.ctl => +PWPRD_DATA/PWPRDDR/CONTROLFILE/Backup.324.654617039
CONTROLFILE UNPROT FINE MAY 13 14:00:00 Y Backup.324.654617039
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] >
I change the control_files parameter in the spfile.
ALTER SYSTEM SET control_files='+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl' SCOPE=SPFILE;
And simply bounce the instance. Indeed, that was rather straight.
First, I used the standard syntax for creating the spfile --
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile;
File created.
All is well, and I could see a new spfile created under ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR] > ls -lt PARAMETERFILE/
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAY 13 13:00:00 Y spfile.325.654606981
I also created an alias for this spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.65460698 spfilePWPRDDR.ora
And reference this alias in the init file under dbs directory.
echo "spfile='+PWPRD_DATA/PWPRDDR/spfilePWPRDDR.ora'" > $ORACLE_HOME/dbs/initPWPRDDR1.ora
(notice I'm doing this for a RAC standby)
Finally, I tried starting the database in nomount mode --
PWPRDDR1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887'
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.325.654601887
ORA-15056: additional error message
ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-17503: ksfdopn:2 Failed to open file +PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887
ORA-15012: ASM file '+PWPRD_DATA/pwprddr/parameterfile/spfile.325.654601887' does not exist
ORA-06512: at line 4
Oops! what went wrong? I spent some time redoing the steps but every time it resulted in the same error.
Well, the mistake I was doing was that the database was started using a pfile sitting under non-standard location (my work area folder). The syntax for spfile creation needs to know that.
The simple fix therefore was:
1) I restarted the database (mount mode).
PWPRDDR1 > startup nomount pfile=/home/pwprd/ravi/initPWPRDDR.ora
2) Created an spfile using the following syntax.
PWPRDDR1 > create spfile='+PWPRD_DATA' from pfile='/home/pwprd/ravi/initPWPRDDR.ora';
File created.
3) Removed and (re)created the alias in ASM (asmcmd) pointing to the new spfile.
ASMCMD [+PWPRD_DATA/PWPRDDR] > rmalias spfilePWPRDDR.ora
ASMCMD [+PWPRD_DATA/PWPRDDR] > mkalias +PWPRD_DATA/PWPRDDR/PARAMETERFILE/spfile.326.654614871 spfilePWPRDDR.ora
4) Finally started the instance (without using the pfile this time).
PWPRDDR1 > startup nomount
The next task is to move the controlfile into ASM. This was rather straight -- My current controlfile is sitting under /home/pwprd/ravi.
PWPRDDR1 > show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /home/pwprd/ravi/stby_PWPRD.ctl
From a different session, while the database (standby) is still mounted, I create a backup of the controlfile using RMAN.
RMAN> copy current controlfile to '+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl';
Starting backup at 13-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+PWPRD_DATA/pwprddr/controlfile/control01.ctl tag=TAG20080513T140358 recid=138 stamp=654617041
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 13-MAY-08
[Note: You may also use DBMS_FILE_TRANSFER.COPY_FILE procedure to copy files. For doing that, you'd need to create 2 directory objects and copy the file. For example, to copy the above controlfile -
SQL > create directory D1 as '/home/pwprd/ravi';
SQL > create directory D2 as '+PWPRD_DATA/PWPRDDR/CONTROLFILE';
SQL > exec dbms_file_transfer.copy_file('D1','stby_PWPRD.ctl','D2', 'control01.ctl');
]
I can now see the backup and an alias created to that inside ASM.
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] > ls -lt
Type Redund Striped Time Sys Name
N control01.ctl => +PWPRD_DATA/PWPRDDR/CONTROLFILE/Backup.324.654617039
CONTROLFILE UNPROT FINE MAY 13 14:00:00 Y Backup.324.654617039
ASMCMD [+PWPRD_DATA/PWPRDDR/CONTROLFILE] >
I change the control_files parameter in the spfile.
ALTER SYSTEM SET control_files='+PWPRD_DATA/PWPRDDR/CONTROLFILE/control01.ctl' SCOPE=SPFILE;
And simply bounce the instance. Indeed, that was rather straight.
Monday, November 12, 2007
Resync RAC standby database using ASM
If a RAC standby database using ASM (and also standby logfiles in our case) stays down for a little while, it will likely go behind the primary and under ASM of the standby cluster you'll see these "gaps" in sequence numbers --
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_652.618.638445679
thread_1_seq_654.619.638466159
thread_1_seq_656.620.638466307
thread_1_seq_658.623.638467267
thread_1_seq_659.624.638467413
thread_1_seq_660.625.638467431
thread_2_seq_262.616.638416837
thread_2_seq_264.617.638445669
thread_2_seq_265.621.638467091
thread_2_seq_266.622.638467229
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12]
Notice that thread 1 (node 1) is missing sequences 653, 655 and 657. Similarly thread 2 is missing sequence#263.
Question is how to bring these missing archivelogs from ASM@primary cluster (they are still sitting on disk @primary)?
Solution: I use a 3-step process there
step 1: Use RMAN "copy" command to copy these to a cooked filesystem on the primary cluster
step 2: Scp the restored (copied) archivelogs to the standby cluster
step 3: Recover (automatic) standby database and supply the scp'ed archivelog under cooked FS.
First, query the ASM@primary to get the exact name/location of these archives --
ASMCMD [+FRA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_650.768.638416833
thread_1_seq_651.771.638416841
thread_1_seq_652.781.638445663
thread_1_seq_653.796.638465819
thread_1_seq_654.797.638466159
thread_1_seq_655.798.638466189
thread_1_seq_656.800.638466307
thread_1_seq_657.801.638466455
thread_2_seq_262.769.638416835
thread_2_seq_263.770.638416841
thread_2_seq_264.782.638445665
thread_2_seq_265.799.638466191
step 1: Execute the RMAN copy command (primary cluster). Set the env to the database first.
RMAN> copy archivelog '+FRA/prod/archivelog/2007_11_12/thread_2_seq_263.770.638416841' to '/u02/backup/PROD/thread_2_seq_263.770.638416841';
Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1057 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=2 sequence=263 recid=1412 stamp=638416840
output filename=/u02/backup/PROD/thread_2_seq_263.770.638416841 recid=1426 stamp=638465672
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638465673.795.638465673 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_653.796.638465819' to '/u02/backup/PROD/thread_1_seq_653.796.638465819';
Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1081 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=653 recid=1427 stamp=638465819
output filename=/u02/backup/PROD/thread_1_seq_653.796.638465819 recid=1435 stamp=638466707
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466708.802.638466709 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_655.798.638466189' to '/u02/backup/PROD/thread_1_seq_655.798.638466189';
Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=655 recid=1430 stamp=638466189
output filename=/u02/backup/PROD/thread_1_seq_655.798.638466189 recid=1436 stamp=638466745
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466746.803.638466747 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_657.801.638466455' to '/u02/backup/PROD/thread_1_seq_657.801.638466455';
Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=657 recid=1434 stamp=638466454
output filename=/u02/backup/PROD/thread_1_seq_657.801.638466455 recid=1437 stamp=638466777
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466778.804.638466779 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> quit
Recovery Manager complete.
/u02/backup/PROD: ls -lt|more
total 47463696
-rw-r----- 1 oracle oinstall 32768 Nov 12 15:52 thread_1_seq_657.801.638466455
-rw-r----- 1 oracle oinstall 17920 Nov 12 15:52 thread_1_seq_655.798.638466189
-rw-r----- 1 oracle oinstall 15824384 Nov 12 15:51 thread_1_seq_653.796.638465819
-rw-r----- 1 oracle oinstall 1024 Nov 12 15:50 thread_2_seq_263.770.638416841
...
...
step2: Scp the restored archivelogs to the standby server (bcp1 is node 1 of our standby cluster)
/u02/backup/PROD: scp thread_2_seq_263.770.638416841 bcp1:/u02/backup/PROD/.
thread_2_seq_263.770.638416841
/u02/backup/PROD: scp thread_1_seq_653.796.638465819 bcp1:/u02/backup/PROD/.
thread_1_seq_653.796.638465819 100% 15MB 15.1MB/s 00:01
/u02/backup/PROD: scp thread_1_seq_655.798.638466189 bcp1:/u02/backup/PROD/.
thread_1_seq_655.798.638466189 100% 18KB 17.5KB/s 00:00
/u02/backup/PROD: scp thread_1_seq_657.801.638466455 bcp1:/u02/backup/PROD/.
thread_1_seq_657.801.638466455
step 3: Recover the standby database using the scp'ed archive logs.
PROD1_SQL > recover automatic standby database;
ORA-00279: change 98980021976 generated at 11/12/2007 02:00:34 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_263_631792716.dbf
ORA-00280: change 98980021976 for thread 2 is in sequence #263
ORA-00278: log file '+DATA/prod/2_262_631792716.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_263.770.638416841
RA-00279: change 98980621191 generated at 11/12/2007 10:01:02 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_653_631792716.dbf
ORA-00280: change 98980621191 for thread 1 is in sequence #653
ORA-00278: log file '+DATA/prod/1_653_631792716.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '+DATA/prod/1_653_631792716.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/1_653_631792716.dbf
ORA-15173: entry '1_653_631792716.dbf' does not exist in directory 'prod'
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_653.796.638465819
ORA-00279: change 98980621191 generated at 11/12/2007 02:00:40 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_264_631792716.dbf
ORA-00280: change 98980621191 for thread 2 is in sequence #264
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 98980621195 generated at 11/12/2007 10:01:04 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_265_631792716.dbf
ORA-00280: change 98980621195 for thread 2 is in sequence #265
ORA-00278: log file '+DATA/prod/2_264_631792716.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_265.799.638466191
ORA-00279: change 98981521197 generated at 11/12/2007 15:36:59 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_654_631792716.dbf
ORA-00280: change 98981521197 for thread 1 is in sequence #654
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_653.796.638465819' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 98981522036 generated at 11/12/2007 15:42:38 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_655_631792716.dbf
ORA-00280: change 98981522036 for thread 1 is in sequence #655
ORA-00278: log file '+DATA/prod/1_654_631792716.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_655.798.638466189
ORA-00279: change 98981522129 generated at 11/12/2007 15:43:09 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_656_631792716.dbf
ORA-00280: change 98981522129 for thread 1 is in sequence #656
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_655.798.638466189' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 98981522138 generated at 11/12/2007 15:43:11 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_266_631792716.dbf
ORA-00280: change 98981522138 for thread 2 is in sequence #266
ORA-00278: log file '/u02/backup/PROD/thread_2_seq_265.799.638466191' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_2_seq_266.806.638467227
ORA-00279: change 98981577931 generated at 11/12/2007 15:45:07 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_657_631792716.dbf
ORA-00280: change 98981577931 for thread 1 is in sequence #657
ORA-00278: log file '+DATA/prod/1_656_631792716.dbf' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/backup/PROD/thread_1_seq_657.801.638466455
ORA-00279: change 98981578237 generated at 11/12/2007 15:47:34 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_658_631792716.dbf
ORA-00280: change 98981578237 for thread 1 is in sequence #658
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_657.801.638466455' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
PROD1_SQL > recover managed standby database using current logfile disconnect from session;
Media recovery complete.
This completes the recovery process for the standby database and fills in the gaps.
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_652.618.638445679
thread_1_seq_654.619.638466159
thread_1_seq_656.620.638466307
thread_1_seq_658.623.638467267
thread_1_seq_659.624.638467413
thread_1_seq_660.625.638467431
thread_2_seq_262.616.638416837
thread_2_seq_264.617.638445669
thread_2_seq_265.621.638467091
thread_2_seq_266.622.638467229
ASMCMD [+DATA/PROD/ARCHIVELOG/2007_11_12]
Notice that thread 1 (node 1) is missing sequences 653, 655 and 657. Similarly thread 2 is missing sequence#263.
Question is how to bring these missing archivelogs from ASM@primary cluster (they are still sitting on disk @primary)?
Solution: I use a 3-step process there
step 1: Use RMAN "copy" command to copy these to a cooked filesystem on the primary cluster
step 2: Scp the restored (copied) archivelogs to the standby cluster
step 3: Recover (automatic) standby database and supply the scp'ed archivelog under cooked FS.
First, query the ASM@primary to get the exact name/location of these archives --
ASMCMD [+FRA/PROD/ARCHIVELOG/2007_11_12] > ls
thread_1_seq_650.768.638416833
thread_1_seq_651.771.638416841
thread_1_seq_652.781.638445663
thread_1_seq_653.796.638465819
thread_1_seq_654.797.638466159
thread_1_seq_655.798.638466189
thread_1_seq_656.800.638466307
thread_1_seq_657.801.638466455
thread_2_seq_262.769.638416835
thread_2_seq_263.770.638416841
thread_2_seq_264.782.638445665
thread_2_seq_265.799.638466191
step 1: Execute the RMAN copy command (primary cluster). Set the env to the database first.
RMAN> copy archivelog '+FRA/prod/archivelog/2007_11_12/thread_2_seq_263.770.638416841' to '/u02/backup/PROD/thread_2_seq_263.770.638416841';
Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1057 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=2 sequence=263 recid=1412 stamp=638416840
output filename=/u02/backup/PROD/thread_2_seq_263.770.638416841 recid=1426 stamp=638465672
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638465673.795.638465673 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_653.796.638465819' to '/u02/backup/PROD/thread_1_seq_653.796.638465819';
Starting backup at 12-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1055 instance=PROD1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1081 instance=PROD1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=653 recid=1427 stamp=638465819
output filename=/u02/backup/PROD/thread_1_seq_653.796.638465819 recid=1435 stamp=638466707
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466708.802.638466709 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_655.798.638466189' to '/u02/backup/PROD/thread_1_seq_655.798.638466189';
Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=655 recid=1430 stamp=638466189
output filename=/u02/backup/PROD/thread_1_seq_655.798.638466189 recid=1436 stamp=638466745
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466746.803.638466747 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> copy archivelog '+FRA/PROD/ARCHIVELOG/2007_11_12/thread_1_seq_657.801.638466455' to '/u02/backup/PROD/thread_1_seq_657.801.638466455';
Starting backup at 12-NOV-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=657 recid=1434 stamp=638466454
output filename=/u02/backup/PROD/thread_1_seq_657.801.638466455 recid=1437 stamp=638466777
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-NOV-07
Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=+FRA/prod/autobackup/2007_11_12/s_638466778.804.638466779 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07
RMAN> quit
Recovery Manager complete.
total 47463696
-rw-r----- 1 oracle oinstall 32768 Nov 12 15:52 thread_1_seq_657.801.638466455
-rw-r----- 1 oracle oinstall 17920 Nov 12 15:52 thread_1_seq_655.798.638466189
-rw-r----- 1 oracle oinstall 15824384 Nov 12 15:51 thread_1_seq_653.796.638465819
-rw-r----- 1 oracle oinstall 1024 Nov 12 15:50 thread_2_seq_263.770.638416841
...
...
step2: Scp the restored archivelogs to the standby server (bcp1 is node 1 of our standby cluster)
thread_2_seq_263.770.638416841
thread_1_seq_653.796.638465819 100% 15MB 15.1MB/s 00:01
thread_1_seq_655.798.638466189 100% 18KB 17.5KB/s 00:00
thread_1_seq_657.801.638466455
PROD1_SQL > recover automatic standby database;
ORA-00279: change 98980021976 generated at 11/12/2007 02:00:34 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_263_631792716.dbf
ORA-00280: change 98980021976 for thread 2 is in sequence #263
ORA-00278: log file '+DATA/prod/2_262_631792716.dbf' no longer needed for this recovery
Specify log: {
/u02/backup/PROD/thread_2_seq_263.770.638416841
RA-00279: change 98980621191 generated at 11/12/2007 10:01:02 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_653_631792716.dbf
ORA-00280: change 98980621191 for thread 1 is in sequence #653
ORA-00278: log file '+DATA/prod/1_653_631792716.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '+DATA/prod/1_653_631792716.dbf'
ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/1_653_631792716.dbf
ORA-15173: entry '1_653_631792716.dbf' does not exist in directory 'prod'
Specify log: {
/u02/backup/PROD/thread_1_seq_653.796.638465819
ORA-00279: change 98980621191 generated at 11/12/2007 02:00:40 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_264_631792716.dbf
ORA-00280: change 98980621191 for thread 2 is in sequence #264
Specify log: {
ORA-00279: change 98980621195 generated at 11/12/2007 10:01:04 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_265_631792716.dbf
ORA-00280: change 98980621195 for thread 2 is in sequence #265
ORA-00278: log file '+DATA/prod/2_264_631792716.dbf' no longer needed for this recovery
Specify log: {
/u02/backup/PROD/thread_2_seq_265.799.638466191
ORA-00279: change 98981521197 generated at 11/12/2007 15:36:59 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_654_631792716.dbf
ORA-00280: change 98981521197 for thread 1 is in sequence #654
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_653.796.638465819' no longer needed for this recovery
Specify log: {
ORA-00279: change 98981522036 generated at 11/12/2007 15:42:38 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_655_631792716.dbf
ORA-00280: change 98981522036 for thread 1 is in sequence #655
ORA-00278: log file '+DATA/prod/1_654_631792716.dbf' no longer needed for this recovery
Specify log: {
/u02/backup/PROD/thread_1_seq_655.798.638466189
ORA-00279: change 98981522129 generated at 11/12/2007 15:43:09 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_656_631792716.dbf
ORA-00280: change 98981522129 for thread 1 is in sequence #656
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_655.798.638466189' no longer needed for this recovery
Specify log: {
ORA-00279: change 98981522138 generated at 11/12/2007 15:43:11 needed for thread 2
ORA-00289: suggestion : +DATA/prod/2_266_631792716.dbf
ORA-00280: change 98981522138 for thread 2 is in sequence #266
ORA-00278: log file '/u02/backup/PROD/thread_2_seq_265.799.638466191' no longer needed for this recovery
Specify log: {
/u02/backup/PROD/thread_2_seq_266.806.638467227
ORA-00279: change 98981577931 generated at 11/12/2007 15:45:07 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_657_631792716.dbf
ORA-00280: change 98981577931 for thread 1 is in sequence #657
ORA-00278: log file '+DATA/prod/1_656_631792716.dbf' no longer needed for this recovery
Specify log: {
/u02/backup/PROD/thread_1_seq_657.801.638466455
ORA-00279: change 98981578237 generated at 11/12/2007 15:47:34 needed for thread 1
ORA-00289: suggestion : +DATA/prod/1_658_631792716.dbf
ORA-00280: change 98981578237 for thread 1 is in sequence #658
ORA-00278: log file '/u02/backup/PROD/thread_1_seq_657.801.638466455' no longer needed for this recovery
Specify log: {
cancel
Media recovery cancelled.
PROD1_SQL > recover managed standby database using current logfile disconnect from session;
Media recovery complete.
This completes the recovery process for the standby database and fills in the gaps.
Thursday, November 1, 2007
Switchover from Primary to Standby in RAC
Recently we were asked to perform a switchover in a RAC environment and although it seemed difficult initially, it turned out to be rather quick and simple (provided, of course you did things in the right order!).
Following the documentation at http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/role_management.htm#i1030646
in preparation for a switchover, I shut instance 2 of standby down (the one that was not involved in the media recovery).
Per the documentation:
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
After doing some research, I realized that you need to shutdown ALL but one instance on the primary side also (other words, before running the switchover command in a RAC env, keep only one instance running at primary site and one instance at the DR site).
On primary node 1
After shutting down instance 2 at the primary site, I was able to run the SWITCHOVER successfully on instance 1 of the primary.
[Note: After the switchover, the instance is brought up in NOMOUNT stage. So I did a shutdown and mounted it again]
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
GCPROD1_SQL > shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
GCPROD1_SQL > startup NOMOUNT
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2072960 bytes
Variable Size 369102464 bytes
Database Buffers 1157627904 bytes
Redo Buffers 14700544 bytes
Database mounted.
GCPROD1_SQL > SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Notice that I used the "..WITH SESSION SHUTDOWN" option as I was unable to bring a few active internal processes down.
Now that our old primary has become the new standby, we need to fix a few INIT parameters related to log shipping.
a) LOG_ARCHIVE_DEST_n setting
Here's our old settings --
GCPROD1_SQL > show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
log_archive_dest_1 string location="+FLASHDG", valid_for
=(ONLINE_LOGFILES, PRIMARY_ROLE)
log_archive_dest_10 string
log_archive_dest_2 string service="GCPROD_STBY", LGWR AS
YNC NOAFFIRM delay=0 OPTIONAL
max_failure=10 max_connection
s=4 reopen=15 db_unique_name="
GCPROD" register net_timeout
=180 valid_for=(online_logfile
,primary_role)
...
...
[removed for brevity]
We can remove log_archive_dest_2 as its not needed on a standby database. Also log_archive_dest_1 needs to be changed.
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=+FLASHDG' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_2='';
[Note: For, log_archive_dest_1 we may also set as LOCATION=+FLASHDG valid_for=(STANDBY_LOGFILES, STANDBY_ROLE)'. That should also work ok]
b) FAL settings
FAL_SERVER should point to the primary database (tns string) and FAL_CLIENT should point to the Standby database (self)
GCPROD1_SQL > ALTER SYSTEM SET fal_client='GCPROD_STBY' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET fal_server='GCPROD_PRIM' SCOPE=BOTH;
Make sure we update the TNSNAMES.ora file for GCPROD_STBY and GCPROD_PRIM. GCPROD_STBY should resolve to the standby database and GCPROD_PRIM should resolve to the primary (ie. the erstwhile standby; that we haven't switched to the primary role yet -- we'll do that shortly)
After making these change, we put the new standby database in recovery mode --
GCPROD1_SQL > alter database recover managed standby database disconnect from session;
Database altered.
On standby node 1
Next, we perform a switchover of the old standby database to make it the new PRIMARY.
[Note: I had to perform media recovery as shown below as I had accidentally canceled the recovery earlier -- this might not be needed if the (old) standby was already in recovery mode]
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
GCPROD1_SQL > alter database recover managed standby database disconnect from session;
Database altered.
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
GCPROD1_SQL > alter database open;
Database altered.
At this point our new primary database is open and available
Now we've to setup the log shipping related INIT parameters on our new primary.
a) LOG_ARCHIVE_DEST_n setting
Here's our old settings --
GCPROD1_SQL > show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=+FLASHDG
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
...
...
[removed for brevity]
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_1='location="+FLASHDG", valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_2='service="GCPROD_STBY", LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=10 max_connections=4 reopen=15 db_unique_name="GCPROD" register net_timeout=180 valid_for=(online_logfile,pr
imary_role)' SCOPE=BOTH;
Also don't forget to enable the destination (else the logs don't ship automatically).
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
b) FAL settings
Primary database doesn't need any FAL settings, so we may remove those.
GCPROD1_SQL > ALTER SYSTEM SET fal_client='' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
This completes the SWITCHOVER activity pretty much.
If the DB_UNIQUE names are different for some reason on the primary and DR sides, we also need to update LOG_ARCHIVE_CONFIG parameter for dg_config setting otherwise we might see the following errors in the alert log of the primary database --
Errors in file /u01/app/oracle/admin/GCPROD/bdump/gcprod1_arc1_1316.trc:
ORA-16047: DGID mismatch between destination setting and standby
...
PING[ARC1]: Heartbeat failed to connect to standby 'GCPROD_STBY'. Error is 16047.
The solution is to set the LOG_ARCHIVE_CONFIG parameter to show the db_unique_name (if that is different than db_name, for any reason)
GCPROD1_SQL > ALTER SYSTEM SET log_archive_config='dg_config=(GCPROD, GCPRODDR)' SCOPE=BOTH;
[where GCPROD is db_unique_name on primary side and GCPRODDR is the db_name on the DR side]
All is set now. We can verify it by performing a few log switches on the primary side
The only remaining item would be to make sure the TNSNAMES file is updated on both sides.
Following the documentation at http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/role_management.htm#i1030646
in preparation for a switchover, I shut instance 2 of standby down (the one that was not involved in the media recovery).
Per the documentation:
Verify that all but one RAC instance on the standby databases in a Real Application Clusters configuration are shut down.
For a Real Application Clusters database, only one RAC instance on the standby database can be online during the role transition. Shut down all other instances before starting the role transition. Then, after the role transition completes, bring these instances back online.I ensured that the primary and standby databases were in sync and then went ahead with the ALTER DATABASE COMMIT TO SWITCHOVER.. command on one of the two primary instances --
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY with session shutdown
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
After doing some research, I realized that you need to shutdown ALL but one instance on the primary side also (other words, before running the switchover command in a RAC env, keep only one instance running at primary site and one instance at the DR site).
On primary node 1
After shutting down instance 2 at the primary site, I was able to run the SWITCHOVER successfully on instance 1 of the primary.
[Note: After the switchover, the instance is brought up in NOMOUNT stage. So I did a shutdown and mounted it again]
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
GCPROD1_SQL > shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
GCPROD1_SQL > startup NOMOUNT
ORACLE instance started.
Total System Global Area 1543503872 bytes
Fixed Size 2072960 bytes
Variable Size 369102464 bytes
Database Buffers 1157627904 bytes
Redo Buffers 14700544 bytes
Database mounted.
GCPROD1_SQL > SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
Notice that I used the "..WITH SESSION SHUTDOWN" option as I was unable to bring a few active internal processes down.
Now that our old primary has become the new standby, we need to fix a few INIT parameters related to log shipping.
a) LOG_ARCHIVE_DEST_n setting
Here's our old settings --
GCPROD1_SQL > show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
log_archive_dest_1 string location="+FLASHDG", valid_for
=(ONLINE_LOGFILES, PRIMARY_ROLE)
log_archive_dest_10 string
log_archive_dest_2 string service="GCPROD_STBY", LGWR AS
YNC NOAFFIRM delay=0 OPTIONAL
max_failure=10 max_connection
s=4 reopen=15 db_unique_name="
GCPROD" register net_timeout
=180 valid_for=(online_logfile
,primary_role)
...
...
[removed for brevity]
We can remove log_archive_dest_2 as its not needed on a standby database. Also log_archive_dest_1 needs to be changed.
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=+FLASHDG' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_2='';
[Note: For, log_archive_dest_1 we may also set as LOCATION=+FLASHDG valid_for=(STANDBY_LOGFILES, STANDBY_ROLE)'. That should also work ok]
b) FAL settings
FAL_SERVER should point to the primary database (tns string) and FAL_CLIENT should point to the Standby database (self)
GCPROD1_SQL > ALTER SYSTEM SET fal_client='GCPROD_STBY' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET fal_server='GCPROD_PRIM' SCOPE=BOTH;
Make sure we update the TNSNAMES.ora file for GCPROD_STBY and GCPROD_PRIM. GCPROD_STBY should resolve to the standby database and GCPROD_PRIM should resolve to the primary (ie. the erstwhile standby; that we haven't switched to the primary role yet -- we'll do that shortly)
After making these change, we put the new standby database in recovery mode --
GCPROD1_SQL > alter database recover managed standby database disconnect from session;
Database altered.
On standby node 1
Next, we perform a switchover of the old standby database to make it the new PRIMARY.
[Note: I had to perform media recovery as shown below as I had accidentally canceled the recovery earlier -- this might not be needed if the (old) standby was already in recovery mode]
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
GCPROD1_SQL > alter database recover managed standby database disconnect from session;
Database altered.
GCPROD1_SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
GCPROD1_SQL > alter database open;
Database altered.
At this point our new primary database is open and available
Now we've to setup the log shipping related INIT parameters on our new primary.
a) LOG_ARCHIVE_DEST_n setting
Here's our old settings --
GCPROD1_SQL > show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string LOCATION=+FLASHDG
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
...
...
[removed for brevity]
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_1='location="+FLASHDG", valid_for=(ALL_LOGFILES, ALL_ROLES)' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_2='service="GCPROD_STBY", LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=10 max_connections=4 reopen=15 db_unique_name="GCPROD" register net_timeout=180 valid_for=(online_logfile,pr
imary_role)' SCOPE=BOTH;
Also don't forget to enable the destination (else the logs don't ship automatically).
GCPROD1_SQL > ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
b) FAL settings
Primary database doesn't need any FAL settings, so we may remove those.
GCPROD1_SQL > ALTER SYSTEM SET fal_client='' SCOPE=BOTH;
GCPROD1_SQL > ALTER SYSTEM SET fal_server='' SCOPE=BOTH;
This completes the SWITCHOVER activity pretty much.
If the DB_UNIQUE names are different for some reason on the primary and DR sides, we also need to update LOG_ARCHIVE_CONFIG parameter for dg_config setting otherwise we might see the following errors in the alert log of the primary database --
Errors in file /u01/app/oracle/admin/GCPROD/bdump/gcprod1_arc1_1316.trc:
ORA-16047: DGID mismatch between destination setting and standby
...
PING[ARC1]: Heartbeat failed to connect to standby 'GCPROD_STBY'. Error is 16047.
The solution is to set the LOG_ARCHIVE_CONFIG parameter to show the db_unique_name (if that is different than db_name, for any reason)
GCPROD1_SQL > ALTER SYSTEM SET log_archive_config='dg_config=(GCPROD, GCPRODDR)' SCOPE=BOTH;
[where GCPROD is db_unique_name on primary side and GCPRODDR is the db_name on the DR side]
All is set now. We can verify it by performing a few log switches on the primary side
The only remaining item would be to make sure the TNSNAMES file is updated on both sides.
Tuesday, October 23, 2007
Oracle Streams (Downstream) with Target on RAC
Since we did it here several times, I thought I would share this with others who might be looking for examples on how to setup Oracle Streams (downstream approach) with target database in a RAC environment.Overview
Our setup (as shown in the picture) has the source database sitting on Unix and the downstream database is on a different server (also solaris). Archivelogs generated on source_db are available on the DOWNSTREAM_SERVER because we have a hot standby database sitting there (you could manually move the logs via scp or ftp). These archivelogs are then registered with the downstream database where the CAPTURE and PROPAGATE are running. The Propagate sends the changes to the Target_db via a Queue-To-Queue communication.
Detailed Steps
This is broken down into 4 sections as explained below.
A. Preparing the SOURCE environment
1. Create the STRMDBA id with the required privileges in the SOURCE_DB database. This will be the administrator account for Oracle streams there.
connect / as sysdba
create user STRMDBA identified by strmdba default tablespace USERS temporary tablespace TEMP;
grant CREATE SESSION, SELECT ANY DICTIONARY to STRMDBA;
grant EXECUTE on DBMS_CAPTURE_ADM to STRMDBA;
grant EXECUTE on DBMS_STREAMS_RPC to STRMDBA;
grant EXEMPT ACCESS POLICY to STRMDBA;
create user STRMDBA identified by strmdba default tablespace USERS temporary tablespace TEMP;
grant CREATE SESSION, SELECT ANY DICTIONARY to STRMDBA;
grant EXECUTE on DBMS_CAPTURE_ADM to STRMDBA;
grant EXECUTE on DBMS_STREAMS_RPC to STRMDBA;
grant EXEMPT ACCESS POLICY to STRMDBA;
2. Prepare tables for instantiation. This ensures that all the required information for generating a LCR (Logical Change Record) gets added to the redologs. This step also turns on the supplemental logging on Primary and Unique Keys (default) or all columns (SUPPLEMENTAL_LOGGING='ALL' option). Supplemental logging for ALL columns is required if the source table does not have a primary or a unique key.
exec DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION (table_name => 'SCOTT.EMP');
exec DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => 'SCOTT.T_EMP', SUPPLEMENTAL_LOGGING=>'ALL');
B. Preparing the DOWNSTREAM environment
1. In the downstream database, create a new tablespace called LOGMINER for use by the logminer process (by default, logminer uses the SYSAUX tablespace). Steps below include a drop tablespace statement and the default tablespace is set to SYSTEM prior to dropping the tablespace.
[Note: Not setting SYSTEM as default for logminer process before dropping the LOGMINER tablespace might corrupt the entire dictionary].
alter user SYSTEM default tablespace SYSTEM;
exec DBMS_LOGMNR_D.SET_TABLESPACE('SYSTEM');
drop tablespace LOGMINER including contents and datafiles;
create tablespace LOGMINER datafile '/u16/oradata/$ORACLE_SID/lgm01$ORACLE_SID.dbf' size 50M autoextend on next 20M maxsize 500M extent management local uniform size 64K segment space management auto;
alter user system default tablespace LOGMINER;
exec DBMS_LOGMNR_D.SET_TABLESPACE('LOGMINER');
alter user system default tablespace SYSTEM;
2 . Create a STREAMS_Q tablespace in the downstream database to store the database objects for Oracle streams.
drop tablespace STREAMS_Q including contents and datafiles;
connect / as sysdba
create user STRMDBA identified by &passwd default tablespace STREAMS_Q quota unlimited on STREAMS_Q temporary tablespace TEMP;
grant create session, dba to strmdba;
exec dbms_streams_auth.grant_admin_privilege('STRMDBA');
4. Create a capture Q (notice, I did that under the STRMDBA schema). This step will also create a Queue Table to store the LCRs.
connect STRMDBA
begin
dbms_streams_adm.set_up_queue(
queue_table => 'T_STRM_Q',
storage_clause => 'TABLESPACE STREAMS_Q',
queue_name => 'STRM_CAPTURE_Q',
queue_user => 'STRMDBA');
end;
/
5. Create a database link to the SOURCE and TARGET databases. Again, this is a private link owned by STRMDBA id.
connect STRMDBA
drop database link SOURCE_DB;
create database link SOURCE_DB> connect to STRMDBA identified by &passwd using 'SOURCE_DB';
drop database link TARGET_DB;
create database link TARGET_DB connect to STRMDBA identified by &passwd using 'TARGET_DB';
6. Create a capture process called STRM_CAPTURE to capture changes into the Capture Queue called STRM_CAPTURE_Q (created in step 4)
begin
dbms_capture_adm.create_capture(
queue_name => 'STRMDBA.STRM_CAPTURE_Q',
capture_name => 'STRM_CAPTURE',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'SOURCE_DB',
use_database_link => TRUE,
first_scn => NULL,
logfile_assignment => 'explicit');
end;
/
7. Similarly, create a propagation process called STRM_PROPAGATE to propagate changes from the Capture Queue to Apply Queue (notice, in RAC environment it is a Queue-To-Queue propagation as APPLY is running only on a single node)
connect STRMDBA
BEGIN
DBMS_PROPAGATION_ADM.CREATE_PROPAGATION(
propagation_name => 'STRM_PROPAGATE',
source_queue => 'STRMDBA.STRM_CAPTURE_Q',
destination_queue => 'STRMDBA.STRM_APPLY_Q',
destination_dblink => 'TARGET_DB',
queue_to_queue => TRUE);
END;
/
8. Add table CAPTURE RULES for all the tables you want to setup streams on (lookup documentation for syntax if you'd like to do it on entire schema). Notice, I'm including both DML and DDL changes to be captured here.
connect STRMDBA
begin
dbms_streams_adm.add_table_rules(
table_name => 'SCOTT.DEPARTMENT',
streams_type => 'CAPTURE',
streams_name => 'STRM_CAPTURE',
queue_name => 'STRMDBA.STRM_CAPTURE_Q',
include_dml => TRUE,
include_ddl => TRUE,
include_tagged_lcr => FALSE,
source_database => 'SOURCE_DB',
inclusion_rule => TRUE);
end;
/
9. Next, add table PROPAGATION RULES. Notice the use of target database link for queue-to-queue propagation. The destination Queue (STRM_APPLY_Q) will be created in the next section.
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'SCOTT.DEPARTMENT',
streams_name => 'STRM_PROPAGATE',
source_queue_name => 'STRMDBA.STRM_CAPTURE_Q',
destination_queue_name=> 'STRMDBA.STRM_APPLY_Q@TARGET_DB',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'SOURCE_DB',
inclusion_rule => TRUE,
queue_to_queue => TRUE);
end;
/
This sets up the DOWNSTREAM enviroment. We have not yet started the CAPTURE process which we will do at the end.
C. Preparing the TARGET environment
1. In the Target database, create the STREAMS_Q and LOGMINER tablespaces, just like we did it in the downstream database. Since this is RAC and we use ASM diskgroups, both these tablespaces are created under the +DATA diskgroup.
Notice, I include the drop statements also in case we are redoing this effort. The STREAM_Q tablespace cannot be dropped unless we drop the Queue Table there.
connect / as sysdba
alter user system default tablespace SYSTEM;
exec DBMS_LOGMNR_D.SET_TABLESPACE('SYSTEM');
drop tablespace LOGMINER including contents and datafiles;
create tablespace LOGMINER datafile '+DATA' size 100M autoextend on next 20M maxsize 4000M extent management local uniform size 64K segment space management auto;
alter user system default tablespace LOGMINER;
exec DBMS_LOGMNR_D.SET_TABLESPACE('LOGMINER');
alter user system default tablespace SYSTEM;
drop tablespace STREAMS_Q including contents and datafiles;
exec DBMS_AQADM.DROP_QUEUE_TABLE('STRMDBA.T_STRM_Q',TRUE);
create tablespace STREAMS_Q datafile '+DATA' size 50M autoextend on next 20M maxsize 1000M extent management local uniform size 64K segment space management auto;'
2. Create the streams Administrator account with the right privileges in the Target database. I remember hitting issues if I tried without the DBA privs, but you might want to test that also.
connect / as sysdba
create user STRMDBA identified by &passwd default tablespace STREAMS_Q quota unlimited on STREAMS_Q temporary tablespace TEMP;
grant create session, dba to STRMDBA;
exec dbms_streams_auth.grant_admin_privilege('STRMDBA');
3. Create the Apply Queue (STRMDBA schema) in the Target database. This also creates a Queue Table to store the propagated LCRs.
connect STRMDBA
begin
dbms_streams_adm.set_up_queue(
queue_table => 'T_STRM_Q',
storage_clause => 'TABLESPACE STREAMS_Q',
queue_name => 'STRM_APPLY_Q',
queue_user => 'STRMDBA');
end;
/
4. Add table APPLY RULES.
connect STRMDBA
begin
dbms_streams_adm.add_table_rules(
table_name => 'SCOTT.DEPARTMENT',
streams_type => 'APPLY',
streams_name => 'STRM_APPLY',
queue_name => 'STRMDBA.STRM_APPLY_Q',
include_dml => TRUE,
include_ddl => TRUE,
source_database => 'SOURCE_DB');
end;
/
[Note: You might have a requirement to add a filter to the APPLY process. In those cases, the include_dml should be set to FALSE and a subset rule should be added.
For example, to apply changes for records where department_code is 3 or 4, set the include_dml=FALSE above and then add the following subset rule.
connect strmdba
begin
DBMS_STREAMS_ADM.ADD_SUBSET_RULES(
table_name => 'SCOTT.DEPARTMENT',
dml_condition => ' DEPT_CODE in (3,4) ',
streams_type => 'APPLY',
streams_name => 'STRM_APPLY',
queue_name => 'STRM_APPLY_Q');
end;
/
5. Alter the APPLY process to set DISABLE_ON_ERROR to 'N'. The default is 'Y' which disables the APPLY on the very first unresolved error.
connect / as sysdba
begin
dbms_apply_adm.alter_apply(
apply_name => 'STRM_APPLY',
apply_user => 'STRMDBA');
end;
/
connect STRMDBA
begin
dbms_apply_adm.set_parameter(
apply_name => 'STRM_APPLY',
parameter => 'DISABLE_ON_ERROR',
value => 'N');
end;
/
D. Starting the CAPTURE/APPLY processes and register Archivelogs
1. Our Source, Downstream and Target environments are all set for capturing changes for SCOTT.DEPARTMENT table. Start the CAPTURE process in the Downstream database (check the STATUS column of DBA_CAPTURE) and APPLY process in the Target database (STATUS column of DBA_APPLY).
In the Target database --
connect STRMDBA
exec dbms_apply_adm.start_apply(apply_name => 'STRM_APPLY');
connect / as sysdba
In Downstream database --
connect STRMDBA
exec dbms_capture_adm.start_capture (capture_name => 'STRM_CAPTURE');
2. The archivelogs generated on the source database should be scp'ed to the downstream server (I get them by virtue of a hot standby there but, as I mentioned earlier, you might have to initiate a job to do that unless you want to add a new destination in the source itself). Once the archivelogs are on the downstream server, go ahead and manually register them in the Downstream database (well, I use a perl script to do that so it is all automated)
alter database register logical logfile '/u02/oradata/SOURCE_DB/log_SOURCE_DB_625925510_1_3023.log' for 'STRM_CAPTURE'
Bonus item..
Adding a Global Rule to exclude certain DDLs like Grants, Truncate Table etc
There could also be a need to exclude certain DDL statements from being applied to the Target database like Truncate Table, Grant, Alter trigger etc. This could be achieved by adding a Global Rule on the Downstream database where CAPTURE is running. [Be aware that this will turn supplemental logging on Primary Key, Unique Key and Foreign Key at the database level in the SOURCE_DB i.e. implicitly execute "alter database add supplemental log data(PRIMARY KEY,UNIQUE INDEX,FOREIGN KEY) columns" in the Source database].
connect STRMDBA
begin
dbms_streams_adm.add_global_rules(
streams_type => 'CAPTURE',
streams_name => 'STRM_CAPTURE',
queue_name => 'STRMDBA.STRM_CAPTURE_Q',
include_dml => FALSE,
include_ddl => TRUE,
include_tagged_lcr => FALSE,
source_database => 'SOURCE_DB',
inclusion_rule => FALSE,
and_condition =>'(:ddl.get_command_type()=''CREATE INDEX'''||' or :ddl.get_command_type()=''GRANT''' || ' or :ddl.get_command_type()=''TRUNCATE TABLE'''||' or :ddl.get_command_type()=''ALTER TRIGGER'''||')');
end;
/
Ensure that the changes on SCOTT.DEPARTMENT are streaming into the Target by generating a log in the Source database and then manually registering it to the Downstream database as shown above. You should be all set now!
Monday, October 22, 2007
ORA-12514 in Oracle Streams
I recently got stuck on setting up streams (downstream approach) with the target database in a RAC environment (I'll detail the steps of doing this in a later blog entry).
Anyway, here's the error that was showing up on the downstream database --
Anyway, here's the error that was showing up on the downstream database --
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Propagation Schedule for (STRMDBA.STRM_CAPTURE_Q, "STRMDBA"."STRM_APPLY_Q"@PERG) disabled due to 16 consecutive errors.
Here's what I did to fix this error (also note that earlier my "global_name" was also not set correctly, so it is quite likely that the service name was initially picked from the wrong global_name entry) --
PERG1_SQL > show parameter service
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
service_names string SYS$STRMDBA.STRM_APPLY_Q.PERG.REGRESS.RDBMS.DEV.US.ORACLE.COM, PERG
PERG1_SQL > alter system set service_names='SYS$STRMDBA.STRM_APPLY_Q.PERG, PERG';
System altered.
PERG1_SQL > show parameter service
NAME TYPE VALUE
------------------------------------ -------- ------------------------------
service_names string SYS$STRMDBA.STRM_APPLY_Q.PERG, PERG
The propagation was still giving errors (as it potentially recorded the wrong service_names entry in the dictionary), so I had to drop and recreate the propagation rules. After that, all went fine.
Subscribe to:
Posts (Atom)
