2019年11月22日 星期五
Oracle DB - 設定 RMAN backup
--Oracle Catalog DB: DB11G@testdb88
--Create tablespace RMAN
CREATE TABLESPACE CATALOG_TBS DATAFILE
'/u01/app/oracle/oradata/DB11G/catalog_tbs01.dbf' SIZE 5M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
--Create DB user: rman_ora11g
CREATE USER RMAN_ORA11G
IDENTIFIED BY "1qaz2wsx"
DEFAULT TABLESPACE CATALOG_TBS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for RMAN_ORA11G
GRANT CONNECT TO RMAN_ORA11G;
GRANT RECOVERY_CATALOG_OWNER TO RMAN_ORA11G;
ALTER USER RMAN_ORA11G DEFAULT ROLE ALL;
-- 1 System Privilege for RMAN_ORA11G
GRANT UNLIMITED TABLESPACE TO RMAN_ORA11G;
--Create Catalog
[oracle@testdb1 admin]$ rman
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 1 16:56:34 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect catalog rman_ora11g/xxxxxxxx@DB11G
connected to recovery catalog database
RMAN> CREATE CATALOG TABLESPACE catalog_tbs;
recovery catalog created
RMAN>
--Register Database
[oracle@testdb1 admin]$ rman target / catalog rman_ora11g/xxxxxxxx@catalog
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 1 17:10:25 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRD (DBID=2086780556)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> report schema;
Report of database schema for database with db_unique_name PRD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM YES /u02/oradata/PRD/system01.dbf
2 540 SYSAUX NO /u02/oradata/PRD/sysaux01.dbf
3 105 UNDOTBS1 YES /u02/oradata/PRD/undotbs01.dbf
4 5 USERS NO /u02/oradata/PRD/users01.dbf
5 313 EXAMPLE NO /u02/oradata/PRD/example01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u02/oradata/PRD/temp01.dbf
RMAN> show all
2> ;
RMAN configuration parameters for database with db_unique_name PRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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_PRD.f'; # default
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> show all
2> ;
RMAN configuration parameters for database with db_unique_name PRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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_PRD.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN>
RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/backup/%d_Full_%Y%M%D_%u.bak';
BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/u03/backup/%d_Control_%Y%M%D_%u.bak';
BACKUP AS BACKUPSET DATABASE;
RELEASE CHANNEL disk1;
}
released channel: ORA_DISK_1
allocated channel: disk1
channel disk1: SID=372 device type=DISK
Starting backup at 04-NOV-19
channel disk1: starting datafile copy
copying current control file
output file name=/u03/backup/PRD_Control_20191104_0bug1589.bak tag=TAG20191104T111209 RECID=5 STAMP=1023448329
channel disk1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-NOV-19
Starting backup at 04-NOV-19
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/PRD/system01.dbf
input datafile file number=00002 name=/u02/oradata/PRD/sysaux01.dbf
input datafile file number=00005 name=/u02/oradata/PRD/example01.dbf
input datafile file number=00003 name=/u02/oradata/PRD/undotbs01.dbf
input datafile file number=00004 name=/u02/oradata/PRD/users01.dbf
channel disk1: starting piece 1 at 04-NOV-19
channel disk1: finished piece 1 at 04-NOV-19
piece handle=/u03/backup/PRD_Full_20191104_0cug158b.bak tag=TAG20191104T111211 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:35
channel disk1: starting full datafile backup set
channel disk1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel disk1: starting piece 1 at 04-NOV-19
channel disk1: finished piece 1 at 04-NOV-19
piece handle=/u03/backup/PRD_Full_20191104_0dug159e.bak tag=TAG20191104T111211 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-NOV-19
released channel: disk1
RMAN> list backup
2> ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
463 Full 1.16G DISK 00:00:29 04-NOV-19
BP Key: 467 Status: AVAILABLE Compressed: NO Tag: TAG20191104T105948
Piece Name: /u03/backup/PRD_Full_20191104_09ug14h4.bak
List of Datafiles in backup set 463
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1393036 04-NOV-19 /u02/oradata/PRD/system01.dbf
2 Full 1393036 04-NOV-19 /u02/oradata/PRD/sysaux01.dbf
3 Full 1393036 04-NOV-19 /u02/oradata/PRD/undotbs01.dbf
4 Full 1393036 04-NOV-19 /u02/oradata/PRD/users01.dbf
5 Full 1393036 04-NOV-19 /u02/oradata/PRD/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
464 Full 9.36M DISK 00:00:01 04-NOV-19
BP Key: 468 Status: AVAILABLE Compressed: NO Tag: TAG20191104T105948
Piece Name: /u03/backup/PRD_Full_20191104_0aug14i7.bak
SPFILE Included: Modification time: 03-NOV-19
SPFILE db_unique_name: PRD
Control File Included: Ckp SCN: 1393229 Ckp time: 04-NOV-19
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
593 Full 1.16G DISK 00:00:30 04-NOV-19
BP Key: 597 Status: AVAILABLE Compressed: NO Tag: TAG20191104T111211
Piece Name: /u03/backup/PRD_Full_20191104_0cug158b.bak
List of Datafiles in backup set 593
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1394382 04-NOV-19 /u02/oradata/PRD/system01.dbf
2 Full 1394382 04-NOV-19 /u02/oradata/PRD/sysaux01.dbf
3 Full 1394382 04-NOV-19 /u02/oradata/PRD/undotbs01.dbf
4 Full 1394382 04-NOV-19 /u02/oradata/PRD/users01.dbf
5 Full 1394382 04-NOV-19 /u02/oradata/PRD/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
594 Full 9.36M DISK 00:00:01 04-NOV-19
BP Key: 598 Status: AVAILABLE Compressed: NO Tag: TAG20191104T111211
Piece Name: /u03/backup/PRD_Full_20191104_0dug159e.bak
SPFILE Included: Modification time: 03-NOV-19
SPFILE db_unique_name: PRD
Control File Included: Ckp SCN: 1394398 Ckp time: 04-NOV-19
RMAN>
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 463 04-NOV-19
Backup Piece 467 04-NOV-19 /u03/backup/PRD_Full_20191104_09ug14h4.bak
Archive Log 547 04-NOV-19 /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_25_gvz5o5ko_.arc
Backup Set 464 04-NOV-19
Backup Piece 468 04-NOV-19 /u03/backup/PRD_Full_20191104_0aug14i7.bak
Archive Log 548 04-NOV-19 /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_26_gvz5o6py_.arc
Archive Log 549 04-NOV-19 /u01/app/oracle/fast_recovery_area/PRD/archivelog/2019_11_04/o1_mf_1_27_gvz5o851_.arc
Control File Copy 576 04-NOV-19 /u03/backup/PRD_Control_20191104_0bug1589.bak
2019年11月21日 星期四
Oracle DB - 11g 使用 DBNEWID utility (nid) 指定 new DBNAME db_id
舊 db_name: PRD
新 db_name: QAS
STEP 1: Backup the database.
STEP 2: Mount the database after a clean shutdown:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
STEP 3: 使用 DBNEWID utility (nid) 指定 new DBNAME from the command line using a user with SYSDBA privilege:
[oracle@testdb2 ~]$ nid TARGET=sys/xxxxxxxxx DBNAME=QAS
DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 21 16:05:19 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database PRD (DBID=2086780556)
Connected to server version 11.2.0
Control Files in database:
/u02/oradata/PRD/control01.ctl
/u01/app/oracle/fast_recovery_area/PRD/control02.ctl
Change database ID and database name PRD to QAS? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2086780556 to 2414354175
Changing database name from PRD to QAS
Control File /u02/oradata/PRD/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - modified
Datafile /u02/oradata/PRD/system01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/sysaux01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/undotbs01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/users01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/example01.db - dbid changed, wrote new name
Datafile /u02/oradata/QAS/test01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/test2/test2.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/temp01.db - dbid changed, wrote new name
Control File /u02/oradata/PRD/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to QAS.
Modify parameter file and generate a new password file before restarting.
Database ID for database QAS changed to 2414354175.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
STEP 5: Modify the DB_NAME parameter. The startup will result in an error but proceed anyway.
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:11:10 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
ORA-01103: database name 'QAS' in control file is not 'PRD'
--這是因為spfile仍然記錄舊的db_name
SQL> alter system set db_name=QAS scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb2 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@testdb2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@testdb2 admin]$
[oracle@testdb2 admin]$ more tnsnames.ora
QAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QAS)
)
)
PRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRD)
)
)
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
lsnrctl reload
[oracle@testdb2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@testdb2 dbs]$ ls
hc_PRD.dat init.ora lkPRD orapwPRD spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:16:36 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
--一些觀察
-- DB Name 改為 QAS
SQL> select name from v$database;
NAME
---------
QAS
-- Instance Name 維持PRD
SQL> select instance from v$thread ;
INSTANCE
--------------------------------------------------------------------------------
PRD
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
PRD
[oracle@testdb2 dbs]$ ps -ef|grep ora_
oracle 3424 1 0 16:16 ? 00:00:00 ora_pmon_PRD
oracle 3448 1 0 16:16 ? 00:00:00 ora_smon_PRD
-- $ORACLE_HOME/dbs 多一個 lkQAS
[oracle@testdb2 admin]$ cd $ORACLE_HOME
[oracle@testdb2 db_1]$ cd dbs
[oracle@testdb2 dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Nov 21 16:17 hc_PRD.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Nov 18 16:29 lkPRD
-rw-r-----. 1 oracle oinstall 24 Nov 21 16:16 lkQAS
-rw-r-----. 1 oracle oinstall 1536 Nov 18 15:55 orapwPRD
-rw-r-----. 1 oracle oinstall 2560 Nov 21 16:18 spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:24:42 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilePRD.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/oradata/PRD/control01.ctl
, /u01/app/oracle/fast_recover
y_area/PRD/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/PRD/temp01.dbf
SQL> select DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME
------------------------------
QAS
[oracle@testdb2 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-NOV-2019 16:52:39
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 21-NOV-2019 16:51:28
Uptime 0 days 0 hr. 1 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/testdb2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PRDXDB" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
Service "QAS" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
The command completed successfully
[root@testdb2 ~]# cd /u01/app/oracle
[root@testdb2 oracle]# ll
total 20
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:22 admin
drwxr-xr-x. 2 oracle oinstall 4096 Oct 30 16:41 checkpoints
drwxrwxr-x. 11 oracle oinstall 4096 Oct 30 16:39 diag
drwxr-xr-x. 4 oracle oinstall 4096 Nov 21 16:17 fast_recovery_area
drwxrwxr-x. 3 oracle oinstall 4096 Oct 30 15:43 product
[root@testdb2 oracle]# ll /u01/app/oracle/admin/PRD/
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:24 adump
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:58 PRD
drwxr-x---. 3 oracle oinstall 4096 Nov 21 16:17 QAS
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 21 16:17 onlinelog
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/onlinelog/
[root@testdb2 oracle]# su - oracle
[oracle@testdb2 ~]$ env|grep ORA
ORACLE_UNQNAME=PRD
ORACLE_SID=PRD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb2 ~]$ cd $ORACLE_BASE
[oracle@testdb2 oracle]$ ls
admin checkpoints diag fast_recovery_area product
[oracle@testdb2 oracle]$ cd diag/rdbms/
[oracle@testdb2 rdbms]$ ll
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:14 prd
drwxr-xr-x. 3 oracle oinstall 4096 Nov 21 16:16 qas
[oracle@testdb2 rdbms]$ cd qas
[oracle@testdb2 qas]$ ll
total 4
-rw-r-----. 1 oracle oinstall 0 Nov 21 16:16 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Nov 21 16:16 PRD
[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 alert
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 hm
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incident
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 ir
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 lck
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 stage
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:23 trace
[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_*.log
-rw-r-----. 1 oracle oinstall 6758 Nov 21 16:22 /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_PRD.log
[oracle@testdb2 trace]$
新 db_name: QAS
STEP 1: Backup the database.
STEP 2: Mount the database after a clean shutdown:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
STEP 3: 使用 DBNEWID utility (nid) 指定 new DBNAME from the command line using a user with SYSDBA privilege:
[oracle@testdb2 ~]$ nid TARGET=sys/xxxxxxxxx DBNAME=QAS
DBNEWID: Release 11.2.0.4.0 - Production on Thu Nov 21 16:05:19 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database PRD (DBID=2086780556)
Connected to server version 11.2.0
Control Files in database:
/u02/oradata/PRD/control01.ctl
/u01/app/oracle/fast_recovery_area/PRD/control02.ctl
Change database ID and database name PRD to QAS? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2086780556 to 2414354175
Changing database name from PRD to QAS
Control File /u02/oradata/PRD/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - modified
Datafile /u02/oradata/PRD/system01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/sysaux01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/undotbs01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/users01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/example01.db - dbid changed, wrote new name
Datafile /u02/oradata/QAS/test01.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/test2/test2.db - dbid changed, wrote new name
Datafile /u02/oradata/PRD/temp01.db - dbid changed, wrote new name
Control File /u02/oradata/PRD/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/PRD/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to QAS.
Modify parameter file and generate a new password file before restarting.
Database ID for database QAS changed to 2414354175.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
STEP 5: Modify the DB_NAME parameter. The startup will result in an error but proceed anyway.
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:11:10 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
ORA-01103: database name 'QAS' in control file is not 'PRD'
--這是因為spfile仍然記錄舊的db_name
SQL> alter system set db_name=QAS scope=spfile;
System altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
- STEP 6: Create a new password file: (選用)
orapwd file=$ORACLE_HOME/dbs/orapwQAS password=password entries=10
- STEP 7: Rename the SPFILE to match the new DBNAME. (選用)
- STEP 8:ORACLE_SID=QAS; export ORACLE_SID (選用)
- STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
[oracle@testdb2 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@testdb2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@testdb2 admin]$
[oracle@testdb2 admin]$ more tnsnames.ora
QAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = QAS)
)
)
PRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRD)
)
)
DB11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
CATALOG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = testdb88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB11G)
)
)
lsnrctl reload
- STEP 10: Open the database with RESETLOGS:
[oracle@testdb2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@testdb2 dbs]$ ls
hc_PRD.dat init.ora lkPRD orapwPRD spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:16:36 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1056967720 bytes
Database Buffers 587202560 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
- STEP 11: Backup the database.
--一些觀察
-- DB Name 改為 QAS
SQL> select name from v$database;
NAME
---------
QAS
-- Instance Name 維持PRD
SQL> select instance from v$thread ;
INSTANCE
--------------------------------------------------------------------------------
PRD
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
PRD
[oracle@testdb2 dbs]$ ps -ef|grep ora_
oracle 3424 1 0 16:16 ? 00:00:00 ora_pmon_PRD
oracle 3448 1 0 16:16 ? 00:00:00 ora_smon_PRD
-- $ORACLE_HOME/dbs 多一個 lkQAS
[oracle@testdb2 admin]$ cd $ORACLE_HOME
[oracle@testdb2 db_1]$ cd dbs
[oracle@testdb2 dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Nov 21 16:17 hc_PRD.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Nov 18 16:29 lkPRD
-rw-r-----. 1 oracle oinstall 24 Nov 21 16:16 lkQAS
-rw-r-----. 1 oracle oinstall 1536 Nov 18 15:55 orapwPRD
-rw-r-----. 1 oracle oinstall 2560 Nov 21 16:18 spfilePRD.ora
[oracle@testdb2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 21 16:24:42 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilePRD.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/oradata/PRD/control01.ctl
, /u01/app/oracle/fast_recover
y_area/PRD/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/PRD/temp01.dbf
SQL> select DB_UNIQUE_NAME from v$database;
DB_UNIQUE_NAME
------------------------------
QAS
[oracle@testdb2 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-NOV-2019 16:52:39
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 21-NOV-2019 16:51:28
Uptime 0 days 0 hr. 1 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/testdb2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PRDXDB" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
Service "QAS" has 1 instance(s).
Instance "PRD", status READY, has 1 handler(s) for this service...
The command completed successfully
[root@testdb2 ~]# cd /u01/app/oracle
[root@testdb2 oracle]# ll
total 20
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:22 admin
drwxr-xr-x. 2 oracle oinstall 4096 Oct 30 16:41 checkpoints
drwxrwxr-x. 11 oracle oinstall 4096 Oct 30 16:39 diag
drwxr-xr-x. 4 oracle oinstall 4096 Nov 21 16:17 fast_recovery_area
drwxrwxr-x. 3 oracle oinstall 4096 Oct 30 15:43 product
[root@testdb2 oracle]# ll /u01/app/oracle/admin/PRD/
total 4
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:24 adump
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:58 PRD
drwxr-x---. 3 oracle oinstall 4096 Nov 21 16:17 QAS
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 21 16:17 onlinelog
[oracle@testdb2 dbs]$ ll /u01/app/oracle/fast_recovery_area/QAS/onlinelog/
[root@testdb2 oracle]# su - oracle
[oracle@testdb2 ~]$ env|grep ORA
ORACLE_UNQNAME=PRD
ORACLE_SID=PRD
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb1
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb2 ~]$ cd $ORACLE_BASE
[oracle@testdb2 oracle]$ ls
admin checkpoints diag fast_recovery_area product
[oracle@testdb2 oracle]$ cd diag/rdbms/
total 8
drwxr-xr-x. 3 oracle oinstall 4096 Nov 18 16:14 prd
drwxr-xr-x. 3 oracle oinstall 4096 Nov 21 16:16 qas
[oracle@testdb2 rdbms]$ cd qas
[oracle@testdb2 qas]$ ll
total 4
-rw-r-----. 1 oracle oinstall 0 Nov 21 16:16 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Nov 21 16:16 PRD
[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 alert
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 hm
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incident
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 ir
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 lck
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 stage
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:16 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Nov 21 16:23 trace
[oracle@testdb2 trace]$ ll /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_*.log
-rw-r-----. 1 oracle oinstall 6758 Nov 21 16:22 /u01/app/oracle/diag/rdbms/qas/PRD/trace/alert_PRD.log
[oracle@testdb2 trace]$
2019年11月15日 星期五
Oracle DB - linux based ORACLE_SID 大小寫區分 case sensitivity
[root@testdb88 build]# su - oracle
[oracle@testdb88 ~]$ env|grep ORA
ORACLE_UNQNAME=DB11G
ORACLE_SID=DB11G
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb88
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb88 ~]$ export ORACLE_SID=db11g
[oracle@testdb88 ~]$ env|grep ORA
ORACLE_UNQNAME=DB11G
ORACLE_SID=db11g
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=testdb88
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@testdb88 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 13:05:01 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@testdb88 ~]$ export ORACLE_SID=DB11G
[oracle@testdb88 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 15 13:05:26 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Perl script 錯誤訊息 Can't locate Mail/Sendmail.pm 解決辦法
在新的Linux Server(Oracle Enterprise Linux Server 6.8))上配置tablespace空間告警的perl script後,測試時報錯如下
[oracle@testdb88 ~]$ ~/perl/mailTableSpaceSize.pl bypass
Can't locate Mail/Sendmail.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /home/oracle/perl/mailTableSpaceSize.pl line 6.
BEGIN failed--compilation aborted at /home/oracle/perl/mailTableSpaceSize.pl line 6.
Perl腳本mailTableSpaceSize.pl如下所示,其中使用了Mail::Sendmail郵件模塊。
#!/usr/bin/perl
use DBI;
use Mail::Sendmail;
use MIME::QuotedPrint;
use MIME::Base64;
use Encode;
use Number::Format qw(:subs);
use DateTime qw();
出現這個錯誤是因為Perl沒有安裝Mail::Sendmail模組,安裝此模組問題即可解決,可以使用下面幾種方式安裝。 (使用cpan安裝,Server要能夠連上網路,否則無法下載相關package。
perl -MCPAN -e shell
install Mail::Sendmail
cpanm
cpanm Mail::Sendmail
make
make test
make install
https://metacpan.org/pod/Mail::Sendmail
How to install CPAN modules
https://www.cpan.org/modules/INSTALL.html
Perl 教程
https://www.runoob.com/perl/perl-tutorial.html
[oracle@testdb88 ~]$ ~/perl/mailTableSpaceSize.pl bypass
Can't locate Mail/Sendmail.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /home/oracle/perl/mailTableSpaceSize.pl line 6.
BEGIN failed--compilation aborted at /home/oracle/perl/mailTableSpaceSize.pl line 6.
Perl腳本mailTableSpaceSize.pl如下所示,其中使用了Mail::Sendmail郵件模塊。
#!/usr/bin/perl
use DBI;
use Mail::Sendmail;
use MIME::QuotedPrint;
use MIME::Base64;
use Encode;
use Number::Format qw(:subs);
use DateTime qw();
出現這個錯誤是因為Perl沒有安裝Mail::Sendmail模組,安裝此模組問題即可解決,可以使用下面幾種方式安裝。 (使用cpan安裝,Server要能夠連上網路,否則無法下載相關package。
[Best]
CPAN shellperl -MCPAN -e shell
install Mail::Sendmail
cpanm
cpanm Mail::Sendmail
[Traditional]
perl Makefile.PLmake
make test
make install
[Manual]
Copy Sendmail.pm to Mail/ in your Perl lib directory.參考資訊:
Neil Bowers / Mail-Sendmail-0.80 ++ / Mail::Sendmailhttps://metacpan.org/pod/Mail::Sendmail
How to install CPAN modules
https://www.cpan.org/modules/INSTALL.html
Perl 教程
https://www.runoob.com/perl/perl-tutorial.html
2019年11月12日 星期二
Oracle Database Offerings 版本與縮寫 Oracle Multitenant - # of PDBs 多租戶
Oracle數據庫19c | 每個CDB最多3個PDB,無需許可多租戶
- 對於所有產品,如果您沒有獲得Oracle Multitenant的許可,則給定容器數據庫中隨時可以有多達3個用戶創建的PDB。
- EE:額外費用選項; 如果您已獲得Oracle Multitenant的許可,則最多可以創建252個PDB。
- EE-ES:額外費用選項; 如果您已獲得Oracle Multitenant的許可,則最多可以創建4096個PDB
- DBCS EE-HP,DBCS EE-EP和ExaCS:包含選項; 您最多可以創建4096個PDB。
Oracle Database Offerings
Offering | 提供型態 | 縮寫 | 說明 |
Oracle Database Standard Edition 2 | On-Premises | SE2 | Oracle數據庫標準版2包含開發工作組,部門級和Web應用程序所必需的功能。 |
Oracle Database Enterprise Edition | On-Premises | EE | Oracle數據庫企業版為開發應用程序提供了性能,可用性,可擴展性和安全性,例如大容量在線事務處理(OLTP)應用程序,查詢密集型數據倉庫和要求苛刻的Internet應用程序。
可以通過購買Oracle數據庫選件和Oracle管理包來增強Oracle數據庫企業版。 |
Oracle Database Enterprise Edition on Engineered Systems | On-Premises | EE-ES | 在本地工程系統(Oracle數據庫雲服務器或Oracle數據庫機)上安裝的Oracle數據庫企業版軟件。
包括Oracle數據庫的所有組件。您可以通過購買Oracle數據庫選件和Oracle管理包來進一步增強此功能。 EE-ES的許可策略取決於它是安裝在Oracle數據庫雲服務器還是Oracle數據庫機上。請務必注意這些差異,這些差異將在本指南的後續部分中進行記錄。 |
Oracle Database Personal Edition | On-Premises | PE | Oracle數據庫個人版支持單用戶開發和部署環境,這些環境要求與Oracle數據庫標準版2和Oracle數據庫企業版完全兼容。
包括Enterprise Edition隨附的所有組件以及所有Oracle Database選項,但Oracle RAC One Node和Oracle Real Application Clusters選項無法與Personal Edition一起使用。 Personal Edition僅在Linux和Windows平台上可用。 Oracle管理包不能與個人版一起使用。 |
Offering | 提供型態 | 縮寫 | 說明 |
Oracle Database Cloud
Service Standard Edition | Cloud | DBCS SE | 包括Oracle數據庫標準版2軟件。 |
Oracle Database Cloud
Service Enterprise Edition | Cloud | DBCS EE | 包括Oracle數據庫企業版軟件。 |
Oracle Database Cloud
Service Enterprise Edition - High Performance | Cloud | DBCS EE-HP | 包括Oracle數據庫企業版軟件以及許多Oracle數據庫選件和Oracle管理包。 |
Oracle Database Cloud
Service Enterprise Edition - Extreme Performance | Cloud | DBCS EE-EP | 包括Oracle數據庫企業版軟件以及適用於Oracle數據庫雲服務的所有Oracle數據庫選件和Oracle管理包。 |
Oracle Database Exadata
Cloud Service | Cloud | ExaCS | 包括Oracle數據庫企業版軟件以及適用於Oracle數據庫數據庫雲服務器的所有Oracle數據庫選件和Oracle管理包。 ExaCS的許可政策也適用於客戶的Oracle Database Exadata Cloud。 |
2019年10月31日 星期四
常見的誤解 - 人不為己天誅地滅
節錄自 https://linriva.pixnet.net/blog/post/62534027-%E5%8D%97%E6%87%B7%E7%91%BE%EF%BC%9A%E3%80%8C%E4%BA%BA%E4%B8%8D%E7%82%BA%E5%B7%B1%EF%BC%8C%E5%A4%A9%E8%AA%85%E5%9C%B0%E6%BB%85%E3%80%8D%E5%8E%9F%E4%BE%86%E6%98%AF%E9%80%99%E5%80%8B
我一直以為,這句話的意思是:人如果不為自己謀利、謀權、謀色,就要招到天地誅殺。其實,這是誤解。
現在才知道,「人不為己天誅地滅」,出處是《佛說十善業道經》第二十四集::人生為己,天經地義,人不為己,天誅地滅。佛家的意思是:不殺生、不偷盜、不邪淫、不妄語、不兩舌、不绮語、不惡口、不貪欲、不嗔恚、不邪見,才是「為自己」。不為自己製造新的惡果,不為自己造成新的災禍,這才是「為自己」。只有這樣才不會天誅地滅。
2019年10月25日 星期五
SAP - Profile Administration (transaction RZ10) 參數設定
SAP建議使用事務T-CODE RZ10調整SAP配置文件。 但是,例如,如果由於錯誤的配置而無法再啟動SAP系統,則可以在文件系統級別上更改相應文件中的SAP參數。
下方例子
1. 修改參數 login/min_password_lng
2. 新增參數 login/min_password_digits
When using the helper file, remember to reload the profiles with transaction RZ10 afterwards! Utilities → Import profiles → Of active servers
a) First look up the current values of the profile parameters you want to change. Go to transaction RZ11 and enter the Param. Name, for example login/min_password_lng. Choose Display. Note the Current value. Repeat this for the other parameters you want to change.
b) To change the parameters in the SAP system, go to transaction RZ10. By using the F4 help select DEFAULT as Profile, select Extended Maintenance and choose Change.
c) To change an already existing parameter, for example login/min_password_lng, simply click into this row and choose Change
d) Enter the new value in the Parameter val. field, for example 8. Choose Copy and Back.
e) To add a new parameter choose Create. Enter the Parameter name, for example login/min_password_digits. Enter the Parameter val., for example 1. Choose Copy twice and Back.
f) In the parameter list choose Copy and Back.
g) Choose Save and in the popup choose No. In the Activate profile popup choose Yes. Then Continue twice.
h) Now restart your SAP system. First stop the Dialog Instance and afterwards the Central Instance. Start in the opposite order. Log on to operating system level of your SAP system and double click the desktop shortcut SAP Management Console. In the new window right click on one instance of your system and choose Stop. After it is stopped, right click and choose Start.
i) After the system is started again, log on and change your password in transaction SU3. If you set parameter login/password_compliance_to_current_ policy to 1 you may need to change your password directly after logging on. Try to violate any parameter you set. You successfully changed some password parameters of your AS ABAP based SAP system.
login/min_password_diff = 3
--Defines the minimum number of characters that must be different in the new password compared to the old password.
login/password_expiration_time = 90
#login/password_expiration_time 密碼有效期
#Default: 0
#Permissible values: 0 - 1000
login/min_password_lng = 6
--Default: 6
--Permissible values: 3 - 40
--Defines the minimum length of the password.
login/failed_user_auto_unlock = 0
--Defines whether user locks due to unsuccessful logon attempts are automatically removed at midnight.
login/no_automatic_user_sapstar = 0
--login/no_automatic_user_sapstar 用戶SAP* 能不能登錄, 0代表SAP*可以登錄,1代表SAP*不能登陸!
login/fails_to_user_lock = 6
--login/fails_to_user_lock 密碼輸錯多少次後鎖定
login/min_password_letters = 2
--Defines the minimum number of letters (A-Z) in passwords.
login/min_password_digits = 2
--Defines the minimum number of digits (0-9) in passwords.
login/system_client = 800
--login/system_client 登錄時默認的Client號
2019年10月24日 星期四
Oracle DB - Creating Linux Services for DB auto-start auto-stop Oracle 19c On OEL7
創建 Linux Services - dbora
Create a new service to automatically start/stop an Oracle database. This assumes the Oracle database is not using Oracle Restart and the "start_all.sh" and "stop_all.sh" scripts are already present.
# vi /lib/systemd/system/dbora.service
[Unit]
Description=The Oracle Database Service
After=syslog.target network.target
[Service]
# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=65535
#Type=simple
# idle: similar to simple, the actual execution of the service binary is delayed
# until all jobs are finished, which avoids mixing the status output with shell output of services.
RemainAfterExit=yes
User=oracle
Group=oinstall
Restart=no
ExecStart=/bin/bash -c '/home/oracle/scripts/start_all.sh'
ExecStop=/bin/bash -c '/home/oracle/scripts/stop_all.sh'
[Install]
WantedBy=multi-user.target
Reload systemd so it can see the new service.
# systemctl daemon-reload
Start the service and enable so it is automatically restarted on reboot.
# systemctl start dbora.service
# systemctl enable dbora.service
# systemctl status dbora.service
參考資訊:
Linux Services (systemd, systemctl)
Oracle DB - 安裝Oracle Database 19c 在 Oracle Linux 7
下載軟體
Download the Oracle software from OTN or MOS depending on your support status.OTN: Oracle Database 19c (19.3) Software (64-bit)
edelivery: Oracle Database 19c (19.3) Software (64-bit)
修改 Hosts File
“ /etc/hosts”文件必須包含服務器的標準名稱。安裝前置作業
[root@testdb81 ~]# yum install -y oracle-database-preinstall-19cLoaded plugins: langpacks, ulninfo
ol7_latest | 2.7 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-19c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: ksh for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Processing Dependency: libaio-devel for package: oracle-database-preinstall-19c-1.0-1.el7.x86_64
--> Running transaction check
---> Package ksh.x86_64 0:20120801-139.0.1.el7 will be installed
---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Installing:
oracle-database-preinstall-19c x86_64 1.0-1.el7 ol7_latest 18 k
Installing for dependencies:
ksh x86_64 20120801-139.0.1.el7 ol7_latest 883 k
libaio-devel x86_64 0.3.109-13.el7 ol7_latest 12 k
Transaction Summary
================================================================================================================================
Install 1 Package (+2 Dependent packages)
Total download size: 913 k
Installed size: 3.2 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/ol7_latest/packages/libaio-devel-0.3.109-13.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Public key for libaio-devel-0.3.109-13.el7.x86_64.rpm is not installed
(1/3): libaio-devel-0.3.109-13.el7.x86_64.rpm | 12 kB 00:00:00
(2/3): ksh-20120801-139.0.1.el7.x86_64.rpm | 883 kB 00:00:00
(3/3): oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm | 18 kB 00:00:02
--------------------------------------------------------------------------------------------------------------------------------
Total 328 kB/s | 913 kB 00:00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Importing GPG key 0xEC551F03:
Userid : "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"
Fingerprint: 4214 4123 fecf c55b 9086 313d 72f9 7b74 ec55 1f03
Package : 7:oraclelinux-release-7.6-1.0.15.el7.x86_64 (@anaconda/7.6)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : ksh-20120801-139.0.1.el7.x86_64 1/3
Installing : libaio-devel-0.3.109-13.el7.x86_64 2/3
Installing : oracle-database-preinstall-19c-1.0-1.el7.x86_64 3/3
Verifying : libaio-devel-0.3.109-13.el7.x86_64 1/3
Verifying : ksh-20120801-139.0.1.el7.x86_64 2/3
Verifying : oracle-database-preinstall-19c-1.0-1.el7.x86_64 3/3
Installed:
oracle-database-preinstall-19c.x86_64 0:1.0-1.el7
Dependency Installed:
ksh.x86_64 0:20120801-139.0.1.el7 libaio-devel.x86_64 0:0.3.109-13.el7
Complete!
如果用 RHEL7 or CentOS7, 可以安裝 OL7 repository. It will pull the dependencies from your normal repositories.
# yum install -y https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
Additional Setup
Set the password for the "oracle" user.passwd oracle
Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.
SELINUX=permissive
Once the change is complete, restart the server or run the following command.
# setenforce Permissive
If you have the Linux firewall enabled, you will need to disable or configure it, as shown here or here. To disable it, do the following.
# systemctl stop firewalld
# systemctl disable firewalld
Create the directories in which the Oracle software will be installed.
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
mkdir -p /u02/oradata
chown -R oracle:oinstall /u01 /u02
chmod -R 775 /u01 /u02
Create a "scripts" directory.
mkdir /home/oracle/scripts
Create an environment file called "setEnv.sh". The "$" characters are escaped using "\". If you are not creating the file with the cat command, you will need to remove the escape characters.
cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP
export ORACLE_HOSTNAME=testdb81
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/19.0.0/dbhome_1
export ORA_INVENTORY=/u01/app/oraInventory
export ORACLE_SID=cdb1
export PDB_NAME=pdb1
export DATA_DIR=/u02/oradata
export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF
Add a reference to the "setEnv.sh" file at the end of the "/home/oracle/.bash_profile" file.
echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile
Create a "start_all.sh" and "stop_all.sh" script that can be called from a startup/shutdown service. Make sure the ownership and permissions are correct.
cat > /home/oracle/scripts/start_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbstart \$ORACLE_HOME
EOF
cat > /home/oracle/scripts/stop_all.sh <<EOF
#!/bin/bash
. /home/oracle/scripts/setEnv.sh
export ORAENV_ASK=NO
. oraenv
export ORAENV_ASK=YES
dbshut \$ORACLE_HOME
EOF
chown -R oracle:oinstall /home/oracle/scripts
chmod u+x /home/oracle/scripts/*.sh
Installation 安裝
# xhost +cd $ORACLE_HOME
unzip -oq /path/to/software/LINUX.X64_193000_db_home.zip
DISPLAY=0.0:0; export DISPLAY
# Silent mode.
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile ${ORACLE_HOME}/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${ORACLE_HOSTNAME} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=${ORA_INVENTORY} \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=${ORACLE_HOME} \
ORACLE_BASE=${ORACLE_BASE} \
oracle.install.db.InstallEdition=SE2 \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
Database Creation
dbca
# Silent mode.
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname ${ORACLE_SID} -sid ${ORACLE_SID} -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword SysPassword1 \
-systemPassword SysPassword1 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbName ${PDB_NAME} \
-pdbAdminPassword PdbPassword1 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 1500 \
-storageType FS \
-datafileDestination "${DATA_DIR}" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
收尾
Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
cdb1:/u01/app/oracle/product/19.0.0/db_1:Y
Enable Oracle Managed Files (OMF) and make sure the PDB starts when the instance starts.
sqlplus / as sysdba <<EOF
alter pluggable database ${PDB_NAME} save state;
exit;
EOF
參考資訊:
Oracle Database 19c Installation On Oracle Linux 7 (OL7)
Multitenant : Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
Multitenant : Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
訂閱:
文章 (Atom)