[10/04] RMAN one host Twice DB

1. 아카이브 모드 전환

SYS@orcl>alter system set log_archive_format = 'orcl_%t_%s_%r.dbf' scope=spfile;

System altered.

SYS@orcl>alter system set log_archive_dest_1 = 'location=/ARC/';

System altered.

SYS@orcl>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@orcl>startup mount

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size    1339824 bytes

Variable Size  507514448 bytes

Database Buffers  335544320 bytes

Redo Buffers    5132288 bytes

Database mounted.

SYS@orcl>alter database archivelog;

Database altered.

SYS@orcl>archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /ARC/

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence       6

SYS@orcl>alter database open;

Database altered.

===prod db===

SYS@prod>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@prod>startup mount

ORACLE instance started.

Total System Global Area  636100608 bytes

Fixed Size    1338392 bytes

Variable Size  192938984 bytes

Database Buffers  436207616 bytes

Redo Buffers    5615616 bytes

Database mounted.

SYS@prod>alter database archivelog;

Database altered.

SYS@prod>alter database open;

Database altered.

SYS@prod>archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /u01/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online log sequence     4

Next log sequence to archive   6

Current log sequence       6

2. tnsnames.ora 및 listener.ora 설정

[oracle@lnx04 admin]$ cat listener.ora 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx04)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@lnx04 admin]$ cat tnsnames.ora 

orcl =

 ( description =

  ( address_list =

   ( address = (protocol=tcp)(host=lnx04.ocmkorea.com)(port=1521))

  )

  ( connect_data =

   (server = dedicated)

   (sid = orcl)

  )

 )

rcat =

 ( description =

  ( address_list =

   ( address = (protocol=tcp)(host=lnx04.ocmkorea.com)(port=1521))

  )

  ( connect_data =

   ( server = dedicated )

   ( sid = prod )

  )

 )

3. recovery catalog db 설정

[oracle@lnx04 admin]$ sqlplus system/oracle@prod

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 4 11:27:13 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM@prod>conn sys/oracle as sysdba

Connected.

SYS@orcl>grant sysdba to system;

Grant succeeded.

SYS@orcl>select * from v$pwfile_users;

USERNAME       SYSDB SYSOP SYSAS

------------------------------ ----- ----- -----

SYS       TRUE  TRUE  FALSE

SYSTEM       TRUE  FALSE FALSE

SYS@orcl>conn system/oracle@prod as sysdba

Connected.

SYS@prod>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@lnx04 admin]$ sqlplus system/oracle@rcat as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 4 11:28:32 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@rcat>create tablespace rman_tbs

    datafile '/u01/app/oracle/oradata/prod/rman_tbs01.dbf' size 100m

    autoextend on next 10m maxsize 1g

    segment space management auto;  

Tablespace created.

SYS@rcat>create user rman_admin identified by oracle

    default tablespace rman_tbs

    temporary tablespace temp

    quota unlimited on rman_tbs;

User created.

SYS@rcat>grant recovery_catalog_owner to rman_admin;

Grant succeeded.

================================================================================

RECOVERY_CATALOG_OWNER 롤 부여

 이 롤에는

  ALTER SESSION, CREATE CLUSTER, CREATE

  DATABASE LINK, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION,

  CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE 및 CREATE VIEW 시스템 권한이 포함

================================================================================

SYS@rcat>select * from v$tablespace; 

       TS# NAME  INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

0 SYSTEM  YES NO  YES

1 SYSAUX  YES NO  YES

2 UNDOTBS1  YES NO  YES

4 USERS  YES NO  YES

3 TEMP  NO  NO  YES

6 RMAN_TBS  YES NO  YES

6 rows selected.

SYS@rcat>conn rman_admin/oracle@rcat

Connected.

RMAN_ADMIN@rcat>select * from rc_database;

select * from rc_database

              *

ERROR at line 1:

ORA-00942: table or view does not exist

4. rman 접속 및 catalog tablespace 생성과 등록 조회

[oracle@lnx04 admin]$ rman target system/oracle@orcl catalog rman_admin/oracle@rcat

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Oct 4 11:59:45 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights rese

connected to target database: ORCL (DBID=1419922387)

connected to recovery catalog database

RMAN> create catalog tablespace "RMAN_TBS";

recovery catalog created

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

2       19      ORCL     1419922387       PARENT  1          13-AUG-09

2       4       ORCL     1419922387       CURRENT 754488     03-OCT-15

RMAN> exit

Recovery Manager complete.

[oracle@lnx04 admin]$ sqlplus rman_admin/oracle@rcat

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 4 12:08:51 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

RMAN_ADMIN@rcat>select * from rc_database;

    DB_KEY  DBINC_KEY    DBID NAME  RESETLOGS_CHANGE# RESETLOGS

---------- ---------- ---------- -------- ----------------- ---------

2    4 1419922387 ORCL     754488 03-OCT-15

5. RMAN Configure

RMAN> CONFIGURE RETENTION POLICY TO recovery window of 7 days;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> CONFIGURE BACKUP OPTIMIZATION on;

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

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> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/rman_con/%F';

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/rman_con/%F';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> CONFIGURE MAXSETSIZE TO 1g;

new RMAN configuration parameters:

CONFIGURE MAXSETSIZE TO 1 G;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure channel device type disk maxpiecesize 100m format '/BACKUP/rman/%d_%s_%p';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 100 M FORMAT   '/BACKUP/rman/%d_%s_%p';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/BACKUP/rman_con/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 100 M FORMAT   '/BACKUP/rman/%d_%s_%p';

CONFIGURE MAXSETSIZE TO 1 G;

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default

6. Full backup / close backup / rman_tbs open backup

RMAN> backup database plus archivelog;

Starting backup at 04-OCT-15

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=38 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=6 RECID=1 STAMP=892213951

channel ORA_DISK_1: starting piece 1 at 04-OCT-15

channel ORA_DISK_2: starting archived log backup set

channel ORA_DISK_2: specifying archived log(s) in backup set

input archived log thread=1 sequence=7 RECID=2 STAMP=892214261

input archived log thread=1 sequence=8 RECID=3 STAMP=892214466

channel ORA_DISK_2: starting piece 1 at 04-OCT-15

channel ORA_DISK_1: finished piece 1 at 04-OCT-15

piece handle=/BACKUP/rman/ORCL_5_1 tag=TAG20151004T132107 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_2: finished piece 1 at 04-OCT-15

piece handle=/BACKUP/rman/ORCL_6_1 tag=TAG20151004T132107 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01

Finished backup at 04-OCT-15

Starting backup at 04-OCT-15

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 04-OCT-15

channel ORA_DISK_2: starting full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_2: starting piece 1 at 04-OCT-15

channel ORA_DISK_1: finished piece 1 at 04-OCT-15

piece handle=/BACKUP/rman/ORCL_7_1 tag=TAG20151004T132108 comment=NONE

channel ORA_DISK_1: starting piece 2 at 04-OCT-15

channel ORA_DISK_1: finished piece 2 at 04-OCT-15

~

Finished backup at 04-OCT-15

Starting backup at 04-OCT-15

current log archived

using channel ORA_DISK_1

using channel ORA_DISK_2

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=9 RECID=4 STAMP=892214501

channel ORA_DISK_1: starting piece 1 at 04-OCT-15

channel ORA_DISK_1: finished piece 1 at 04-OCT-15

piece handle=/BACKUP/rman/ORCL_9_1 tag=TAG20151004T132142 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 04-OCT-15

Starting Control File and SPFILE Autobackup at 04-OCT-15

piece handle=/BACKUP/rman_con/c-1419922387-20151004-00 comment=NONE

Finished Control File and SPFILE Autobackup at 04-OCT-15

RMAN> list backup of database;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

95      Full    380.38M    DISK        00:00:25     04-OCT-15      

  List of Datafiles in backup set 95

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  2       Full 829998     04-OCT-15 /u01/app/oracle/oradata/orcl/sysaux01.dbf

  3       Full 829998     04-OCT-15 /u01/app/oracle/oradata/orcl/undotbs01.dbf

  Backup Set Copy #1 of backup set 95

  Device Type Elapsed Time Completion Time Compressed Tag

  ----------- ------------ --------------- ---------- ---

  DISK        00:00:25     04-OCT-15       NO         TAG20151004T132108

    List of Backup Pieces for backup set 95 Copy #1

    BP Key  Pc# Status      Piece Name

    ------- --- ----------- ----------

    100     1   AVAILABLE   /BACKUP/rman/ORCL_8_1

    101     2   AVAILABLE   /BACKUP/rman/ORCL_8_2

    102     3   AVAILABLE   /BACKUP/rman/ORCL_8_3

    103     4   AVAILABLE   /BACKUP/rman/ORCL_8_4

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

96      Full    585.48M    DISK        00:00:31     04-OCT-15      

  List of Datafiles in backup set 96

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  1       Full 829996     04-OCT-15 /u01/app/oracle/oradata/orcl/system01.dbf

  4       Full 829996     04-OCT-15 /u01/app/oracle/oradata/orcl/users01.dbf

  Backup Set Copy #1 of backup set 96

  Device Type Elapsed Time Completion Time Compressed Tag

  ----------- ------------ --------------- ---------- ---

  DISK        00:00:31     04-OCT-15       NO         TAG20151004T132108

    List of Backup Pieces for backup set 96 Copy #1

    BP Key  Pc# Status      Piece Name

    ------- --- ----------- ----------

    104     1   AVAILABLE   /BACKUP/rman/ORCL_7_1

    105     2   AVAILABLE   /BACKUP/rman/ORCL_7_2

    106     3   AVAILABLE   /BACKUP/rman/ORCL_7_3

    107     4   AVAILABLE   /BACKUP/rman/ORCL_7_4

    108     5   AVAILABLE   /BACKUP/rman/ORCL_7_5

    109     6   AVAILABLE   /BACKUP/rman/ORCL_7_6

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN> exit

Recovery Manager complete.

[oracle@lnx04 ~]$ cp /u01/app/oracle/oradata/orcl/* /BACKUP/close/

[oracle@lnx04 ~]$ ss

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 4 13:26:54 2015

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

Connected to an idle instance.

SYS@orcl>startup

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size    1339824 bytes

Variable Size  507514448 bytes

Database Buffers  335544320 bytes

Redo Buffers    5132288 bytes

Database mounted.

Database opened.

SYS@prod>alter tablespace rman_tbs begin backup;

alter tablespace rman_tbs begin backup

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery not enabled

rcatdb가 noarchivemode 라서 발생하는 error

archivemode로 변경 후

[oracle@lnx04 ~]$ sqlplus system/oracle@rcat

SQL*Plus: Release 11.2.0.1.0 Production on Sun Oct 4 13:35:09 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM@rcat>alter tablespace rman_tbs begin backup;

Tablespace altered.

SYSTEM@rcat>!cp /u01/app/oracle/oradata/prod/rman_tbs01.dbf /BACKUP/open

SYSTEM@rcat>alter tablespace rman_tbs end backup;

Tablespace altered.

출처 http://cafe.naver.com/ocmkorea 이현룡 교수님 강의자료보고 참고했습니다.

기타 설정

alias or='export ORACLE_SID=orcl'

alias pr='export ORACLE_SID=prod'

alias ec='echo $ORACLE_SID'

alias rtc='rman target system/oracle@orcl catalog rman_admin/oracle@rcat'