To check database parameter within Primary Database
col value format a65
col name format a50
set pagesize 50
select name, value from v$parameter
where name in (
'db_unique_name',
'db_name',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'dg_broker_start',
'fal_client',
'fal_server',
'dg_broker_config_file1',
'dg_broker_config_file2',
'db_recovery_file_dest',
'db_recovery_file_dest_size',
'db_create_file_dest',
'db_create_online_log_dest_1',
'db_create_online_log_dest_2',
'log_archive_config');
NAME VALUE
-------------------------------------------------- -----------------------------------------------------------------
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
fal_client
fal_server
db_create_file_dest +DATA_GRUP
db_create_online_log_dest_1 +DATA_GRUP
db_create_online_log_dest_2 +DATA_GRUP
db_recovery_file_dest +DATA_GRUP
db_recovery_file_dest_size 5048893440
db_name agap
db_unique_name agap
dg_broker_start FALSE
dg_broker_config_file1 /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1agap.dat
dg_broker_config_file2 /u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2agap.dat
16 rows selected.
To check if database is archivelog mode
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Current log sequence 168
To enable archivelog mode
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 503320288 bytes
Database Buffers 1056964608 bytes
Redo Buffers 13848576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Next log sequence to archive 168
Current log sequence 168
To set up parameter within primary database
alter system set fal_client='agap' scope=both sid='*';
alter system set fal_server='agap_sb' scope=both sid='*';
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(agap,agap_sb)' scope=both sid='*';
Backing up database
[oracle@ol7-121-server1 admin]$ rman target / catalog rman/rman@rman
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Sep 23 13:27:13 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGAP (DBID=392571929)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> backup database plus archivelog delete all input;
Starting backup at 23-SEP-17
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=168 RECID=1 STAMP=955459976
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/BACKUPSET/2017_09_23/annnf0_tag20170923t133258_0.288.955459979 tag=TAG20170923T133258 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+DATA_GRUP/AGAP/ARCHIVELOG/2017_09_23/thread_1_seq_168.287.955459977 RECID=1 STAMP=955459976
Finished backup at 23-SEP-17
Starting backup at 23-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA_GRUP/AGAP/DATAFILE/undotbs1.275.955306755
input datafile file number=00001 name=+DATA_GRUP/AGAP/DATAFILE/system.267.955306645
input datafile file number=00003 name=+DATA_GRUP/AGAP/DATAFILE/sysaux.271.955306735
input datafile file number=00006 name=+DATA_GRUP/AGAP/DATAFILE/users.277.955306777
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/BACKUPSET/2017_09_23/nnndf0_tag20170923t133259_0.287.955459981 tag=TAG20170923T133259 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA_GRUP/AGAP/594B8F8A96405680E0536538A8C0A639/DATAFILE/sysaux.281.955306785
input datafile file number=00007 name=+DATA_GRUP/AGAP/594B8F8A96405680E0536538A8C0A639/DATAFILE/system.279.955306779
input datafile file number=00009 name=+DATA_GRUP/AGAP/594B8F8A96405680E0536538A8C0A639/DATAFILE/users.283.955306793
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/594B8F8A96405680E0536538A8C0A639/BACKUPSET/2017_09_23/nnndf0_tag20170923t133259_0.289.955459989 tag=TAG20170923T133259 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA_GRUP/AGAP/594A73CA5B691AC2E0536538A8C003B4/DATAFILE/sysaux.273.955306745
input datafile file number=00002 name=+DATA_GRUP/AGAP/594A73CA5B691AC2E0536538A8C003B4/DATAFILE/system.269.955306729
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/594A73CA5B691AC2E0536538A8C003B4/BACKUPSET/2017_09_23/nnndf0_tag20170923t133259_0.290.955459991 tag=TAG20170923T133259 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-SEP-17
Starting backup at 23-SEP-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=169 RECID=2 STAMP=955459994
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/BACKUPSET/2017_09_23/annnf0_tag20170923t133315_0.292.955459995 tag=TAG20170923T133315 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+DATA_GRUP/AGAP/ARCHIVELOG/2017_09_23/thread_1_seq_169.291.955459995 RECID=2 STAMP=955459994
Finished backup at 23-SEP-17
Starting Control File and SPFILE Autobackup at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/AUTOBACKUP/2017_09_23/s_955459997.291.955459999 comment=NONE
Finished Control File and SPFILE Autobackup at 23-SEP-17
RMAN>
RMAN> BACKUP BACKUPSET ALL format="/backup_rman/backup_%U" ;
Starting backup at 23-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: input backup set: count=2, stamp=955306653, piece=1
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece +DATA_GRUP/AGAP/AUTOBACKUP/2017_09_21/s_955306134.268.955306653
piece handle=/backup_rman/backup_02sf1kkt_1_3 comment=NONE
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:02
channel ORA_DISK_1: input backup set: count=4, stamp=955306732, piece=1
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece +DATA_GRUP/AGAP/AUTOBACKUP/2017_09_21/s_955306134.270.955306733
piece handle=/backup_rman/backup_04sf1knc_1_3 comment=NONE
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=6, stamp=955306741, piece=1
channel ORA_DISK_1: starting piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece +DATA_GRUP/AGAP/AUTOBACKUP/2017_09_21/s_955306134.272.955306741
piece handle=/backup_rman/backup_06sf1knl_1_3 comment=NONE
channel..............
...............
..........
....
channel ORA_DISK_1: backup piece +DATA_GRUP/AGAP/AUTOBACKUP/2017_09_23/s_955459997.291.955459999
piece handle=/backup_rman/backup_0osf6act_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 23-SEP-17
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
Finished backup at 23-SEP-17
Starting Control File and SPFILE Autobackup at 23-SEP-17
piece handle=+DATA_GRUP/AGAP/AUTOBACKUP/2017_09_23/s_955462754.295.955462755 comment=NONE
Finished Control File and SPFILE Autobackup at 23-SEP-17
RMAN> exit
Recovery Manager complete.
Moving backup to target server for duplicating database
scp backup_0* 192.168.56.110:/backup_rman/
oracle@192.168.56.110's password:
Permission denied, please try again.
oracle@192.168.56.110's password:
backup_02sf1kkt_1_3 100% 17MB 66.9MB/s 00:00
backup_04sf1knc_1_3 100% 17MB 63.8MB/s 00:00
backup_06sf1knl_1_3 100% 17MB 64.7MB/s 00:00
backup_08sf1knv_1_3 100% 17MB 64.4MB/s 00:00
backup_0asf1koh_1_3 100% 17MB 58.9MB/s 00:00
backup_0csf1kop_1_3 100% 17MB 56.7MB/s 00:00
backup_0esf1kou_1_3 100% 17MB 60.9MB/s 00:00
backup_0gsf1kp7_1_3 100% 17MB 63.0MB/s 00:00
backup_0isf1kpb_1_3 100% 17MB 64.8MB/s 00:00
backup_0jsf6aca_1_3 100% 4905KB 57.6MB/s 00:00
backup_0ksf6acd_1_2 100% 1194MB 62.1MB/s 00:19
backup_0lsf6ack_1_2 100% 608MB 63.5MB/s 00:09
backup_0msf6acn_1_2 100% 607MB 65.7MB/s 00:09
backup_0nsf6acr_1_2 100% 4096 1.3MB/s 00:00
backup_0osf6act_1_2
To create standby controlfile from primary
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/agap_sb.ctl';
scp agap_sb.ctl 192.168.56.110:/u01/app/oracle/product/12.2.0.1/db_1/dbs/
To create initagap_sb.ora for using within standby
Create pfile='/tmp/initagap_sb.ora' from spfile;
scp initagap_sb.ora 192.168.56.110:/u01/app/oracle/product/12.2.0.1/db_1/dbs/
you must modified initagap_sb.ora for using as agap_sb
vi initagap_sb.ora
:%s/agap/agap_sb
:%s/AGAP/AGAP_SB
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/agap_sb'
*.db_name='agap_sb'
*.db_unique_name='agap'
*.db_create_file_dest=/u01/app/oracle/oradata''
*.db_create_online_log_dest_1='/u01/app/oracle/oradata'
*.db_create_online_log_dest_2='/u01/app/oracle/oradata'
*.control_files='/u01/app/oracle/oradata/agap_sb/control01.ctl','/u01/app/oracle/oradata/agap_sb/control02.ctl'
To create new directories
mkdir -p /u01/app/oracle/fast_recovery_area/agap_sb
mkdir -p /u01/app/oracle/admin/agap_sb/adump
mkdir -p /u01/app/oracle/oradata/agap_sb
To copy orapwd
scp orapwagap 192.168.56.110:/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapwagap_sb
to check connection between primary database and standby database
(both nodes)
tnsping agap
tnsping agap_sb
To start instance
echo $ORACLE_SID
agap_sb
sqlplus / as sysdba
startup nomount
Duplicating
#rman target sys/123456@AGAP auxiliary / catalog rman/rman@RMAN
RMAN> duplicate target database for standby;
for Oracle Database Administrators
