【RMANでディスクチェック】
# su - oracle
# rman target /
# validate database check logical;
#-------------------------------------------------------
# archivelog 削除 ＆ 同期 （アーカイブログ）
#-------------------------------------------------------
# su - oracle
# rman target /
RMAN> list archivelog all;
RMAN> delete archivelog all; # 全削除
RMAN> crosscheck archivelog all; ＃ 物理ディスクとの整合性（同期）
RMAN> configure channel device type disk format '/work/archivelog.backup/ar_%U';
RMAN> show all; # 確認
RMAN configuration parameters for database with db_unique_name ARCSDBMS are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/work/archivelog.backup/ar_%U';
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 '+DATA/ARCSDBMS/snapcf_arcsdbms1.f';
RMAN> backup archivelog until sequence XXX thread=1 delete input;
※ XXXは、Seq.
RMAB> backup archivelog all delete all input; # 全バックアップ＆削除
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ARCSDBMS
=====================================================================
Key Thrd Seq S Low Time Name:
--------- ------ ------- - -------------------------
161321 1 45331 A 21-11-24 +ARCT_M/arcsdbms/archivelog/2021_11_24/thread_1_seq_45331.297.1089496643
# su - oracle
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
ORACLE_SID=XXXXXXXXX
# sqlplus / as sysdba
SQL> select * from v$version; #バージョン表示
SQL> def #ユーザ情報等確認
SQL> select username,expiry_date,profile from dba_users; #ユーザ一覧
SQL> select * from all_users; #ユーザ一覧
SQL> select * from v$instance; #インスタンス表示
SQL> select * from sys.gv_$instance; #インスタンス一覧
SQL> host #OSコマンドを実行。
SQL> select username,osuser,machine,terminal,program from v$session; #session一覧
SQL> select * from product_component_version;
SQL> select * from v$option; #オプション製品Ver.
SQL> select * from v$pwfile_users; #DBA接続可能ユーザ
SQL> select * v$database; #DBID
# sqlplus ARCSXXXX/xxxxxxx@arcsdbms1
SQL> select owner, table_name from all_tables where owner='XXX'; #テーブル一覧
SQL> select column_name,data_type, data_length from user_tab_columns where owner='XXXXXXX' and table_name='CCMXXX'; #column一覧
SQL> select * from CCMXXX;
【python36でアクセス】
# vi oracle.py
#!/usr/bin/env python
import cx_Oracle
import os
HOST = '192.168.
xx.
xxx'
PORT = '1521'
SID = 'arcsdbms'
os.environ["NLS_LANG"] = "JAPANESE_JAPAN.JA16SJISTILDE"
tns = cx_Oracle.makedsn(HOST, PORT, SID)
conn = cx_Oracle.connect("ARCSxxxx", "XXXXXXX", tns)
print( conn.version)
cur = conn.cursor()
cur.execute("""select column_name,data_type,data_length from all_tab_columns where table_name='XXXnnn'""")
rows = cur.fetchall()
for r in rows:
print("%s %s %s" % (r[0],r[1],r[2]))
cur.execute("""select * from XXXnnn""")
rows = cur.fetchall()
for r in rows:
print("%s %s %s %s" % (r[0],r[1],r[2],r[3]))
【Oracle RMANの有効期限が切れた。】
# su - oracle
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
ORACLE_SID=XXXXXXXXX
# sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 4 11:58:16 2015
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, Real Application Clusters and Automatic Storage Management options
set linesize 200;
SQL> select username,expiry_date,profile from dba_users;
USERNAME EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
MGMT_VIEW DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
DBSNMP DEFAULT
SYSMAN DEFAULT
PERFSTAT DEFAULT
SGNGDBMC DEFAULT
NEORDS DEFAULT
ARCSDBPA DEFAULT
DRCSDBCB DEFAULT
ARCSDBSS DEFAULT
USERNAME EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
ARCSDBCO DEFAULT
ARCSDBCB DEFAULT
SGNGDBMS DEFAULT
ARCSDBCS DEFAULT
TRCSDBCB DEFAULT
XRCSDBCB DEFAULT
OUTLN 15-01-20 DEFAULT
WMSYS 15-01-20 DEFAULT
APPQOSSYS 15-01-20 DEFAULT
DIP 15-01-20 DEFAULT
GG_USER 15-04-07 DEFAULT
USERNAME EXPIRY_D PROFILE
------------------------------ -------- ------------------------------
ORACLE_OCM 15-01-20 DEFAULT
23 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
[oracle@dbsv1 ~]$ sqlplus sys/パスワード@rmanrepo1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 4 12:01:34 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> select username,expiry_date,profile from dba_users;
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW 15-07-23
DEFAULT
SYS 15-07-23
DEFAULT
SYSTEM 15-07-23
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN 16-01-30
DEFAULT
DBSNMP 15-07-30
DEFAULT
SYSMAN 15-07-30
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN 15-01-24
DEFAULT
ANONYMOUS 15-01-24
DEFAULT
WMSYS 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB 15-01-24
DEFAULT
APPQOSSYS 15-01-24
DEFAULT
DIP 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM 15-01-24
DEFAULT
XS$NULL 15-01-24
DEFAULT
14 rows selected.
SQL> select username,expiry_date,profile from dba_users;
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW 15-07-23
DEFAULT
SYS 15-07-23
DEFAULT
SYSTEM 15-07-23
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN 16-01-30
DEFAULT
DBSNMP 15-07-30
DEFAULT
SYSMAN 15-07-30
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN 15-01-24
DEFAULT
ANONYMOUS 15-01-24
DEFAULT
WMSYS 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB 15-01-24
DEFAULT
APPQOSSYS 15-01-24
DEFAULT
DIP 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM 15-01-24
DEFAULT
XS$NULL 15-01-24
DEFAULT
14 rows selected.
SQL> select * from dba_profiles where PROFILE = 'DEFAULT'and RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
PROFILE
------------------------------------------------------------
RESOURCE_NAME
----------------------------------------------------------------
RESOURCE_TYPE
----------------
LIMIT
--------------------------------------------------------------------------------
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
180
SQL> alter profile DEFAULT limit password_life_time unlimited;
Profile altered.
SQL> select * from dba_profiles where PROFILE = 'DEFAULT'and RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
PROFILE
------------------------------------------------------------
RESOURCE_NAME
----------------------------------------------------------------
RESOURCE_TYPE
----------------
LIMIT
--------------------------------------------------------------------------------
DEFAULT
PASSWORD_LIFE_TIME
PASSWORD
UNLIMITED
SQL> select username,expiry_date,profile from dba_users;
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
MGMT_VIEW
DEFAULT
SYS
DEFAULT
SYSTEM
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
RMAN
DEFAULT
DBSNMP 15-07-30
DEFAULT
SYSMAN 15-07-30
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
OUTLN 15-01-24
DEFAULT
ANONYMOUS 15-01-24
DEFAULT
WMSYS 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
XDB 15-01-24
DEFAULT
APPQOSSYS 15-01-24
DEFAULT
DIP 15-01-24
DEFAULT
USERNAME EXPIRY_D
------------------------------------------------------------ --------
PROFILE
------------------------------------------------------------
ORACLE_OCM 15-01-24
DEFAULT
XS$NULL 15-01-24
DEFAULT
14 rows selected.
【パスワード変更】
SQL> alter USER DBSNMP IDENTIFIED BY "パスワード";
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@dbsv1 ~]$ logout
[root@dbsv1 ~]# logout
