So starting out with the database i created in http://dbaharrison.blogspot.co.uk/2013/12/it-cant-get-much-simpler-than-this.html
I initially run catalog and catproc to make the database usable (and things fail miserably...)
grant select on gv_$dispatcher_rate to select_catalog_role
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select
obj#,type#,ctime,mtim...","SQLA^5f83ba36","idndef : qcuAllocIdn")
Seems the default shared pool is not big enough so I have to amend my original method :-(
echo shared_pool_size=1G >> $ORACLE_HOME/dbs/initMINI.ora
So i have to have 2 parameters rather than just one....
Anyway after setting that and restarting i can then run catalog and catproc (which interestingly enough at 12 also automatically installs XDB)
I have to run utlrp for some reason at the end of this to make everything clean but then all looks good.
So now on to the standby - i'm creating this on the same server to make things less complicated (or more complicated depending on how you look at it....)
I initially just copy the init file and add a single extra entry for db_unique_name
[oracle@server]# cp initMINI.ora initMINICOPY.ora
[oracle@server]# echo db_unique_name=MINICOPY >> $ORACLE_HOME/dbs/initMINICOPY.ora
I then have to add this to tnsnames (ezconnect syntax did not seem to work with rman) (UR=A is important to allow connections to the standby from remote - if you don;t have it you get an error about connections being blocked)
MINICOPY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME=MINICOPY)))
Then create a password file for the standby
orapwd file=orapwMINICOPY password=change_on_install
Now restart the primary to activate archivelog mode
shutdown ;
startup mount;
alter database archivelog;
alter database open;
Now we have everything ready lets fire up rman and try a duplicate from active database.
export ORACLE_SID=MINI
rman target=/ auxiliary=sys/change_on_install@MINICOPY
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Dec 23 21:58:05 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: MINI (DBID=483340855)
connected to auxiliary database: MINI (not mounted)
RMAN> run{ duplicate target database for standby from active database;}
Starting Duplicate Db at 23-DEC-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2013 22:04:08
RMAN-05501: aborting duplication of target database
RMAN-05609: Must specify a username for target connection when using active duplicate
And it fails - seems we have to use a username/password and not just /. Lets try that next:
rman target=sys/change_on_install auxiliary=sys/change_on_install@MINICOPY
Starting Duplicate Db at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2013 22:05:12
RMAN-05501: aborting duplication of target database
RMAN-05621: password file location could not be found for the target database
And we fail again - seems we need a password file for the primary too.
orapwd file=orapwMINI password=change_on_install
Now lets try
RMAN> run{ duplicate target database for standby from active database;}
Starting Duplicate Db at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/12.0.0.1/dbs/orapwMINI' auxiliary format
'/oracle/12.0.0.1/dbs/orapwMINICOPY' ;
}
executing Memory Script
Starting backup at 23-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=770 device type=DISK
Finished backup at 23-DEC-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/12.0.0.1/dbs/cntrlMINI.dbf';
}
executing Memory Script
Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/23/2013 22:06:12
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2013 22:06:12
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/23/2013 22:06:12
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
RMAN>
And we fail again with an odd error - after a bit of digging i suspect the problem is the init file i had copied I'd actually put the controlfile name in at one point and that had ended up in the standby init file - lets remove that and try again.
RMAN> run{ duplicate target database for standby from active database;}
Starting Duplicate Db at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/12.0.0.1/dbs/orapwMINI' auxiliary format
'/oracle/12.0.0.1/dbs/orapwMINICOPY' ;
}
executing Memory Script
Starting backup at 23-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=770 device type=DISK
Finished backup at 23-DEC-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/12.0.0.1/dbs/cntrlMINI.dbf';
}
executing Memory Script
Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/23/2013 22:06:12
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2013 22:06:12
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/23/2013 22:06:12
ORA-17628: Oracle error 19505 returned by remote Oracle server
ORA-19505: failed to identify file ""
RMAN>
Hmm - i tried allocating channels here but it doesn;t like it - let's revert to my original command.
rman target=sys/change_on_install auxiliary=sys/change_on_install@MINICOPY
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Dec 23 22:14:29 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: MINI (DBID=483340855)
connected to auxiliary database: MINI (not mounted)
RMAN> run{ duplicate target database for standby from active database;
2> }
Starting Duplicate Db at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/12.0.0.1/dbs/orapwMINI' auxiliary format
'/oracle/12.0.0.1/dbs/orapwMINICOPY' ;
}
executing Memory Script
Starting backup at 23-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1218 device type=DISK
Finished backup at 23-DEC-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/12.0.0.1/dbs/cntrlMINICOPY.dbf';
}
executing Memory Script
Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/12.0.0.1/dbs/snapcf_MINI.f tag=TAG20131223T221445
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/23/2013 22:14:52
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /oracle/12.0.0.1/dbs/dbu1MINI.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /oracle/12.0.0.1/dbs/dbx1MINI.dbf conflicts with a file used by the target database
RMAN-05001: auxiliary file name /oracle/12.0.0.1/dbs/dbs1MINI.dbf conflicts with a file used by the target database
RMAN>
Getting better now we just have file name conflicts. Lets fix that with another couple of parameters
db_file_name_convert=('MINI','MINICOPY')
log_file_name_convert=('MINI','MINICOPY')
Now we try again
rman target=sys/change_on_install auxiliary=sys/change_on_install@MINICOPY
Recovery Manager: Release 12.1.0.1.0 - Production on Mon Dec 23 22:17:08 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: MINI (DBID=483340855)
connected to auxiliary database: MINI (not mounted)
RMAN> run{ duplicate target database for standby from active database;}
Starting Duplicate Db at 23-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/12.0.0.1/dbs/orapwMINI' auxiliary format
'/oracle/12.0.0.1/dbs/orapwMINICOPY' ;
}
executing Memory Script
Starting backup at 23-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1218 device type=DISK
Finished backup at 23-DEC-13
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/12.0.0.1/dbs/cntrlMINICOPY.dbf';
}
executing Memory Script
Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/12.0.0.1/dbs/snapcf_MINI.f tag=TAG20131223T221719
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for datafile 1 to
"/oracle/12.0.0.1/dbs/dbs1MINICOPY.dbf";
set newname for datafile 2 to
"/oracle/12.0.0.1/dbs/dbx1MINICOPY.dbf";
set newname for datafile 3 to
"/oracle/12.0.0.1/dbs/dbu1MINICOPY.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oracle/12.0.0.1/dbs/dbs1MINICOPY.dbf" datafile
2 auxiliary format
"/oracle/12.0.0.1/dbs/dbx1MINICOPY.dbf" datafile
3 auxiliary format
"/oracle/12.0.0.1/dbs/dbu1MINICOPY.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/12.0.0.1/dbs/dbu1MINI.dbf
output file name=/oracle/12.0.0.1/dbs/dbu1MINICOPY.dbf tag=TAG20131223T221727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/12.0.0.1/dbs/dbs1MINI.dbf
output file name=/oracle/12.0.0.1/dbs/dbs1MINICOPY.dbf tag=TAG20131223T221727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/12.0.0.1/dbs/dbx1MINI.dbf
output file name=/oracle/12.0.0.1/dbs/dbx1MINICOPY.dbf tag=TAG20131223T221727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 23-DEC-13
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=834963485 file name=/oracle/12.0.0.1/dbs/dbs1MINICOPY.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=834963485 file name=/oracle/12.0.0.1/dbs/dbx1MINICOPY.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=834963485 file name=/oracle/12.0.0.1/dbs/dbu1MINICOPY.dbf
Finished Duplicate Db at 23-DEC-13
RMAN>
There we go - standby is created...... well i say created but it won;t actually work particularly well (and by that i mean not at all :-)) We need to set up extra archive dests, FAL and the broker to do this properly - but at this point we have a pre dataguard style standby set up - we'd just have to manually do everything to make it recover etc.
Again this whole thing is pretty pointless but it does show you the minimum you need to do just to get a very basic standby up and running - maybe just for a trial of something it might be helpful.
In the end my pfile's just contained the following:
Primary
db_name=MINI
shared_pool_size=1G
Standby
db_name=MINI
shared_pool_size=1G
db_unique_name=MINICOPY
db_file_name_convert=('MINI','MINICOPY')
log_file_name_convert=('MINI','MINICOPY')
Comments
Post a Comment